My database includes a Customer and a Subcategories table.
Customers can belong to one-or-more Subcategories (and of course there are one-or-more Customers for any Subcategory).
I wonder which is the best solution to link these tables that share a many-to-many relationship:
我的数据库包括一个客户和一个子类别表。客户可以属于一个或多个子类别(当然任何子类别都有一个或多个客户)。我想知道将这些表连接到多对多关系的最佳解决方案是什么:
- A "standard" junction CustomerSubcategory table that includes just two fields: CustomerID (PK, FK) and SubcategoryID (PK, FK), or
- 一个“标准”连接CustomerSubcategory表，它只包含两个字段:CustomerID (PK, FK)和SubcategoryID (PK, FK)，或者
- A CustomerSubcategoryDetail table that would also include a CustomerSubcategoryDetailID (PK), as well as the SubcategoryID (PK, FK) and CustomerID (FK) fields.
- CustomerSubcategoryDetail表也将包括CustomerSubcategoryDetailID (PK)以及SubcategoryID (PK, FK)和CustomerID (FK)字段。
Any advice?
任何建议吗?
Cheers, Corbex
欢呼,Corbex
3 个解决方案
#1
2
I don't think the additional PK on the junction table adds any value. Will you ever have to look up the value(s) in that table without being able to identify them by Customer and/or SubCategoryID? Will the CustomerSubCategoryDetailID value be used anywhere else?
我认为连接表上的附加PK不会增加任何价值。您是否曾经需要查找该表中的值，而无法通过Customer和/或SubCategoryID识别它们?CustomerSubCategoryDetailID值会被用在别的地方吗?
An interesting discussion ensued on my blog when I complained about folks knee-jerking an IDENTITY column onto every single table. Some folks made some good arguments for having an OrderDetailID column on the OrderDetails table. Do any of these situations apply to you?
我在博客上发表了一篇有趣的讨论，当时我抱怨人们在每张桌子上随意地插入一个身份栏。有些人对在OrderDetails表上有OrderDetailID列提出了一些很好的理由。这些情况对你适用吗?
#2
1
I don't see the need for a third column with a customersubcategoryid; therefore, I would go with the first option. The extra column is not going to give you any advantage in any of your joins. You won't even use it.
我不认为需要使用customersubcategoryid作为第三列;因此，我会选择第一个选项。额外的列不会给您的任何连接带来任何优势。你甚至不会使用它。
#3
1
Go with your first choice. Make sure that you create a key that includes both columns and enforces uniqueness. That way you'll never need a separate primary key to distinguish between otherwise identical rows.
选择你的第一选择。确保您创建了一个包含列和强制惟一性的键。这样，您就不需要单独的主键来区分其他相同的行。
There is a additional benefit in having both columns in the unique key. Queries that need to perform a lookup by the first column can use the index, but they never need to read the data rows because the index already contains the second column.
在惟一键中包含两个列还有一个额外的好处。需要在第一列执行查找的查询可以使用索引，但是它们永远不需要读取数据行，因为索引已经包含第二列。
#1
2
I don't think the additional PK on the junction table adds any value. Will you ever have to look up the value(s) in that table without being able to identify them by Customer and/or SubCategoryID? Will the CustomerSubCategoryDetailID value be used anywhere else?
我认为连接表上的附加PK不会增加任何价值。您是否曾经需要查找该表中的值，而无法通过Customer和/或SubCategoryID识别它们?CustomerSubCategoryDetailID值会被用在别的地方吗?
An interesting discussion ensued on my blog when I complained about folks knee-jerking an IDENTITY column onto every single table. Some folks made some good arguments for having an OrderDetailID column on the OrderDetails table. Do any of these situations apply to you?
我在博客上发表了一篇有趣的讨论，当时我抱怨人们在每张桌子上随意地插入一个身份栏。有些人对在OrderDetails表上有OrderDetailID列提出了一些很好的理由。这些情况对你适用吗?
#2
1
I don't see the need for a third column with a customersubcategoryid; therefore, I would go with the first option. The extra column is not going to give you any advantage in any of your joins. You won't even use it.
我不认为需要使用customersubcategoryid作为第三列;因此，我会选择第一个选项。额外的列不会给您的任何连接带来任何优势。你甚至不会使用它。
#3
1
Go with your first choice. Make sure that you create a key that includes both columns and enforces uniqueness. That way you'll never need a separate primary key to distinguish between otherwise identical rows.
选择你的第一选择。确保您创建了一个包含列和强制惟一性的键。这样，您就不需要单独的主键来区分其他相同的行。
There is a additional benefit in having both columns in the unique key. Queries that need to perform a lookup by the first column can use the index, but they never need to read the data rows because the index already contains the second column.
在惟一键中包含两个列还有一个额外的好处。需要在第一列执行查找的查询可以使用索引，但是它们永远不需要读取数据行，因为索引已经包含第二列。