I have two tables, products and categories which have a many to many relationship, so I'm adding a products_categories table which will contain category_id and product_id.
我有两个表，产品和类别有很多关系，所以我添加了一个products_categories表，其中包含category_id和product_id。
Should I add another (auto-incrementing) index column or use the two existing ones as primary key?
我应该添加另一个（自动递增）索引列还是使用两个现有索引列作为主键？
6 个解决方案
#1
9
That depends.
那要看。
-
Are you seeing your data more as set of objects (and relational database is just a storage medium) or as set of facts represented and analyzed natively by relational algebra.
您是否将数据视为一组对象（关系数据库只是一种存储介质），或者是由关系代数本地表示和分析的一组事实。
-
Some ORMs/Frameworks/Tools don't have good support for multicolumn primary keys. If you happen to use one of them, you'll need additional id column.
某些ORM /框架/工具不能很好地支持多列主键。如果您碰巧使用其中一个，则需要额外的id列。
-
If it's just a many-to-many relationship with no additional data associated with it, it's better to avoid additional id column and have both columns as primary key.
如果它只是一个多对多关系而没有与之关联的其他数据，最好避免使用额外的id列并将两列作为主键。
-
If you want to add some additional information to this association, then it'll become something more then many-to-many relationship of two entities. So it becomes entity in it's own and it'd be more convenient if it had it's own id independent to entities it connects.
如果你想为这个关联添加一些额外的信息，那么它将变成两个实体的多对多关系。所以它变成了它自己的实体，如果它拥有独立于它连接的实体的自己的id，它会更方便。
#2
5
You don't need to add an extra, auto-incrementing index column, but I (perhaps contrary to most others) still recommend that you do. First, it is easier in the application program to refer to a row using a single number, for example when you delete a row. Second, it sometimes turns out to be useful to be able to know the order in which the rows were added.
您不需要添加额外的自动递增索引列，但我（可能与其他大多数人相反）仍然建议您这样做。首先，应用程序更容易使用单个数字引用行，例如删除行时。其次，有时能够知道添加行的顺序是有用的。
#3
2
No, it's not necessary at all, given that these two columns are already executing the function of a primary key.
不，这根本没有必要，因为这两列已经在执行主键功能了。
This third column whould just add more space to your table.
第三列可以为您的表添加更多空间。
But... You could use it maybe to see the order in which your records where added to your table. That's the only function I can see to this column.
但是......您可以使用它来查看您的记录添加到表中的顺序。这是我能在本专栏中看到的唯一功能。
#4
2
You don't need to add an auto-incrementing index column. Standard practice is to use just the two existing columns as your primary key for M:M association tables like you describe.
您不需要添加自动递增索引列。标准做法是仅使用两个现有列作为您描述的M：M关联表的主键。
#5
1
I would make the primary key category_id and product_id. Add an auto increment only if the order will ever be relevent in later uses.
我会创建主键category_id和product_id。仅当订单在以后的使用中相关时才添加自动增量。
#6
0
There's a conceptual question - is products_categories an entity or is simply a table that represents a relationship between two entities? If it's an entity then, even if there are no additional attributes, I'd advocate for a separate ID column for the entity. If it's a relationship, if there are additional attributes (say, begin_date, end_date or something like that), I'd advocate to have a multi-column primary key.
有一个概念性的问题 - products_categories是一个实体还是只是一个代表两个实体之间关系的表？如果它是一个实体，那么即使没有其他属性，我也会主张为该实体提供一个单独的ID列。如果它是一种关系，如果有其他属性（比如，begin_date，end_date或类似的东西），我主张拥有一个多列主键。
#1
9
That depends.
那要看。
-
Are you seeing your data more as set of objects (and relational database is just a storage medium) or as set of facts represented and analyzed natively by relational algebra.
您是否将数据视为一组对象（关系数据库只是一种存储介质），或者是由关系代数本地表示和分析的一组事实。
-
Some ORMs/Frameworks/Tools don't have good support for multicolumn primary keys. If you happen to use one of them, you'll need additional id column.
某些ORM /框架/工具不能很好地支持多列主键。如果您碰巧使用其中一个，则需要额外的id列。
-
If it's just a many-to-many relationship with no additional data associated with it, it's better to avoid additional id column and have both columns as primary key.
如果它只是一个多对多关系而没有与之关联的其他数据，最好避免使用额外的id列并将两列作为主键。
-
If you want to add some additional information to this association, then it'll become something more then many-to-many relationship of two entities. So it becomes entity in it's own and it'd be more convenient if it had it's own id independent to entities it connects.
如果你想为这个关联添加一些额外的信息，那么它将变成两个实体的多对多关系。所以它变成了它自己的实体，如果它拥有独立于它连接的实体的自己的id，它会更方便。
#2
5
You don't need to add an extra, auto-incrementing index column, but I (perhaps contrary to most others) still recommend that you do. First, it is easier in the application program to refer to a row using a single number, for example when you delete a row. Second, it sometimes turns out to be useful to be able to know the order in which the rows were added.
您不需要添加额外的自动递增索引列，但我（可能与其他大多数人相反）仍然建议您这样做。首先，应用程序更容易使用单个数字引用行，例如删除行时。其次，有时能够知道添加行的顺序是有用的。
#3
2
No, it's not necessary at all, given that these two columns are already executing the function of a primary key.
不，这根本没有必要，因为这两列已经在执行主键功能了。
This third column whould just add more space to your table.
第三列可以为您的表添加更多空间。
But... You could use it maybe to see the order in which your records where added to your table. That's the only function I can see to this column.
但是......您可以使用它来查看您的记录添加到表中的顺序。这是我能在本专栏中看到的唯一功能。
#4
2
You don't need to add an auto-incrementing index column. Standard practice is to use just the two existing columns as your primary key for M:M association tables like you describe.
您不需要添加自动递增索引列。标准做法是仅使用两个现有列作为您描述的M：M关联表的主键。
#5
1
I would make the primary key category_id and product_id. Add an auto increment only if the order will ever be relevent in later uses.
我会创建主键category_id和product_id。仅当订单在以后的使用中相关时才添加自动增量。
#6
0
There's a conceptual question - is products_categories an entity or is simply a table that represents a relationship between two entities? If it's an entity then, even if there are no additional attributes, I'd advocate for a separate ID column for the entity. If it's a relationship, if there are additional attributes (say, begin_date, end_date or something like that), I'd advocate to have a multi-column primary key.
有一个概念性的问题 - products_categories是一个实体还是只是一个代表两个实体之间关系的表？如果它是一个实体，那么即使没有其他属性，我也会主张为该实体提供一个单独的ID列。如果它是一种关系，如果有其他属性（比如，begin_date，end_date或类似的东西），我主张拥有一个多列主键。