如何处理超过2个表的多对多关系？
这就是我现在所处的位置。我有四个表：任务、项目、机会和任务外部参照。项目表和机会表均与任务具有一对多关系。我将这些关系存储在task_xref 中。每个表的架构如下所示（简化）：
task
----
id(pk)
name
project
-------
id(pk)
name
...
opportunity
-----------
id(pk)
name
...
task_xref
---------
task_id(task id)
fkey(other table id)
假设项目和机会中的键不相同 (GUID)，因此机会无法获取项目的任务等。从表面上看，这很有效，一个外部参照表可以维护任务和项目、机会（或将来可能需要任务关系的任何其他表）之间的关系。
我目前的困境是双向的。如果我希望获得单个项目或机会的所有任务，那没问题。如果我撤回一项任务并想知道相关项目或机会的名称，我就无法做到。我无法知道相关的fkey是项目还是机会。将来我可能会有其他带有任务关系的表；虽然我现在有两张桌子，但将来可能会有更多。
以下是我到目前为止想到的可能的解决方案： 1) 每对单独的外部参照表（例如task_project_xref、task_opportunity_xref...） 缺点：我必须对每个外部参照表运行查询，以查找任务的关系
2）task_xref 中的第三列指向父表 缺点：这对我来说似乎是一个拼凑
3）以可识别的方式存储项目、机会中的主键（例如 proj1、proj2、proj3、opp1、opp2、opp3），这样我可以通过查看来判断任务与哪个表相关F键 缺点：这感觉就像我正在使项目和机会中的主键变得神奇，赋予它们更多的意义，而不仅仅是作为单个记录的标识符（也许我想得太多了）
我的问题是：有吗我忽略的其他解决方案？哪种解决方案比其他解决方案更好/更差？
如果可能的话，我试图限制连接并尽可能保持良好的性能。如果这有助于简化事情，我也不反对在代码中加入数据。
我正在使用 PHP 和 MySQL（目前使用 MyISAM 表，但如果有理由的话将使用 INNODB）。
Here is where I am at right now. I have four tables: task, project, opportunity, and task_xref. The project and opportunity tables each have a one-to-many relationship with task. I am storing those relationships in task_xref. The schema looks something like this for each table (simplified):
task
----
id(pk)
name
project
-------
id(pk)
name
...
opportunity
-----------
id(pk)
name
...
task_xref
---------
task_id(task id)
fkey(other table id)
Assume that the keys in project and opportunity will not be the same (GUID) so an opportunity can't fetch tasks for a project and so on. This works well on the surface, one xref table to maintain the relationships between task and project, opportunity (or any other tables that might need a task relationship in the future).
My current dilemma is of bi-directionality. If I'm looking to get all the tasks for an individual project or opportunity it's no problem. If I'm pulling back a task and want to know the name of the related project or opportunity I can't. I have no way of knowing whether the related fkey is a project or opportunity. In the future I will probably have other tables with task relationships; while I have 2 tables now, there could be many more in the future.
Here are the possible solutions I've thought of so far:
1) separate xref table for each pair (e.g. task_project_xref, task_opportunity_xref...)
cons: I have to run a query for each xref table looking for relationships for a task
2) a third column in task_xref to point to the parent table
cons: this seems like a kludge to me
3) store the primary keys in project, opportunity in an identifiable way (e.g. proj1, proj2, proj3, opp1, opp2, opp3) so I can tell which table a task relates to by looking at the fkey
cons: this feels like I'm making the primary keys in projects and opportunities magical, imbuing them with more meaning than just being an identifier to a single record (maybe I'm over-thinking it)
My question then is this: Are there other solutions I am overlooking? Which solution is better/worse than the others?
I am trying to keep joins limited if possible and performance as good as possible. I am also not opposed to joining data in code if that will help simplify things.
I am using PHP and MySQL (MyISAM tables presently, but will use INNODB if there is a reason to do so).
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我确实更喜欢为不同的概念使用单独的外部参照表。这样，一个概念与其任务的关系就更容易维护，并且与其他概念与任务的关系隔离开来。
如果您的设计中的任何表都可以有任务，那么我也许会倾向于为所有相关概念使用一个外部参照，然后我只需要一个额外的列来指示哪种类型对象是任务的父级。
I do prefer to have a separate xref table for the different concepts. This way, the relationship from a concept to its tasks is easier to maintain and isolated from the other concept's relationships to tasks.
I'd maybe favor a single xref for all related concepts if any table in your design could have tasks, and then I'd just have an extra column to indicate which kind of object is the parent of the tasks.
如果task_xref 具有单独的project_id 和opportunity_id 字段，则您的SQL 连接将会更加整洁。这是一种很好的方法，因为查询很简单。此外，只需查看哪个外键不为空，就可以轻松判断任务类型。这也很有效。与任务连接时，需要两个查询，因为项目和机会无疑具有不同的结构，因此结果无论如何都无法
联合。Your SQL joins will be neater if task_xref has separate project_id and opportunity_id fields. It's a good way of doing it, because queries are straightforward. Also, it's simple to tell what type of task it is, by simply seeing which of the foreign keys is not null. It's also efficient. When joining with tasks, two queries are needed, since project and opportunities will no doubt have different structures, so the results can't be
union'd anyway.