What SQL Classes don't normally cover - Database Normalization

A fundamental knowledge of Database Normalization will lead to a deeper understanding of how databases actually work; it will also allow you to design at a conceptual level an elegant and efficient database. Most SQL classes don't have time to cover this. So we hope this brief overview, will get you started.

Normalization is the process of organizing data in a database; it's the first step in the design process. This includes creating tables and establishing relationships between those tables according to principals designed both to protect the data and to make the database more flexible by eliminating redundancy and inconsistent dependencies.

Redundant data wastes disk space and creates maintenance problems. If data that exists in more than one place must be changed, the data must be changed in exactly the same way in all locations. A customer phone number change is much easier to implement if that data is stored only in the Customers table and nowhere else in the database.

There are different levels of database normalization. These are generally called the First Normal Form, Second Normal Form and Third Normal Form.

First Normal Form Guidelines

Second Normal Form

A record should not depend on anything other than the table’s primary key (or composite key). For example, consider a customer’s address. The address may be needed by the Customers table, but also by the Orders, Shipping, Invoices, Accounts Receivable, and Collections tables. Instead of storing the customer’s address as a separate entry in each of these tables, store it in one place, either in the Customers table (or possibly in a separate Addresses table).

Third Normal Form

Normally, any time the contents of a group of fields may apply to more than a single record in the table, consider placing those fields in a separate table.

Other Normalization Forms
There is a fourth normal form, called Boyce Codd Normal Form (BCNF), and a fifth normal form, but these are rarely used in practical design. Disregarding these rules may result in less than perfect database design, but should not affect functionality.

A Database Normalization Example

The following graphic gives an example of a typical conversion of an un-normalized table to multiple normalized tables.

Advantages of Database Normalization:

We realize that SQL Classes don't usually spend much time on covering database normalization, so we hope this brief overview will give you a head start with your SQL Classes in the future and perhaps spark your interest into your own further research.