MySQL 数据库 - 来自同一个表的相关结果/多对多数据库设计问题
我正在设计一个产品的关系数据库，其中有些产品是彼此的副本/盗版，我希望能够通过系统显示这一点。
因此，最初在我的初稿期间，我在产品中有一个名为“isacopyof”的字段，考虑只列出以逗号分隔的产品 ID 列表，这些产品 ID 是当前产品的副本。
显然，一旦我开始实施，就不会成功。
到目前为止，大多数多对多关系解决方案都围绕一个关联表，列出表 A 中的相关 id 和表 B 中的相关 id。这可行，但我的情况涉及同一个产品表中的相关项目...
我怎样才能围绕它建立一个解决方案？或者也许我的想法是错误的？
I am designing a relational database of products where there are products that are copies/bootlegs of each other, and I'd like to be able to show that through the system.
So initially during my first draft, I had a field in products called " isacopyof " thinking to just list a comma delimited list of productIDs that are copies of the current product.
Obviously once I started implementing, that wasn't going to work out.
So far, most many-to-many relationship solutions revolve around an associative table listing related id from table A and related id from table B. That works, but my situation involves related items from the SAME table of products...
How can I build a solution around that ? Or maybe I am thinking in the wrong direction ?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
你想太多了。
如果您有一个带有
productid键的products表，则可以有一个带有productid1和clones表>productid2 字段从products映射到products以及两个字段上的多键。没问题，它仍然是 3NF。You're overthinking.
If you have a
productstable with aproductidkey, you can have aclonestable withproductid1andproductid2fields mapping fromproductstoproductsand a multi-key on both fields. No issue, and it's still 3NF.因为某些东西是副本，这意味着你有父子关系......分层数据。
对于要建模的数据，您的方向是正确的。您可以向现有表添加一列来保存parent_id 值（指示当前记录的父级的主键值），而不是使用单独的表来保存关系。这是关于在 MySQL 中处理分层数据的优秀读物。 遗憾的是，
MySQL 没有分层查询语法，对于此类问题，我强烈建议您查看具有分层查询语法的语法：
Because something is a copy, that means you have a parent and child relationship... Hierarchical data.
You're on the right track for the data you want to model. Rather than have a separate table to hold the relationship, you can add a column to the existing table to hold the parent_id value--the primary key value indicating the parent to the current record. This is an excellent read about handling hierarchical data in MySQL...
Sadly, MySQL doesn't have hierarchical query syntax, which for things like these I highly recommend looking at those that do:
您没有理由不能在“链接”表中包含指向同一产品表的链接。
有几种方法可以做到这一点，但基本设计可能只是两列：
这两列都链接回产品表中的 ProductID。如果您知道哪个是“真实”产品，哪个是副本，您可能具有将“真实”productID 放置在 ProductID1 中并将“副本”productID 放置在 ProductID2 中的逻辑/约束。
There's no reason you can't have links to the same product table in your 'links' table.
There are a few ways to do this, but a basic design might simply be 2 columns:
Where both these columns link back to ProductID in your product table. If you know which is the 'real' product and which is the copy, you might have logic/constraints which place the 'real' productID in ProductID1 and the 'copy' productID in ProductID2.