Tutorial :Is this necessary to point back the relationship?



Question:

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.


Solution:1

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


Solution:2

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


Solution:3

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.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »