Simplifying Normalization and Database Relationships

Normalizaiton and relationships seemed like rocket science when I first took a class in database management back in 1996. Now, it seems like common sense.

The most important thing to understand about database design is a very simple concept.

Each table must be about a well defined subject.

Each column needs to be directly about that subject, unless there are related subjects.

Now, the "unless" here is what throws students, but is not a big deal if you follow a simple rule of normalization.

Every table must have a primay key field that uniquely identifies each row (a record id).

If so, you should be able to use the key field as a substitute for the subject of a table when we have related tables.

For example, when you place an order the only thing to add to orders is the customer id. It is the substitute for the subject of customer.

That is 60% of what you need to know design and normalize tables.

Relationships seem complicated, but that you cannot design a many to many into a database and one to one are rare it simplifies things.

No database has a Many-to-Many relationship.

All relationships are created using the SQL Foriegn Key (FK) Constraint.

When you add an FK Constraint to a field in a table you also specify a primary key field in another table that it is related to.

Then, when you add a record to the table it will not save a record unless that value exists in the related table.

.

The most common situation, by far, is for the FK field to NOT be the primary key of its table.

This becomes the one to many relationship.

*For example, we would add the FK constraint to the customerid field in an orders table to relate customer to orders.

One customer can have many orders.

*We would add the FK constraint to the studentid field to a class registration table to relate registrations to students.

One student can register for many classes.

The only time the relationship is not one to many is when we add an FK constraint to a primary key field.

Because the FK constraint must relate to another primary key and primary keys are unique to their tables,

one record in the first table is related to one and only one record in the second.

Why would you do that? Relate two primary keys?

A table should be about a single subject.

However, some subjects have types, sometimes called subtypes.

There are different types of students, different types of customers, different types authors, etc.

Sometimes you handle the differences by simply adding a field to the subject table to define the type.

However, sometimes a type requires fields that are unique to it. If you include fields in a table that are specific to a type the field will be empty for all other types.

This can be very inefficient.

The

...