Im trying to use to define a one-to-many relationship in a single table. For example lets say I have a Groups table with these entries:
我试图用来在一个表中定义一对多的关系。例如,假设我有一个包含这些条目的Groups表:
Group:
Group_1:
name: Atlantic Records
Group_2:
name: Capital Records
Group_3:
name: Gnarls Barkley
Group_4:
name: Death Cab For Cutie
Group_5:
name: Coldplay
Group_6:
name: Management Company
The group Coldplay could be a child of the group Capital Records and a child of the group Management Company and Gnarls Barkley could only be a child of Atlantic Records.
Coldplay集团可能是Capital Records集团的子女,也是集团管理公司的子女,而Gnarls Barkley只能成为Atlantic Records的孩子。
What is the best way to represent this relationship. I am using PHP and mySQL. Also I am using PHP-Doctrine as my ORM if that helps.
表达这种关系的最佳方式是什么?我正在使用PHP和mySQL。我也使用PHP-Doctrine作为我的ORM,如果有帮助的话。
I was thinking that I would need to create a linking table called group_groups that would have 2 columns. owner_id and group_id. However i'm not sure if that is best way to do this.
我在想我需要创建一个名为group_groups的链接表,它有2列。 owner_id和group_id。但是我不确定这是否是最好的方法。
Any insight would be appreciated. Let me know if I explained my problem good enough.
任何见解将不胜感激。如果我解释好我的问题,请告诉我。
8 个解决方案
#1
7
There are a number of possible issues with this approach, but with a minimal understanding of the requirements, here goes:
这种方法存在许多可能的问题,但是对要求的理解很少,这里有:
There appear to be really three 'entities' here: Artist/Band, Label/Recording Co. and Management Co.
这里似乎有三个“实体”:艺术家/乐队,标签/录音公司和管理公司
Artists/Bands can have a Label/Recording CO Artists/Bands can have a Management Co.
艺术家/乐队可以有标签/录音CO艺术家/乐队可以有管理公司
Label/Recording Co can have multiple Artists/Bands
标签/录制公司可以有多个艺术家/乐队
Management Co can have multiple Artists/Bands
管理公司可以有多个艺术家/乐队
So there are one-to-many relationships between Recording Co and Artists and between Management Co and Artists.
因此,唱片公司和艺术家之间以及管理公司和艺术家之间存在着一对多的关系。
Record each entity only once, in its own table, with a unique ID.
仅使用唯一ID在自己的表中记录每个实体一次。
Put the key of the "one" in each instance of the "many" - in this case, Artist/Band would have both a Recording Co ID and a Management Co ID
在“many”的每个实例中放置“one”的键 - 在这种情况下,Artist / Band将同时具有Recording Co ID和Management Co ID
Then your query will ultimately join Artist, Recording Co and Management Co.
然后您的查询将最终加入Artist,Recording Co和Management Co.
With this structure, you don't need intersection tables, there is a clear separation of "entities" and the query is relatively simple.
有了这个结构,你不需要交集表,有一个明确的“实体”分离和查询相对简单。
#2
3
A couple of options:
有两种选择:
Easiest: If each group can only have one parent, then you just need a "ParentID" field in the main table.
最简单:如果每个组只能有一个父级,那么您只需要在主表中使用“ParentID”字段。
If relationships can be more complex than that, then yes, you'd need some sort of linking table. Maybe even a "relationship type" column to define what kind of relationship between the two groups.
如果关系可能比那更复杂,那么是的,你需要某种链接表。也许甚至可以通过“关系类型”列来定义两组之间的关系。
#3
1
In this particular instance, you would be wise to follow Ken G's advice, since it does indeed appear that you are modeling three separate entities in one table.
在这个特定的例子中,你应该明智地遵循Ken G的建议,因为你确实在一个表中建模了三个独立的实体。
In general, it is possible that this could come up -- If you had a "person" table and were modeling who everybody's friends were, for a contrived example.
一般来说,这可能会出现 - 如果你有一个“人”表并且模拟了每个人的朋友,那就是一个人为的例子。
In this case, you would indeed have a "linking" or associative or marriage table to manage those relationships.
在这种情况下,您确实会有一个“链接”或关联或婚姻表来管理这些关系。
#4
0
I agree with Ken G and JohnMcG that you should separate Management and Labels. However they may be forgetting that a band can have multiple managers and/or multiple managers over a period of time. In that case you would need a many to many relationship.
我同意Ken G和JohnMcG您应该将管理和标签分开。然而,他们可能忘记了一个乐队在一段时间内可以拥有多个经理和/或多个经理。在这种情况下,您需要多对多的关系。
- management has many bands
- band has many management
- label has many bands
- band has many labels
管理层有很多乐队
乐队有很多管理层
标签有很多乐队
乐队有很多标签
In that case your orginal idea of using a relationship table is correct. That is home many-to-many relationships are done. However, group_groups could be named better.
在这种情况下,您使用关系表的原始想法是正确的。这就是家庭多对多的关系。但是,group_groups可以更好地命名。
Ultimately it will depend on your requirements. For instance if you're storing CD titles then perhaps you would rather attach labels to a particular CD rather than a band.
最终它将取决于您的要求。例如,如果您正在存储CD标题,那么您可能更愿意将标签附加到特定CD而不是乐队。
#5
0
This does appear to be a conflation of STI (single-table inheritance) and nested sets / tree structures. Nested set/trees are one parent to multiple children:
这似乎是STI(单表继承)和嵌套集/树结构的混合。嵌套集/树是多个子节点的父亲:
http://jgeewax.wordpress.com/2006/07/18/hierarchical-data-side-note/
#6
0
I think best of all is to use NestedSet http://www.doctrine-project.org/documentation/manual/1_0/en/hierarchical-data#nested-set
我认为最重要的是使用NestedSet http://www.doctrine-project.org/documentation/manual/1_0/en/hierarchical-data#nested-set
Just set actAs NestedSet
只需设置actAs NestedSet即可
#7
-1
Yes, you would need a bridge that contained the fields you described. However, I would think your table should be split if it is following the same type of entities as you describe.
是的,您需要一个包含您描述的字段的桥。但是,如果它跟随你描述的相同类型的实体,我认为你的表应该被拆分。
#8
-1
(I am assuming there is an id column which can be used for references).
(我假设有一个id列可以用于引用)。
You can add a column called parent_id (allow nulls) and store the id of the parent group in it. Then you can join using sql like: "Select a., b. from group parent join group child on parent.id = child.parent_id".
您可以添加名为parent_id(允许空值)的列,并将父组的ID存储在其中。然后你可以使用如下的SQL加入:“在parent.id = child.parent_id上从组父连接组子中选择一个。,b。”。
I do recommend using a separate table for this link because: 1. You cannot support multiple parents with a field. You have to use a separate table. 2. Import/Export/Delete is way more difficult with a field in the table because you may run into key conflicts. For example, if you try to import data, you need to make sure that you first import the parents and then children. With a separate table, you can import all groups and then all relationships without worrying about the actual order of the data.
我建议为此链接使用单独的表,因为:1。您不能支持具有字段的多个父级。你必须使用一个单独的表。 2.使用表中的字段导入/导出/删除更加困难,因为您可能会遇到关键冲突。例如,如果您尝试导入数据,则需要确保首先导入父项,然后导入子项。使用单独的表,您可以导入所有组,然后导入所有关系,而无需担心数据的实际顺序。
#1
7
There are a number of possible issues with this approach, but with a minimal understanding of the requirements, here goes:
这种方法存在许多可能的问题,但是对要求的理解很少,这里有:
There appear to be really three 'entities' here: Artist/Band, Label/Recording Co. and Management Co.
这里似乎有三个“实体”:艺术家/乐队,标签/录音公司和管理公司
Artists/Bands can have a Label/Recording CO Artists/Bands can have a Management Co.
艺术家/乐队可以有标签/录音CO艺术家/乐队可以有管理公司
Label/Recording Co can have multiple Artists/Bands
标签/录制公司可以有多个艺术家/乐队
Management Co can have multiple Artists/Bands
管理公司可以有多个艺术家/乐队
So there are one-to-many relationships between Recording Co and Artists and between Management Co and Artists.
因此,唱片公司和艺术家之间以及管理公司和艺术家之间存在着一对多的关系。
Record each entity only once, in its own table, with a unique ID.
仅使用唯一ID在自己的表中记录每个实体一次。
Put the key of the "one" in each instance of the "many" - in this case, Artist/Band would have both a Recording Co ID and a Management Co ID
在“many”的每个实例中放置“one”的键 - 在这种情况下,Artist / Band将同时具有Recording Co ID和Management Co ID
Then your query will ultimately join Artist, Recording Co and Management Co.
然后您的查询将最终加入Artist,Recording Co和Management Co.
With this structure, you don't need intersection tables, there is a clear separation of "entities" and the query is relatively simple.
有了这个结构,你不需要交集表,有一个明确的“实体”分离和查询相对简单。
#2
3
A couple of options:
有两种选择:
Easiest: If each group can only have one parent, then you just need a "ParentID" field in the main table.
最简单:如果每个组只能有一个父级,那么您只需要在主表中使用“ParentID”字段。
If relationships can be more complex than that, then yes, you'd need some sort of linking table. Maybe even a "relationship type" column to define what kind of relationship between the two groups.
如果关系可能比那更复杂,那么是的,你需要某种链接表。也许甚至可以通过“关系类型”列来定义两组之间的关系。
#3
1
In this particular instance, you would be wise to follow Ken G's advice, since it does indeed appear that you are modeling three separate entities in one table.
在这个特定的例子中,你应该明智地遵循Ken G的建议,因为你确实在一个表中建模了三个独立的实体。
In general, it is possible that this could come up -- If you had a "person" table and were modeling who everybody's friends were, for a contrived example.
一般来说,这可能会出现 - 如果你有一个“人”表并且模拟了每个人的朋友,那就是一个人为的例子。
In this case, you would indeed have a "linking" or associative or marriage table to manage those relationships.
在这种情况下,您确实会有一个“链接”或关联或婚姻表来管理这些关系。
#4
0
I agree with Ken G and JohnMcG that you should separate Management and Labels. However they may be forgetting that a band can have multiple managers and/or multiple managers over a period of time. In that case you would need a many to many relationship.
我同意Ken G和JohnMcG您应该将管理和标签分开。然而,他们可能忘记了一个乐队在一段时间内可以拥有多个经理和/或多个经理。在这种情况下,您需要多对多的关系。
- management has many bands
- band has many management
- label has many bands
- band has many labels
管理层有很多乐队
乐队有很多管理层
标签有很多乐队
乐队有很多标签
In that case your orginal idea of using a relationship table is correct. That is home many-to-many relationships are done. However, group_groups could be named better.
在这种情况下,您使用关系表的原始想法是正确的。这就是家庭多对多的关系。但是,group_groups可以更好地命名。
Ultimately it will depend on your requirements. For instance if you're storing CD titles then perhaps you would rather attach labels to a particular CD rather than a band.
最终它将取决于您的要求。例如,如果您正在存储CD标题,那么您可能更愿意将标签附加到特定CD而不是乐队。
#5
0
This does appear to be a conflation of STI (single-table inheritance) and nested sets / tree structures. Nested set/trees are one parent to multiple children:
这似乎是STI(单表继承)和嵌套集/树结构的混合。嵌套集/树是多个子节点的父亲:
http://jgeewax.wordpress.com/2006/07/18/hierarchical-data-side-note/
#6
0
I think best of all is to use NestedSet http://www.doctrine-project.org/documentation/manual/1_0/en/hierarchical-data#nested-set
我认为最重要的是使用NestedSet http://www.doctrine-project.org/documentation/manual/1_0/en/hierarchical-data#nested-set
Just set actAs NestedSet
只需设置actAs NestedSet即可
#7
-1
Yes, you would need a bridge that contained the fields you described. However, I would think your table should be split if it is following the same type of entities as you describe.
是的,您需要一个包含您描述的字段的桥。但是,如果它跟随你描述的相同类型的实体,我认为你的表应该被拆分。
#8
-1
(I am assuming there is an id column which can be used for references).
(我假设有一个id列可以用于引用)。
You can add a column called parent_id (allow nulls) and store the id of the parent group in it. Then you can join using sql like: "Select a., b. from group parent join group child on parent.id = child.parent_id".
您可以添加名为parent_id(允许空值)的列,并将父组的ID存储在其中。然后你可以使用如下的SQL加入:“在parent.id = child.parent_id上从组父连接组子中选择一个。,b。”。
I do recommend using a separate table for this link because: 1. You cannot support multiple parents with a field. You have to use a separate table. 2. Import/Export/Delete is way more difficult with a field in the table because you may run into key conflicts. For example, if you try to import data, you need to make sure that you first import the parents and then children. With a separate table, you can import all groups and then all relationships without worrying about the actual order of the data.
我建议为此链接使用单独的表,因为:1。您不能支持具有字段的多个父级。你必须使用一个单独的表。 2.使用表中的字段导入/导出/删除更加困难,因为您可能会遇到关键冲突。例如,如果您尝试导入数据,则需要确保首先导入父项,然后导入子项。使用单独的表,您可以导入所有组,然后导入所有关系,而无需担心数据的实际顺序。