Resolving Many To Many

You will probably have been shouting at me over the last few sections – ‘Oy Simon! Children can have many Parents, not just one!’. Let’s have a Data Model showing that:

parent_child_m2m

Remember that Parent / Child just signifies the cardinality – One Entity joined to Many entities, it’s not literally mummies, daddies and kiddies!

Ok, so what is wrong with the above Data Model?

A Foreign Key may only point to One ‘Parent’ Record’

In the Data Model above we don’t have a ‘Parent’ or ‘ONE’ end of the relationship.

As an example Mr and Mrs Jones have 3 children – Julie, Harry and Will. Using the above Data Model we would need two Entities holding data like this:

Parent Entity
Name Child
Mrs Jones Julie
Mrs Jones Harry
Mrs Jones Will
Mr Jones Julie
Mr Jones Harry
Mr Jones Will

 

Child Entity
Child  Parent
Will Mrs Jones
Harry Mrs Jones
Julie Mrs Jones
Will Mr Jones
Harry Mr Jones
Julie Mr Jones

I think this smashes all our Normalisation rules as there is a lot of duplication here.

So we need to resolve this many to many relationship using a Link Entity.

Link Entities

parent_child_link

Now the Data will look like this (let’s add the Primary Key column):

Parents
ID Parent
1 Mrs Jones
2 Mrs Jones

 

Children
ID Child
1 Julie
2 Harry
3 Will

 

Parent Child Link
Parent Child
Mr Jones Will
Mr Jones Harry
Mr Jones Julie
Mrs Jones Will
Mrs Jones Harry
Mrs Jones Julie

 

OK, so that still looks like a lot of duplication – BUT…. when we create our final tables the Parent and Child Columns in the Link entity will actually be populated with Foreign Keys to the Parent and Child tables, so the data will really look like this:

Actual Data in Link Entity
Parent_ID Child_ID
1 1
1 2
1 3
2 1
2 2
2 3

Back to Top