在相同的表一对多关系One-to-many relationship in the same table

- 此内容更新于:2014-12-30
主题:

原文:

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:

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.

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.

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.

Any insight would be appreciated. Let me know if I explained my problem good enough.

jonnii的回复:你为什么需要一个树结构吗?为什么可以# 39;t为艺术家,一个表和一个表管理公司吗?

(原文:Why do you need a tree like structure? Why can't you have a table for artists, and a table for management companies?)

Totty的回复:是的,我认为这是真正需要回答的第一个问题。

(原文:Yes, I think that is really the first question that needs to be answered.)

Philippe Grondier的回复:表为对象相同的性质。不要混合不同的对象在一个表中。

(原文:Tables are made for objects of the same nature. Do not mix different objects in one table.)

Philippe Grondier的回复:每个人都回答这个问题应该得到- 1 !

(原文:Everybody answering this question should get a minus 1!)

Mastermind的回复:也许你不应该判断的例子(确实是有问题的),只是回答这个问题。或者你认为关系在同一表坏在任何情况下?如果是这样,为什么?

(原文:Perhaps you should not judge the example (which is indeed problematic) and just answer the question. Or do you think a relation within the same table is bad in any case? If so, why?)

解决方案:
原文:

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.

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.

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

Then your query will ultimately join Artist, Recording Co and Management Co.

With this structure, you don't need intersection tables, there is a clear separation of "entities" and the query is relatively simple.

Philippe Grondier的回复:我很佩服你的耐心……在你的地方我就会down-clicked问题!一票,你的好意!

(原文:I admire your patience ... In your place I would have down-clicked the question! One vote up for your kindness!)

Jonathan Leffler的回复:一种可能性不覆盖——可能有其他公司的公司管理。也就是说,可能有标签,管理公司的集合;可能有管理公司标签的集合。等。不清楚的问题。

(原文:One possibility not covered - there could be companies that manage other companies in general. That is, there might be labels that have a collection of management companies; there might be management companies that have collections of labels. Etc. Not clear from the question.)

ysth的回复:它是怎样善良只是假设这个提问者是错误的吗?我能看到建议做事为这样的问题# 39;t有关,但不只是假设。

(原文:How is it kindness to just assume the questioner is wrongheaded? I can see suggesting doing things such that the question isn't relevant, but not just assuming.)

Ken Gentle的回复:谁说提问者是& # 39;错误# 39;?

(原文:Who said the questioner was 'wrongheaded'?)

解决方案:
两个选项: 简单:如果每组只能有一个父,那么你只需要一个“ParentID”字段表。 如果可以比这更复杂的人际关系,那么是的,你需要某种形式的链接表。甚至“关系类型”列定义两组之间是什么样的关系。
原文:

A couple of options:

Easiest: If each group can only have one parent, then you just need a "ParentID" field in the main table.

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.

解决方案:
原文:

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.

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.

解决方案:
原文:

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.

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

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.

解决方案:
这似乎是一个合并的STI(单表继承)和嵌套集/树结构。嵌套组/树是一个家长多个孩子: http://jgeewax.wordpress.com/2006/07/18/hierarchical-data-side-note/ http://www.dbmsmag.com/9603d06.html http://www.sitepoint.com/article/hierarchical-data-database
原文:

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:

http://jgeewax.wordpress.com/2006/07/18/hierarchical-data-side-note/

http://www.dbmsmag.com/9603d06.html

http://www.sitepoint.com/article/hierarchical-data-database

解决方案:
我认为最好的是使用NestedSet http://www.doctrine-project.org/documentation/manual/1_0/en/hierarchical-data嵌套式 把找NestedSet
原文:

I think best of all is to use NestedSet http://www.doctrine-project.org/documentation/manual/1_0/en/hierarchical-data#nested-set

Just set actAs NestedSet

解决方案:
是的,你需要一个桥,你描述的字段。然而,我认为你的表应该分裂后如果是像你描述的相同类型的实体。
原文:

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.

seanbrant的回复:由分割你什么意思,你能解释吗?

(原文:What do you mean by split, could you explain?)

Totty的回复:作为jonnii建议在上面的评论,你应该把你的表,每个表,在最好的情况下,只有一个单一的实体。这样专辑的所有数据都在一个表,艺术家在另一个,在另一个标签,等等。

(原文:As jonnii suggested in the comment above, you should split your tables so that each table will, in the best case, hold only a single entity. That way all data for albums is in one table, artists in another, labels in another, etc.)

解决方案:
原文:

(I am assuming there is an id column which can be used for references).

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

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.