I need to implement Categorization and Sub-Categorization on something which is a bit similar to golden pages.
Assume I have the following table:
CategoryId, Title
10, Home
20, Business
30, Hobbies
I have two options to code the sub-categorization.
CategoryId, SubCategoryId, Title
10, 100, Gardening
10, 110, Kitchen
10, 120, ...
20, 100, Development
20, 110, Marketing
20, 120, ...
30, 100, Soccer
30, 110, Reading
30, 120, ...
CategoryId, SubCategoryId, Title
10, 100, Gardening
10, 110, Kitchen
10, 120, ...
20, 130, Development
20, 140, Marketing
20, 150, ...
30, 160, Soccer
30, 170, Reading
30, 180, ...
Option 2 sounds like it is easier to fetch rows from table
For example: SELECT BizTitle FROM tblBiz WHERE SubCatId = 170
whereas using Option 1 I'd have to write something like this:
SELECT BizTitle FROM tblBiz WHERE CatId = 30 AND SubCatId = 170
i.e., containing an extra AND
However, Option 1 is easier to maintain manually (when I need to update and insert new subcategories etc. and it is more pleasant to the eye in my opinion.
Any thoughts about it? Does Option 2 worth the trouble in terms of efficiency? Is there any design patters related with this common issue?
I would use this structure:
ParentId, CategoryId, Title
null, 1, Home
null, 2, Business
null, 3, Hobbies
1, 4, Gardening
1, 5, Kitchen
1, 6, ...
2, 7, Development
2, 8, Marketing
2, 9, ...
3, 10, Soccer
3, 11, Reading
3, 12, ...
In detail:
IDENTITY
, or similar), so that you can have more than 10 subcategoriesAs long as you are only using two levels of categories you can still select like this:
SELECT BizTitle FROM tblBiz WHERE ParentId = 3 AND CategoryId = 11
The new hierarchyid
feature of SQL server also looks quite promising: https://msdn.microsoft.com/en-us/library/bb677173.aspx
What I don't like about the Nested Set Model:
parent
field in combination with a foreign key constraint.
rght
is lower than lft
rght
or lft
fields