locked
Database with Multiple Category Levels RRS feed

  • Question

  • Hi,

    Which of the following is a better structure?

    Should I create separate tables for each category level: Tables Category1, Category2, Category3 etc. Category1 will have a primary key column Category1ID. Category2 will have a primary key column Category2ID and a foreign key Category1ID which ties records from category2 to category1.
    OR should I create one table with multiple categories and use a parentcategoryid column to tie children categories with their parents.

    Thank you for your help.
    Tuesday, February 23, 2010 3:48 PM

Answers

  • That depends. Is your hierarchy balanced (i.e. each leaf level node has the same number of ancetors) or ragged (i.e. Each leaf level node can have a different number of ancestors).
    If the former then use a different table for each level. If the latter, use parentCategoryId (aka parent-child aka adjacency list)

    -Jamie

    http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Tuesday, February 23, 2010 3:55 PM

All replies

  • As always, it depends.

    If the categories are necessarily hierarchical, Category(Id, Parent) will do just fine.
    If they are not necessarily hierarchical, there are other options based on the requirements, which would require greater detail.
    • Marked as answer by CDHDev Tuesday, February 23, 2010 6:34 PM
    • Unmarked as answer by CDHDev Tuesday, February 23, 2010 6:55 PM
    • Proposed as answer by Eric WisdahlEditor Friday, March 26, 2010 3:14 PM
    Tuesday, February 23, 2010 3:55 PM
    Answerer
  • That depends. Is your hierarchy balanced (i.e. each leaf level node has the same number of ancetors) or ragged (i.e. Each leaf level node can have a different number of ancestors).
    If the former then use a different table for each level. If the latter, use parentCategoryId (aka parent-child aka adjacency list)

    -Jamie

    http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Tuesday, February 23, 2010 3:55 PM
  • Jamie,

    Thank you for your answer. If it's balanced why should I use a different table for each level?
    Tuesday, February 23, 2010 7:25 PM
  • Jamie,

    Thank you for your answer. If it's balanced why should I use a different table for each level?
    Because it would then conform to 3rd normal form. Plus you can store pertinent attributes for each level which you can't if everything is bunged into a single table.

    http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Tuesday, February 23, 2010 8:30 PM