I have tables called 'books', 'category', 'subcategory', and 'booksCategory'. Each book can have its own category and subcategories. For example, a Sci-Fi book can belong in the 'category' table under sci-fi, but it also may be under the subcategory under 'Aliens'.
- Table books - a list of books
- Table Category - a list of categories. e.g. Sci-Fi
- Table Subcategory - a list of subcategories. e.g. Aliens
- Table booksCategory - a list of books under categories and subcategories
I wasn't sure of the most optimal way of creating booksCategory. booksCategory will have a FK referencing the ids of both Category and Subcategory tables.
CREATE TABLE booksCategory(
booksCategory_id INT NOT NULL AUTO_INCREMENT primary key,
category_id INT references category (category_id),
subcategory_id INT references subcategory (subcategory_id)
)
so the results would be something like this
booksCategory_id | category_id | subcategory_id | books_id
----------------------------------------------------------
1 3 4 1 // 3 = SCI-FI, 4 = Aliens
2 2 NULL 1 // 2 = Adventure, Null = does not belong to any subcategory
The problem occurs when a book falls under multiple categories. In the example above, book_id with 1 can be both SCI-FI and Adventure. Is this the best method because then I will have a NULL in subcategory?
My other option I can think of is to have separate tables for booksCategory, booksSubcategory, and if I have more subcategories, bookSubcategories2. So basically a separate list of books that fall under Category, another list of books that fall under subcategory, and if I have more branching subcategories, a list of books that fall under that subcategory. I'm not really too fond of this method because it seems less organized/scattered.