使用一个“主”实施多对多价值
我有很多产品，每个产品都可以属于多个类别。
products: id, ...
products_categories: product_id, category_id
categories: id, ...
现在我想要有许多产品，每个产品都有一个主类别和 0 个或多个次要类别。我可以想到两种在 SQL 中对此进行建模的方法。
将
is_primary列添加到products_categories或者向
products添加primary_category_id列
什么是在纯 SQL 和/或 ActiveRecord 中实现此功能的最佳方法是什么？我正在使用 PostgreSQL，因为它物有所值。
I have many products that can each be in many categories.
products: id, ...
products_categories: product_id, category_id
categories: id, ...
Now I want to have many products, each with one master category, and 0 or more secondary categories. I can think of two ways to model this in SQL.
Add an
is_primarycolumn toproducts_categories
ORAdd a
primary_category_idcolumn toproducts
What is the best way to implement this in pure SQL and/or ActiveRecord? I'm using PostgreSQL, for what it's worth.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
我会选择第一个选项，除非我有充分的理由选择 2（例如获取主要类别时额外连接的成本）
原因：您可能需要将主要类别添加到 Product_category 表中（以便使用它）在查询中以统一且简单的方式（例如获取产品的所有类别）
选项 1 避免重复主要类别，因此更简单
I would go with the first option unless I have a good reason for choosing 2 (like the cost of an extra join when getting the primary category)
reason: you probably need to add the primary category to product_category table anyway (in order to use it in a uniform and simple way in queries like getting all categories for a product)
option 1 avoids duplicating primary category thus simpler
我会选择选项（1）。其原因是，由于您的产品可以属于多个类别，因此关系属性（即其“主要”类别）属于定义关系的表中。
我什至会更进一步建议您不应将字段标记为“is_primary”，而应该将该字段标记为“association_type”。并且不要只添加一个位字段，而是将其设为整数字段，并定义所有关联类型。在您今天的情况下，只有两种关联类型 - 次要和主要。优点是这种设计更具可扩展性。如果明天要求您定义“主要”、“次要”和所有其他第三级类别，则此设计将能够处理它，而不必添加另一个字段来指定“次要”字段。
I would go with option (1). The reason for this is since your products can belong to more than one category, the relationship attribute (that its a 'primary' category) belongs in the table that defines the relationship.
I would even go further and suggest that instead of labeling the field 'is_primary', you should have the field labeled as 'association_type'. And instead of just adding a bit field, make it an integer field, and have all the association types defined. In your case today, there are only two association types - secondary and primary. The advantage is that this design is much more scalable. If tomorrow, you are asked to define a 'primary', a 'secondary' and all other tertiary categories, this design will be able to handle it, instead of having to add another field to designate the 'secondary' field.
这实际上取决于您想要完成的具体细节。以下是在决定什么最适合您时需要考虑的一些事项。其他答案已经解决了第一种情况，所以我将重点讨论第二种情况。
如果您有
primary_category_id：product中有一个字段来告诉哪个category是主要类别，这似乎比在尽管 MR 建议使用association_type，但每行中具有1且每隔一行具有0的product_category> 听起来也很干净——但是你有“第三级”类别的机会有多大？NOT NULL）products_categories？如果使用
is_primary 方法，您应该以某种方式确保每个产品始终只有一个主要类别。It really depends on the exact details of what you're trying to accomplish. Here are some of the things to consider while deciding what's best for you. Other answers already tackled the first case, so I'm going to focus on the second one.
If you have
primary_category_id:productthat tells whichcategoryis the primary one, than to have a field in everyproduct_categorywhich has1in one row and0in every other row, although the suggestion by M.R. to useassociation_typesounds clean too - but what's the chance you're going to have "tertiary" categories?NOT NULL)products_categories?If you use the
is_primarymethod, you should somehow ensure that every product always has exactly one primary category.每种方式的优点和缺点是什么？
选项 1。我可以确定产品的主要类别确实是其类别之一。但可能存在确保产品不超过一个主要类别的问题。
选项 2。这可以让我确保产品只有一个主要类别。但我似乎没有办法确保它是同一产品的类别之一。
因此，我可能会选择第三个选项，使用表
Products_PrimaryCategories：它看起来与
product_categories相同，但有一些附加属性：product_id具有关联的唯一索引，确保每个产品只能有一个主要类别；(product_id,category_id)是引用products_categories (product_id,category_id)的外键，确保产品的主要类别是其中之一。类别（这意味着(product_id,category_id)应该是products_categories的主键）。What are each way's pros and cons?
Option 1. I can be sure that the primary category for a product is indeed one of its categories. But there may be a problem of ensuring that a product has no more than one primary category.
Option 2. This lets me make sure that a product has only one primary category. But then I don't seem to have a way to make sure that it's one of this same product's categories.
So, I would probably go for a third option, using a table
Products_PrimaryCategories:It seems the same as
product_categories, but has some additional properties:product_idhas an associated unique index, making sure you can only have one primary category for each product;(product_id, category_id)is a foreign key referencingproducts_categories (product_id, category_id)ensuring that a product's primary category is one of its categories (which implies that(product_id, category_id)should beproducts_categories's primary key).