Normalization

Designing an efficient database

Here is an understandable article about how to put together a normalized database.

One topic covered is what should be used for a Primary or foreign key.

"In general, a key field should have these characteristics:

Should be One Field

It is possible to define multiple fields as the key fields of a table, but a single field is preferable. ...Far better to have a CustomerID number than a combination of other fields to define a customer.

Should be Numeric

Access offers an AutoNumber field that is a Long Integer which is ideal for key fields. These values are automatically unique for each record and supports multi-user data entry as well.

Should Not Change Over Time

A key field should not change over time. Once identified, like a social security number, it should never change.

Should be Meaningless

To ensure a key field doesn’t change over time, it should have no meaning and therefore no reason to change over time.

Here are a few of the other topic headings:

Understanding Your Data

• What Data Do You Need?
  
• What Are You Going to Do with the Data?
  
• How Is Your Data Related to Each Other?
  
• What Is Going to Happen to the Data Over Time?
  
• Learn How to Use Queries

Database Normalization Tips By Luke Chung, Founder and CEO of FMS Inc
Also see: Knowledgebase #283878:
Description of the database normalization basics
and:
Normalization Model



See all Topics