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.
Best Answer
Because you desire a categorization hierarchy of indeterminate depth, you will have to use a single category table structured along these lines:
This type of structure is not as well supported by MySQL as some other RDBMS's, but these issues can all be worked through.
You will also need a revised BookCategory mapping table such as this:
My apologies if the Foreign Key syntax above is not quite correct for MySQL; I usually work with SQL Server.
Update With this INSERT:
the following SELECT:
yields this:
When Common Table Expressions are supported (not yet in MySQL I understand) the following will provide automatic nesting of the hierarchy levels: