我需要实现分类和子分类的东西,这是有点类似于黄金页面.
假设我有下表:
Category Table
CategoryId, Title
10, Home
20, Business
30, Hobbies
我有两个选项来编码子分类.
OPTION 1 - Subcategory Id is unique within Category ONLY:
Sub Category Table
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, ...
OPTION 2 - Subcategory Id is unique OVERALL:
Sub Category Table
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, ...
选项2听起来更容易从表读取行
例如:SELECT BizTitle FROM tblBiz WHERE SubCatId = 170
而使用选项1,我必须写下如下内容:
SELECT BizTitle FROM tblBiz WHERE CatId = 30 AND SubCatId = 170
即包含额外的AND
然而,选项1更容易手动维护(当我需要更新和插入新的子类别等时),在我看来,它更令人赏心悦目.
有什么 idea 吗?就效率而言,选项2值得麻烦吗?有没有与这个常见问题相关的设计模式?