多对多关系:在列中使用关联表或分隔值? [英] Many-to-many relationship: use associative table or delimited values in a column?
问题描述
更新 2009.04.24
我的问题的重点不是开发人员的困惑以及如何解决.
The main point of my question is not developer confusion and what to do about it.
关键是要了解分隔值何时是正确的解决方案.
The point is to understand when delimited values are the right solution.
我见过商业产品数据库中使用的分隔数据(Ektron 哈哈).
I've seen delimited data used in commercial product databases (Ektron lol).
SQL Server 甚至有一个 XML 数据类型，因此它可以用于与分隔字段相同的目的.
SQL Server even has an XML datatype, so that could be used for the same purpose as delimited fields.
/结束更新
我正在设计的应用程序有一些多对多的关系.过去，我经常使用关联表在数据库中表示这些.这给开发者带来了一些困惑.
The application I'm designing has some many-to-many relationships. In the past, I've often used associative tables to represent these in the database. This has caused some confusion to the developers.
这是一个示例数据库结构:
Here's an example DB structure:
Document
---------------
ID (PK)
Title
CategoryIDs (varchar(4000))
Category
------------
ID (PK)
Title
文档和类别之间存在多对多关系.
在这个实现中，Document.CategoryIDs 是一个巨大的以竖线分隔的 CategoryIDs 列表.
In this implementation, Document.CategoryIDs is a big pipe-delimited list of CategoryIDs.
对我来说，这很糟糕，因为它需要在查询中使用子字符串匹配——而不能使用索引.我认为这会很慢并且不会扩展.
To me, this is bad because it requires use of substring matching in queries -- which cannot make use of indexes. I think this will be slow and will not scale.
使用该模型，要获取某个类别的所有文档，您需要如下内容:
With that model, to get all Documents for a Category, you would need something like the following:
select * from documents where categoryids like '%|' + @targetCategoryId + '|%'
我的解决方案是创建一个关联表如下:
My solution is to create an associative table as follows:
Document_Category
-------------------------------
DocumentID (PK)
CategoryID (PK)
这让开发人员感到困惑.是否有一些我遗漏的优雅替代解决方案?
This is confusing to the developers. Is there some elegant alternate solution that I'm missing?
我假设 Document 中有数千行.类别可能像 40 行左右.主要关注的是查询性能.我是否过度设计了这个?
I'm assuming there will be thousands of rows in Document. Category may be like 40 rows or so. The primary concern is query performance. Am I over-engineering this?
是否存在将 ID 列表存储在数据库列中而不是将数据推送到关联表的情况?
Is there a case where it's preferred to store lists of IDs in database columns rather than pushing the data out to an associative table?
还要考虑我们可能需要在文档之间创建多对多关系.这将建议关联表 Document_Document.这是首选设计还是将关联的文档 ID 存储在单列中更好?
Consider also that we may need to create many-to-many relationships among documents. This would suggest an associative table Document_Document. Is that the preferred design or is it better to store the associated Document IDs in a single column?
谢谢.
推荐答案
您设计中的 Document_Category 表无疑是解决问题的正确方法.如果可能的话，我建议您对开发人员进行教育，而不是提出一个次优的解决方案(并影响性能，并且没有参照完整性).
The Document_Category table in your design is certainly the correct way to approach the problem. If it's possible, I would suggest that you educate the developers instead of coming up with a suboptimal solution (and taking a performance hit, and not having referential integrity).
您的其他选项可能取决于您使用的数据库.例如，在 SQL Server 中，您可以有一个 XML 列，它允许您将数组存储在预定义的架构中，然后根据该字段的内容进行连接.其他数据库系统可能有类似的东西.
Your other options may depend on the database you're using. For example, in SQL Server you can have an XML column that would allow you to store your array in a pre-defined schema and then do joins based on the contents of that field. Other database systems may have something similar.
这篇关于多对多关系:在列中使用关联表或分隔值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
