One-to-many relationship in the same table

Question

Hi,

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.

Answer 1

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.

Answer 2

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.

Answer 3

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

Answer 4

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.

Answer 5

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.

Answer 6

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.

Answer 7

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

Answer 8

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