Relational Database Design - Data Models, Resolving Many To Many

Data Model

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:

Data Model Parent Child Example

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

Data Model Parent Child Example

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:

Parent_ID Child_ID
1 1
1 2
1 3
2 1
2 2
2 3