Relational Database Design - Data Models, Continued

Data Model

In the earlier section - Data Model Basics- I said that there were only two things on a Data Model to be concerned about and that a data model always looked like this:

Data Model Parent Child Example

Poor Little Orphans

The above example shows that ONE parent MAY have MANY Children. It also signifies that a Child must have a parent. So, how about Orphan Annie?

Child records may often exist as Orphans and there are many ways of showing this on a Data Model, I prefer using a dotted line to link the two Entities:

Data Model Parent Child Example

Strange Relations - One To One

Imagine if you will a Data Model that looks like this:

Data Model Parent Child Example

A One To One Relationship? Not in my world! You could say that a person does have a one to one relationship with their shoe size. But shoe size is an ATTRIBUTE of the person. If you were to start mistaking attributes for Entities then you could end up with every entity on your data model looking like this:

Data Model Parent Child Example

Spaghetti Data Model! Some designers will use separate entities to logically group data within an entity. This breaks all the Rules of Codd, the Laws of the Universe and worse it really annoys me!

One to One Relationships are bad, wrong, evil and naughty!

Strange Relations - Many To Many

This type of relationship causes a lot of confusion.

It is the most second most important type of relationship in your Data Model. I have never worked on a project that did not have many to many relationships - but this vital type of relationship must NEVER be seen on a data model!

In fact this relationship is so important it's going to have it's own section - Resolving Many To Many Relationships