Here is the situation: I have a table called "books", and a table called "categories". Each "Book" is belong to a category, so the book have a field called "category id". But it is necessary for making a "categoryList" to store "book id"? I can use a select statement to find the books that belongs to categories, but is it a better approach to store the book id from categoryList? thank you.
+3
A:
in fact, it would be better to use a mapping table to specify the category/book relationship.
this table could be named category_book and have one column for category_id & one column for book_id
dweeves
2010-07-05 15:01:01
Agreed, this way you could have a book belonging to many categories
BenW
2010-07-05 15:01:44
But a many to many relationship in this case is a bad idea. How can a single book sit on two different shelves in a library? That's essentially what you're doing with a many to many relationship.
rockinthesixstring
2010-07-05 15:12:40
in fact, a many to many relationship has another advantage in design.it clearly separates the "what are my entities" from "how do i use them".a book is a book, a category is a category , the relationship category/book has nothing to do with what a book is
dweeves
2010-07-05 15:15:36
A:
In your previous question you say a Book belongs to only one Category. So you do not need a CategoryList table. That is extra maintenance for no extra benefit. Supporting queries by Category is why Nature gave us indexes.
APC
2010-07-05 15:01:34
In a real library, a book sometimes has difficulty being on two shelves at one time, but different collections may have their own copies. In libraries, each copy of a book has to have its own identifier and assignment.
Cade Roux
2010-07-05 15:05:54
@APC - if a library is purchasing more than one copy of a book, then the database "should" have multiple records of that book (one record for each physical book), and assign the category ID accordingly.
rockinthesixstring
2010-07-05 15:14:02
+3
A:
If ONE book can belong to MANY categories, and ONE category can have MANY books then you need a MANY to MANY relationship using an intermediary table (something like Books _JOIN_Categories).
Books
ID | BookName
Categories
ID | CategoryName
Books_Join_Categories
BookID | CategoryID
If EACH book only has ONE category, but EACH category can have MANY books, then your current relationship structure (CategoryID in the Books table) should do you just fine.
Books
ID | CategoryID | BookName
Categories
ID | CategoryName
In fact, the second option is probably best. A many to many relationship is probably not a good idea in this situation (unless the book is getting torn in half to be put on separate shelves). If two different "category" sections require the same book, then each category (section in a library) would get they're own physical copy of the book. Meaning two book records in the database, each with a separate CategoryID
rockinthesixstring
2010-07-05 15:02:46