DB设计模式-多对多分类/分类标签
我有一个现有的数据库设计来存储职位空缺。
“职位空缺”表在所有客户中都有许多固定字段，例如“职位”、“描述”、“薪资范围”。
EAV 设计了“自定义”字段，客户可以自行设置，例如“经理姓名”、“工作时间”。字段名称存储在“ClientText”表中，数据存储在“VacancyClientText”表中，其中包含 VacancyId、ClientTextId 和 Value。
最后，还有多对多 EAV 设计，用于自定义标记/对空缺职位进行分类，例如空缺职位所在的位置/办公室、所需技能列表等。这存储为列出标签类型的“ClientCategory”表、“位置、技能”、列出每个类别的有效值的“ClientCategoryItem”表，例如“伦敦、巴黎、纽约、罗马”、“C#、 VB、PHP、Python”。最后，有一个“VacancyClientCategoryItem”表，其中包含空缺的每个选定项目的 VacancyId 和 ClientCategoryItemId。
客户端可以添加的自定义字段或自定义类别的数量没有限制。
我现在正在设计一个与现有系统非常相似的新系统，但是，我能够限制客户端可以拥有的自定义字段的数量，并且它是从头开始构建的，因此我没有需要处理的遗留问题。
对于自定义字段，我的解决方案很简单，空缺表上有 5 个附加列，称为 CustomField1-5。这消除了其中一项 EAV 设计。
我正在努力应对标签/分类设计。如果我限制客户端拥有 5 个类别/类型的标签。我是否应该创建 5 个列出可能值“CustomCategoryItems1-5”的表，然后再创建 5 个多对多表“VacancyCustomCategoryItem1-5”，
这将导致 10 个表执行与现有系统中的三个表相同的存储。
另外，如果（天堂不允许）需求发生变化，因为我需要 6 个自定义类别而不是 5 个，那么这将导致大量代码更改。
因此，任何人都可以建议任何更适合存储此类数据的数据库设计模式。我很高兴坚持使用 EAV 方法，但是，现有系统遇到了与此类设计相关的所有常见性能问题和复杂查询。
非常感谢任何意见/建议。
使用的 DBMS 系统是 SQL Server 2005，但是，如果任何特定模式需要，2008 也是一个选项。
I have an existing database design that stores Job Vacancies.
The "Vacancy" table has a number of fixed fields across all clients, such as "Title", "Description", "Salary range".
There is an EAV design for "Custom" fields that the Clients can setup themselves, such as, "Manager Name", "Working Hours". The field names are stored in a "ClientText" table and the data stored in a "VacancyClientText" table with VacancyId, ClientTextId and Value.
Lastly there is a many to many EAV design for custom tagging / categorising the vacancies with things such as Locations/Offices the vacancy is in, a list of skills required. This is stored as a "ClientCategory" table listing the types of tag, "Locations, Skills", a "ClientCategoryItem" table listing the valid values for each Category, e.g., "London,Paris,New York,Rome", "C#,VB,PHP,Python". Finally there is a "VacancyClientCategoryItem" table with VacancyId and ClientCategoryItemId for each of the selected items for the vacancy.
There are no limits to the number of custom fields or custom categories that the client can add.
I am now designing a new system that is very similar to the existing system, however, I have the ability to restrict the number of custom fields a Client can have and it's being built from scratch so I have no legacy issues to deal with.
For the Custom Fields my solution is simple, I have 5 additional columns on the Vacancy Table called CustomField1-5. This removes one of the EAV designs.
It is with the tagging / categorising design that I am struggling. If I limit a client to having 5 categories / types of tag. Should I create 5 tables listing the possible values "CustomCategoryItems1-5" and then an additional 5 many to many tables "VacancyCustomCategoryItem1-5"
This would result in 10 tables performing the same storage as the three tables in the existing system.
Also, should (heaven forbid) the requirements change in that I need 6 custom categories rather than 5 then this will result in a lot of code change.
Therefore, can anyone suggest any DB Design Patterns that would be more suitable to storing such data. I'm happy to stick with the EAV approach, however, the existing system has come across all the usual performance issues and complex queries associated with such a design.
Any advice / suggestions are much appreciated.
The DBMS system used is SQL Server 2005, however, 2008 is an option if required for any particular pattern.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您是否考虑过使用 XML 列？您可以通过 XSL 以声明方式强制执行所有约束。
使用包含由模式（或模式集合）验证的 XML 数据的单个列来代替 EAV。
Have you thought about using an XML column? You can enforce all your constraints declaratively through XSL.
Instead of EAV, have a single column with XML data validated by a schema (or a collection of schemas).
看看在此问题/答案；描述观察模式。
它使用五个表，并且可以在“标准”RDBMS 中实现——Sql Server 2005 就可以。
实体可以拥有的自定义属性（观察）的数量没有限制。
编辑
如果属性需要标签（类别），请查看在这个。
Take a look at this question/answer; describes the observation pattern.
It uses five tables and can be implemented in a "standard" RDBMS -- Sql Server 2005 will do.
No limit on number of custom properties (observations) that an entity can have.
EDIT
If tags (categories) are needed for properties, take a look at this one.
为什么不将自定义字段存储在键值表中？
然后有辅助表列出每种类型的可能值（1 个表）以及每种空缺类型可能的类型（似乎是原始的 ClientCategory）
Why not store the custom fields in a key-value table?
Then have auxillary tables listing possible values per type (1 table) and may be possible types per vacancy type (it seems to be the original ClientCategory)