在相同的表一对多关系One-to-many relationship in the same table
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.
(原文：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.
(原文：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:
我认为最好的是使用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.
(原文：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.
- MySQL条件语句MySQL conditional statement
- 我怎么能有MySQL写输出文件作为不同的用户? - How can I have MySQL write outfiles as a different user?
- SQL查询字段不包含$ x的地方SQL Query Where Field DOES NOT Contain $x
- MySQL更新错误当使用特殊字符MySQL update error when special characters are used
- 的最佳实践是什么命名数据库表提供自然组织?What is the best practice for naming database tables to provide natural organization?
- 在相同的表一对多关系One-to-many relationship in the same table
- 在MySQL外键吗?Foreign keys in MySQL?
- 帮助编写一个查询:混乱的顺序操作的GROUP BY和秩序Help write a query: Confusion over order of operations of GROUP BY and ORDER BY
- MySQL加密列MySQL encrypted columns
- 全文搜索相关性测量在吗? - Full-text search relevance is measured in?
- 如何编写一个mysql查询,将读取数据从一个表和写信给另一个?How do I write a mysql query that will read data from one table and write to another?