Geek Answers Handbook

One-to-many relationship in one table

I am trying to use a one-to-many relationship in a single table. For example, let's say I have a group 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

Coldplay can be a subsidiary of Capital Records, and a child from Management Company and Gnarls Barkley can only be a child of Atlantic Records.

What is the best way to represent this relationship. I use PHP and mySQL. I also use PHP-Doctrine as my ORM if that helps.

I thought that I would need to create a link table called group_groups that would have 2 columns. owner_id and group_id. However, I'm not sure if this is the best way to do this.

Any insight would be appreciated. Let me know if I explain my problem well enough.

+4
source share
8 answers

There are a number of potential problems with this approach, but with a minimal understanding of the requirements, it says here:

There are apparently three “entities” here: Artist / Band, Label / Recording Co. and Management Co.

Artists / groups may have a CO label / record Artists / groups may have Management Co.

Label / Recording Co may have several artists / bands

Management Co may have several artists / groups

Thus, there is a one-to-many relationship between Recording Co and Artists, as well as between management and artists.

Record each object only once in your table with a unique identifier.

Put the key "one" in each instance of "many" - in this case, Artist / Band will have both a record identifier and a Co Management Management identifier

Then your request will eventually join Artist, Recording Co and Management Co.

In this structure, you do not need intersection tables, there is a clear separation of "entities", and the query is relatively simple.

+7
source

Several variants:

The simplest: if each group can have only one parent, then you just need the "ParentID" field in the main table.

If the relationship can be more complex, then yes, you need some sort of link table. Maybe even a “relationship type” column to determine what the relationship is between the two groups.

+3
source

In this particular case, it would be wise for you to follow Ken G's recommendations, as it really seems that you are modeling three separate objects in one table.

In general, it is possible that this may occur. If you had a “person” table and they modeled who all the friends were, for a far-fetched example.

In this case, you really will have a “link” or an associative or marriage table to manage these relationships.

+1
source

I agree with Ken G and JohnMcG that you should separate controls and shortcuts. However, they may forget that a group may have several managers and / or several managers over a period of time. In this case, you will need many, many relationships.

  • management has many groups.
  • the group has a lot of management.
  • label has many stripes band
  • has many shortcuts

In this case, the correct idea of ​​using a relationship table is correct. This happens with many relationships to many. However, group_groups can be called better.

Ultimately, this will depend on your requirements. For example, if you save the names of the CDs, you might prefer to label the specific CD rather than the group.

0
source

This is similar to joining STIs (single table inheritance) and nested sets / tree structures. Nested set / trees - one parent for 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

0
source

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

Just install actAs NestedSet

0
source

Yes, you will need a bridge that contains the fields that you described. However, I would think that your table should be split if it matches the same type of entities as you describe.

-1
source

(I assume there is an id column that can be used for links).

You can add a column called parent_id (allow null) and store the parent group id in it. Then you can join sql, for example: "Select a., B. From the parent of the parent group to parent.id = child.parent_id".

I recommend using a separate table for this link because: 1. You cannot support multiple parents with a field. You must use a separate table. 2. Import / export / delete is difficult with the field in the table, as you may encounter key conflicts. For example, if you are trying to import data, you must make sure that you import the parents first and then the children. With a separate table, you can import all groups, and then all relationships, without worrying about the actual order of the data.

-1
source

More articles:


All Articles