More highly normalized tables reduce data duplication and opportunities for various
kinds of logical inconsistencies that could lead to loss of integrity of the database. There
are two goals of the normalization process: eliminating redundant data (for example,
storing the same data in more than one table) and ensuring data dependencies make sense
(only storing related data in a table). Both of these are worthy goals as they reduce the
amount of space a database consumes and ensure that data is logically stored.
Problems addressed by normalization
• The same information can be expressed on multiple records; therefore updates to
the table may result in logical inconsistencies. For example, each record in an
unnormalized "Employees' Skills" table might contain an Employee ID,
Employee Address, and Skill; thus a change of address for a particular employee
will potentially need to be applied to multiple records (one for each of his skills).
If the update is not carried through successfully—if, that is, the employee's
address is updated on some records but not others—then the table is left in an
inconsistent state. Specifically, the table provides conflicting answers to the
question of what this particular employee's address is. This phenomenon is known
as an update anomaly.
• There are circumstances in which certain facts cannot be recorded at all. In the
above example, if it is the case that Employee Address is held only in the
"Employees' Skills" table, then we cannot record the address of an employee
whose skills are not yet known. This phenomenon is known as an insertion
anomaly.
• There are circumstances in which the deletion of data representing certain facts
necessitates the deletion of data representing completely different facts. For
example, suppose a table has the attributes Student ID, Course ID, and Lecturer
ID (a given student is enrolled in a given course, which is taught by a given
lecturer). If in the early stages of enrolment the number of students on the course
temporarily drops to zero, then the last of the records referencing that course must
be deleted—meaning, as a side-effect, that the table no longer tells us which
lecturer has been assigned to teach the course. This phenomenon is known as a
deletion anomaly.
Normalization
Well normalized data makes programming (relatively) easy, and works very well in
multi-platform, enterprise wide environments. Non-normalized data leads to heartbreak.
Normalization: The first three forms
First Normal Form:
No repeating groups. As an example, it might be tempting to make an invoice table with
columns for the first, second, and third line item (see above). This violates the first
normal form, and would result in large rows, wasted space (where an invoice had less
than the maximum number of line items), and *horrible* SQL statements with a separate
join for each repetition of the column. First form normalization requires you make a
separate line item table, with it's own key (in this case the combination of invoice number
and line number) (See below).
Second Normal Form:
Each column must depend on the *entire* primary key. As an example, the customer
information could be put in the line item table (see above). The trouble with that is that
the customer goes with the invoice, not with each line on the invoice. Putting customer
information in the line item table will cause redundant data, with it's inherant overhead
and difficult modifications. Second form normalization requires you place the customer
information in the invoice table (see below).
Third Normal Form:
Each column must depend on *directly* on the primary key. As an example, the
customer address could go in the invoice table (see above), but this would cause data
redundancy if several invoices were for the same customer. It would also cause an update
nightmare when the customer changes his address, and would require extensive
programming to insert the address every time an existing customer gets a new invoice.
Third form normalization requires the customer address go in a separate customer table
with its own key (customer), with only the customer identifier in the invoice table (see
below).