| Isolate Independent Multiple Relationships ( 4NF) |
This applies primarily to key-only associative tables, and appears as a
ternary relationship, but has incorrectly merged 2 distinct, independent
relationships.
The way this situation starts is by a business request list the one shown
below. This could be any 2 M:M relationships from a single entity. For instance,
a member could know many software tools, and a software tool may be used by many
members. Also, a member could have recommended many books, and a book could be
recommended by many members.
Initial business request
So, to resolve the two M:M relationships, we know that we should resolve them
separately, and that would give us 4th normal form. But, if we were to combine
them into a single table, it might look right (it is in 3rd normal form) at
first. This is shown below, and violates 4th normal form.
Incorrect solution
To get a picture of what is wrong, look at some sample data, shown below. The
first few records look right, where Bill knows ERWin and recommends the ERWin
Bible for everyone to read. But something is wrong with Mary and Steve. Mary
didn't recommend a book, and Steve Doesn't know any software tools. Our solution
has forced us to do strange things like create dummy records in both Book and
Software to allow the record in the association, since it is key only table.
Sample data from incorrect solution
The correct solution, to cause the model to be in 4th normal form, is to
ensure that all M:M relationships are resolved independently if they are indeed
independent, as shown below.
Correct 4th normal form
NOTE! This is not to say that ALL ternary associations are invalid.
The above situation made it obvious that Books and Software were independently
linked to Members. If, however, there were distinct links between all three,
such that we would be stating that "Bill recommends the ERWin Bible as a
reference for ERWin", then separating the relationship into two separate
associations would be incorrect. In that case, we would lose the distinct
information about the 3-way relationship.
|
