One-to-many relationship in the same table

Question

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

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.