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:
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:
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.
Now the Data will look like this (let’s add the Primary Key column):
|Parent Child Link|
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|