Table Normalization

Normalization

Normalizing tables is the act of designing effiecient relationships. This comes in four flavors 1) Singular Seperate Values 2) reduction of redundancy 3) analyze and reduce additional redundancy 4) manageability. Database designers refer to this normalization as forms and would say 1st normal, second normal, and thrid normal. The 4th I have added as a realistic precaution and worth exploring.

First Normal Form

During this stage we look to seperate information into single fields. For example, an address could be placed into a single field. Do this reduces our ability to query by city, or state. So the goal is to seperate these elements into their own fields. Address, city, state_province, zip_postal.

Second Normal Form

In the second normal form the goal is to use a seperate table for multiple records. Instead of having both the users information and address information in one table, the addresses could be placed in another table. This allows users to add multiple addresses (home, work, summer home).

Third Normal Form

Convert many to many to one to many with intermediary tables to join the tables together. In our music database, we could create a table for artists and a table for bands. We could then create a intermediary table that joins the two together, perhaps called artists_to_albums. The artists_to_albums table may just have a primary key and two foreign keys to join primary keys of both original tables together.

Manageability

When designing the tables the general goal is to reduce redundancy but keep the tables manageable for the project at hand. If you followed all of the rules of normalization you would remove all redundancy, but you may also create a beast of tables that are difficult to manage and keep straight. At the end of the day, the designer has to come to a decision that works best for the needs of the web application and the people that will be interfacing with it.