Microsoft Word - Sam's Teach Yourself MySQL in 21 Days - SAMS.doc

Address (^)
Now all your tables are in Third Normal Form. This provides the most flexibility and prevents any logic
errors when inserting or deleting records. Each column in the table is uniquely identified by the key, and
no data is repeated. This provides a clean, elegant schema that is easy to work with and easy to
expand.

How Far to Take Normalization

The next decision is how far to go with normalization. Normalization is a subjective science. It is up to you to
determine what needs to be broken down. If your database is just going to provide data to a single user for a
simple purpose and there is little to no chance of expansion, taking your data to 3NF might be a little
extreme. The rules of normalization exist as guidelines to create easily manageable tables that are flexible
and efficient.

There are times when normalizing your data to the highest level doesn't make sense. For example,

suppose you added another address column to your database. It is quite normal to have two lines for an

address. The table schema might look like the following:

Customer_ID

Last_Name

First_Name

Address1

Address2

According to the rules that would make this table compliant with First Normal Form, the address

columns would be taken out and replaced with the key for the new table. The following is the resulting

schema:

Customer_ID Address_ID

Last_Name Customer_ID

First_Name Address

The database is now First Normal Form compliant. Your customers can have more than one address.

The problem that exists is that you have overcomplicated a simple idea because you were trying to

follow the rules of normalization. In the example, the second address is totally optional. It is there just to

collect information that might be used for contact information. There is really no need to break it into its

own table and force the rules of normalization on it. In this instance, taking it to a form of normalcy

defeats the purpose for which the data is used. It adds another layer of complexity that is not needed. A

good way to determine if your normalizing is getting carried away is to look at the number of tables you

have. A large number of tables may indicate that you are normalizing too much. Take a step back and

look at your schema. Are you breaking things down just to follow the rules, or is it a practical

breakdown. These are the things that you, the database designer, need to decide. Experience and

common sense will guide you to make the right decisions. Normalizing is not an exact science; It is a

subjective one.

There are six more levels of normalization that have not been discussed so far. They are Boyce-Codd

Normal Form, Fourth Normal Form (4NF), Fifth Normal Form (5NF), Strong Join-Protection Normal

Form, Over-Strong Join-Protection Normal Form, and Domain Key Normal Form. These forms of

normalization may take things further than they need to go. They exist to make a database truly

relational. They mostly deal with multiple dependencies and relational keys. If you are familiar with this

level of normalization, you probably don't need this book.

Summary

Normalization is a technique used to create good logical relationships between tables in a database. It helps
prevent logical errors when manipulating data. Normalization also makes adding new columns easier without
disrupting the current schema and relationships.

There are several levels of normalization: First Normal Form (1NF), Second Normal Form (2NF), Third

Normal Form (3NF), Boyce-Codd Normal Form, Fourth Normal Form (4NF), Fifth Normal Form (5NF),