hi! I've found some useful information in this forum and once again I'm looking for some advice. I'm trying to make a site for listing books at a university. I want it to be able to support multiple universities, with each university having several departments, each dept with several courses, each course with several sections and finally each course will have zero or more required textbooks. I want people (students primarily) to be able to login and list books that they either want to sell and/or want to buy. they can then list their contact information for others to buy/sell the books. I need the university/dept/course/etc info because I want people to be able to browse by subject as well as search using the ISBN.

Here is what my schema looks like so far. I don't have the tables for the user info yet, because I'm not sure about the school schema. The layout seems logical, since the book depends on the university, and it looks like I'd have to use a series of JOINS trickling down from the university to the course section. that seems like a lot of extra work, so I'm wondering if there is a better way to organize my tables.

any ideas would be REALLY appreciated! thanks again!

-SelArom

I talked to my database professor about this and he was pretty hard to understand (his accent I mean) but he gave me a slightly revised schema that looked something this, with the courses BELONGING TO the departments... I kinda see how it makes sense but I don't see how this improves my design. I still see having to JOIN all of the tables to get the courses for a particular university... which would be the better route to accomplish what I'm trying to do? thanks again!

-SelArom

You are not joining lists by providing foreign keys and primary keys, what you are doing is linking them. You link lists so that you can search efficiently. Imagine you have 100 universities, each with 20 departments, each offering 20 courses and each course uses 20 books. That is 8 million books. If the lists are linked firstly there is no need to record a book twice and secondly specifying the particular course will give you the universities, departments and books required as a lookup.

Books you request will come from this list.

If you want to sell a book then it would be good to know who is using what book, what edition, where, likewise if you are buying.

You have users who buy and sell so there will be a list of books wanted and a list of books on offer.

The two lists can be linked on their key (ISBN).

I hope this helps.

thank you for your reply! I'm starting to understand what you're saying, but I am still confused. See, the departments refer to schools like "business" and "science", and many universities have the same departments. so I'm thinking that it would be a waste to list "Math" for every university. I was thinking of doing a kind of lookup table, but the problem is, not ALL schools have every department... is it best to leave the schema as it is or maybe make the department table a lookup table with a new table in the middle linking universities to departments? thanks again for your insight!

-SelArom

The way it works is something like this

Universities Departments Courses Books
-------------------------------------------------
Uni of Sydney Comp Sci Prog & DS A
Uni of Adelaide ... Prog & DS B
Deakin ... ....

I did Programming & Data Structues at Adelaide
Perhaps Deakin and Sydney have courses called Prog & DS as well
however they probably didn't use the same books.

So it is necessary to specify University, Department and Course
to get the correct booklist. This your lecturer was pointing out.

Bill
....