以 m:n 关系引用两个表的数据
我正在开发一个基于 mysql 的系统来管理食品加工过程中的数据。 此时我遇到了以下具体问题：
我有一个表 A，其中包含一些项目：
Farmer Quantity
Farmer A 1000 kg
Farmer B 500 kg
然后我有一个表 B，它是表 A 中数据的 m:n 聚合：
Batch Quantity Quality etc.
LI1 200 kg ....
LI2 12000 kg ....
为了表示 m:n 关系，我有一个表连接两者的AB：
FK_Farmer FK_Batch
FarmerA LI1
FarmerB LI1
FarmerA LI2
现在的问题是：表B中的一些批次实际上是由其他批次组成的......这意味着它们是递归组成的。我很想知道在数据库设计方面实现这种情况的最佳方法是什么。
我是否应该在表 AB 中包含一个额外的外键来引用批次表？我是否应该强制执行外键并通过同一列引用农民和批处理表（并添加一个标志来指示递归或其他内容）？ 还有其他我忽略的明显解决方案吗？
能够通过直接 MySQL 对所有数据进行下钻查询会很好，但不是必需的。
I am working on a mysql based system to manage data from processing of food products.
At this point I came across the following specific Problem:
I have a table A with some items:
Farmer Quantity
Farmer A 1000 kg
Farmer B 500 kg
Then I have a table B which is an m:n agregation of data from table A:
Batch Quantity Quality etc.
LI1 200 kg ....
LI2 12000 kg ....
To represent the m:n relation I have a table AB which connects the two:
FK_Farmer FK_Batch
FarmerA LI1
FarmerB LI1
FarmerA LI2
Now the problem: some of the batches in Table B are actually made up of other batches... which means they are recursively composed. I am intersted to know what is the best approach in terms of database design to implement this situation.
Should I include an additional foreign key in table AB referencing back to the batches table? Should I not enforce foreign keys and reference both the farmers and the batch table through the same column (and add a flag to indicate recursion or something)?
Is there any other obvious solution I have ovelooked?
Being able to do drill-down queries for all data through direct MySQL would be nice, but is not necessarily required.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
表示数据的最简单方法是将 Parent 指针添加到 Batch 表。层次结构的根在此字段中为空。任何非根都将指向其父级，而父级可能又指向另一个父级，依此类推，无论您拥有多少级别。
查询这样的结构很棘手，因为标准 SQL 无法处理树。 Oracle 在其 SQL 方言中有专有扩展，但我认为 MySQL 没有。这意味着要追踪整个树，您必须编写循环查询的代码，或者必须编写一个对任意数量的最大级别执行多个连接的查询。
但我不知道有什么更简单的方法。基本上我打算用代码而不是单个查询来追踪树。
The simplest way to represent the data is to add a Parent pointer to the Batch table. The root of a hierarchy would have a null in this field. Any non-root would point to its parent, which might in turn point to another parent, etc, for as many levels as you may have.
Querying such a structure is tricky because standard SQL has no way to process a tree. Oracle has a proprietary extension in their SQL dialect, but I don't think MySQL does. This means that to chase the whole tree, you have to either write code that loops through queries, or you have to write a query that does multiple joins for some arbitrary number of maximum levels.
But I don't know any easier way around it. Basically I'd plan on chasing the tree with code rather than a single query.
如果一个父批次可以有多个子批次，一个子批次可以有多个父批次，那么你需要一个新的映射表：
使用外键来确保关系得到维护；但我不知道数据库是否可以防止您进入循环，您可能必须依赖代码或存储过程来实现这一点。
If a parent batch can have multiple child batches, and a child batch can have multiple parent batches, then you need a new mapping table:
Use foreign keys to make sure that the relations are maintained; but I don't know if the database can prevent you from getting into loops, you might have to rely on code or stored procs for that.