数据库设计-类别和子类别 [英] database design - categories and sub-categories

问题描述

我需要对有点像黄金页面的东西实施分类和子分类。

I need to implement Categorization and Sub-Categorization on something which is a bit similar to golden pages.

假设我有下表：

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, ...

选项2-子类别ID总体上是唯一的：

子类别表

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听起来更容易从表
中获取行例如：从tblBiz中选择BizTitle，其中SubCatId = 170

Option 2 sounds like it is easier to fetch rows from table For example: SELECT BizTitle FROM tblBiz WHERE SubCatId = 170

而使用选项1，我必须写类似

whereas using Option 1 I'd have to write something like this:

SELECT BizTitle FROM tblBiz WHERE CatId = 30 AND SubCatId = 170

即，包含额外的 AND

但是，选项1更易于手动维护（当我需要更新和插入新的子类别等时，在我看来，它更令人愉悦。

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.

任何有什么想法吗？在效率方面，选项2值得承担麻烦吗？是否有与此常见问题相关的设计模式？

Any thoughts about it? Does Option 2 worth the trouble in terms of efficiency? Is there any design patters related with this common issue?

推荐答案

我将使用以下结构：

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, ...

详细信息：

• 仅使用一个表，该表引用自身，这样您就可以拥有无​​限个类别深度


• 使用技术ID （使用 IDENTITY 或类似的名称），这样您就可以超过10个子类别


• 如果需要，为类别号添加可读性列，作为单独字段

• only use one table, which references itself, so that you can have unlimited depth of categories
• use technical ids (using IDENTITY, or similar), so that you can have more than 10 subcategories
• if required add a human readable column for category-numbers as separate field

只要您仅使用两个级别的类别，您仍然可以像这样选择：

As 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

---

新的< SQL Server的code> hierarchyid 功能看起来也很有希望： https://msdn.microsoft.com/zh-CN/library/bb677173.aspx

---

The new hierarchyid feature of SQL server also looks quite promising: https://msdn.microsoft.com/en-us/library/bb677173.aspx

我对嵌套集模型不满意：

• 插入和删除嵌套集模型中的/ strong>项目非常复杂，需要昂贵的锁。


• 一个人可以轻松地创建不一致之处如果将 parent 字段与外键约束结合使用，则被禁止。
  
  
  
  
  • 如果 rght 比<$ c低，则可能会出现不一致$ c> lft
  
  
  • 如果值成倍增加 rght 或 lft 字段
  
  
  • 如果创建间隙
  $，则可能会出现不一致的情况b $ b
  • 如果创建重叠
  
  
  • Inserting and deleting items in the Nested Set Model is a quite comlicated thing and requires expensive locks.
  • One can easily create inconsistencies which is prohibited, if you use the parent field in combination with a foreign key constraint.
    • Inconsistencies can appear, if rght is lower than lft
    • Inconsistencies can appear, if a value apprears in several rght or lft fields
    • Inconsistencies can appear, if you create gaps
    • Inconsistencies can appear, if you create overlaps

    这篇关于数据库设计-类别和子类别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！