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.

1 Answer 1


Because you desire a categorization hierarchy of indeterminate depth, you will have to use a single category table structured along these lines:

    Category_ID           INT NOT NULL AUTO_INCREMENT primary key,
    Parent_Category_ID    INT NOT NULL REFERENCES Category(Category_ID),
    Description           varchar(255) not null

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:

CREATE TABLE BookCategory (
    Book_ID        INT NOT NULL references Book(Book_ID),
    Category_ID    INT NOT NULL references Category(Category_ID)

My apologies if the Foreign Key syntax above is not quite correct for MySQL; I usually work with SQL Server.

Update With this INSERT:

insert Category(Parent_Category_ID,Description)
values (1,'Mystery')

the following SELECT:

      coalesce(grandparent.Description + '-',' ') +
      coalesce(parent.Description + '-',' ') +
    as varchar(255)) as Description
from Category                           root
left join Category                      parent
   on parent.Category_ID            = root.Parent_Category_ID
  and root.Parent_Category_ID      <> root.Category_ID
left join Category                      grandparent
   on grandparent.Category_ID       = parent.Parent_Category_ID
  and parent.Parent_Category_ID    <> parent.Category_ID

yields this:

Category_ID Description
1             Mystery
2             SciFi
3            SciFi-Aliens
4           SciFi-Aliens-Humanoid
5           SciFi-Aliens-Reptilian
6           SciFi-Aliens-Other

When Common Table Expressions are supported (not yet in MySQL I understand) the following will provide automatic nesting of the hierarchy levels:

with data as (
        cast(Description as varchar(255)) as Description
    from Category
    where Parent_Category_ID = Category_ID

    union all

        cast(parent.Description + '-' + child.Description as varchar(255)) as Description
    from data       parent
    join Category   child
       on child.Parent_Category_ID  = parent.Category_ID
    and child.Parent_Category_ID <> child.Category_ID
from data
  • You'll also need a many-to-many table between Category and Book Commented Jul 20, 2014 at 15:36
  • @ypercube: OP already has that; it seemed redundant to re-state the obvious in that circumstance. Commented Jul 20, 2014 at 15:46
  • He has it but without foreign key to Book! Commented Jul 20, 2014 at 15:56
  • @ypercube: Ha! That's what I get for skimming too quickly. Commented Jul 20, 2014 at 15:59
  • 1
    Check the Adjacency List model (one of the ways to represent hierarchical data.) See this question at SO: What are the Options for Storing Hierarchical Data in a Relational Database? There are numerous articles on the Internet about the 4 or 5 ways to store such data. Commented Jul 20, 2014 at 17:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.