数据库设计-类别和子类别

本文介绍了数据库设计-类别和子类别的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 29岁程序员，3月因学历无情被辞！ 我需要对有点像黄金页面的东西实施分类和子分类。I need to implement Categorization and Sub-Categorization on something which is a bit similar to golden pages.假设我有下表：CategoryId, Title10, Home20, Business30, Hobbies我有两个选择可以对子分类进行编码。I have two options to code the sub-categorization.CategoryId, SubCategoryId, Title10, 100, Gardening10, 110, Kitchen10, 120, ...20, 100, Development20, 110, Marketing20, 120, ...30, 100, Soccer30, 110, Reading30, 120, ... 选项2-子类别ID总体上是唯一的： 子类别表OPTION 2 - Subcategory Id is unique OVERALL:Sub Category TableCategoryId, SubCategoryId, Title10, 100, Gardening10, 110, Kitchen10, 120, ...20, 130, Development20, 140, Marketing20, 150, ...30, 160, Soccer30, 170, Reading30, 180, ...选项2听起来更容易从表中获取行例如：从tblBiz中选择BizTitle，其中SubCatId = 170Option 2 sounds like it is easier to fetch rows from tableFor 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, Titlenull, 1, Homenull, 2, Businessnull, 3, Hobbies1, 4, Gardening1, 5, Kitchen1, 6, ...2, 7, Development2, 8, Marketing2, 9, ...3, 10, Soccer3, 11, Reading3, 12, ...详细信息： 仅使用一个表，该表引用自身，这样您就可以拥有无​​限个类别深度 使用技术ID （使用 IDENTITY 或类似的名称），这样您就可以超过10个子类别 如果需要，为类别号添加可读性列，作为单独字段only use one table, which references itself, so that you can have unlimited depth of categoriesuse technical ids (using IDENTITY, or similar), so that you can have more than 10 subcategoriesif 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 新的 hierarchyid 功能看起来也很有希望： https://msdn.microsoft.com/zh-CN/library/bb677173.aspxThe new hierarchyid feature of SQL server also looks quite promising: https://msdn.microsoft.com/en-us/library/bb677173.aspx我对嵌套集模型不满意： 插入和删除嵌套集模型中的/ strong>项目非常复杂，需要昂贵的锁。 一个人可以轻松地创建不一致之处如果将 parent 字段与外键约束结合使用，则被禁止。 如果 rght 比，则可能会出现不一致$ 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 lftInconsistencies can appear, if a value apprears in several rght or lft fieldsInconsistencies can appear, if you create gapsInconsistencies can appear, if you create overlaps 这篇关于数据库设计-类别和子类别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！