如何处理多于两个表的多对多关系? [英] How to handle many-to-many relationships with more than 2 tables?

问题描述

这就是我现在所在的位置.我有四个表:任务，项目，机会和task_xref.项目表和机会表均与任务具有一对多关系.我将这些关系存储在task_xref中.每个表的模式如下所示(简化):

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)

假定项目和机会中的键将不相同(GUID)，因此机会无法获取项目的任务，依此类推.这在表面上很好用，一个外部参照表可维护任务与项目，机会之间的关系(或将来可能需要任务关系的任何其他表).

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).

我目前的困境是双向的.如果我希望获得单个项目或机会的所有任务，那没有问题.如果我要撤回一项任务，并且想知道相关项目的名称或机会，那我就不知道了.我无法知道相关的fkey是项目还是机会.将来，我可能还会有其他具有任务关系的表.虽然我现在有2张桌子，但将来可能还会更多.

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.

以下是我到目前为止想到的可能解决方案: 1)每对都有单独的外部参照表(例如task_project_xref，task_opportunity_xref ...) 缺点:我必须为每个外部参照表运行查询，以查找任务的关系

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)task_xref中的第三列指向父表 缺点:对我来说这似乎是个错误

2) a third column in task_xref to point to the parent table cons: this seems like a kludge to me

3)以可识别的方式(例如proj1，proj2，proj3，opp1，opp2，opp3)存储项目中的主键和机会，因此我可以通过查看fkey来确定任务与哪个表相关 缺点:感觉就像我在使项目和机会中的主键变得神奇，使它们具有更多的意义，而不仅仅是成为单个记录的标识符(也许我想得太多)

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.

我正在使用PHP和MySQL(目前是MyISAM表，但如果有理由将使用INNODB).

I am using PHP and MySQL (MyISAM tables presently, but will use INNODB if there is a reason to do so).

推荐答案

我更喜欢为不同的概念提供单独的外部参照表.这样，一个概念与其任务之间的关系就更易于维护，并且与另一个概念与任务之间的关系相隔离.

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.

这篇关于如何处理多于两个表的多对多关系?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！