问题描述
更新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.
我见过的商业产品数据库（晔笑）。用来分隔数据
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.
/结束更新
我设计的应用有一定的许多一对多的关系。在过去，我经常使用的缔表来重新present这些在数据库中。这引起了一些混乱给开发者。
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.
下面是一个例子DB结构：
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.
通过这种模式，获得A类的所有文件，你需要像下面这样：
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?
我猜想会有数千行的文件。类别可能会像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?
有它的pferred存储在数据库中的列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。是，preferred设计还是更到相关的文档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列，将允许您将数组存储在pre定义的架构，然后执行连接基于该字段的内容。其他数据库系统可能有类似的东西。
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.
这篇关于许多一对多的关系：使用关联表或分隔的值一列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！