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:
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
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 |