Normalization
No discussion of relational database (DB) design is complete without a section on normalization. A normalized DB schema avoids certain anomalies when inserting, updating, or deleting data and, therefore, helps to keep consistent data in the database.
However, the absence of anomalies is only the tangible result of a deeper benefit of normalization  namely the correct identification and modeling of entities. The insert, update, and delete anomalies I've just referred to are the consequences of the redundancy introduced by improper or inadequate separation between distinct entities. The normalization procedure is, therefore, not just a technical chore to be done out of principle, but it can actively help to improve the understanding of the business domain.
Regrettably, the treatment of normalization is often prohibitively formal, and it suffers from a special, rather nonintuitive terminology. This is unfortunate since the outcome of a normalization procedure often evokes the reaction that it all is nothing more than common sense. I will try to offer explanations of expressions that you are likely to encounter in the literature as they come up in the following discussion.
Overview
Normalization is a process in which an initial DB design is transformed, or decomposed, into a different, but equivalent, design. The resulting schema is equivalent to the original one in the sense that no information is lost when going from one to the other.
The normalization procedure consists of a sequence of projections  that is, some attributes are extracted from one table to form a new one. In other words, tables are split up vertically. The decomposition is lossless, only if you can restore the original table by joining its projections.
Through such nonloss decompositions it is possible to transform an original schema into a resulting one that satisfies certain conditions, known as Normal Forms:
 The First Normal Form (1NF) addresses the structure of an isolated table.
 The Second (2NF), Third (3NF), and BoyceCodd (BCNF) Normal Forms address onetoone and onetomany relationships.
 The Fourth (4NF) and Fifth (5NF) Normal Forms deal with manytomany relationships.
These Normal Forms form a hierarchy in such a way that a schema in a higher normal form automatically fulfills all the criteria for all of the lower Normal Forms.
The Fifth Normal Form is the ultimate normal form with respect to projections and joins  it is guaranteed to be free of anomalies that can be eliminated by taking projections.
In the following discussion, any mention of keys refers to the conceptual keys formed from business data, not to any plainly technical surrogate keys which might have been defined.
First Normal Form
A table is said to be in First Normal Form (1NF), if all entries in it are scalarvalued. Relational database tables are 1NF by construction since vectorvalued entries are forbidden. Vectorvalued data (that is, entries which have more than one value in each row) are referred to as repeating groups.
The following relation violates 1NF because the SupplierID
forms a repeating group (here and in the following examples and text, primary key fields are in bold):

Repeating groups indicate a onetomany relationship  in other words, a relationship which in relational databases is treated using foreign keys. Note that the problem of repeating groups cannot be solved by adding any number of fields to a record; even if the number of elements of the vectorvalued data was fixed, finite, and predetermined, searching for a value in all these parallel fields is prohibitively cumbersome.
To achieve 1NF, eliminate repeating groups by creating separate tables for each set of related data.
To demonstrate the typical anomalies that occur in tables that are only 1NF, consider the following example:

Note the following problems:
 Insert: It is not possible to add a record for a customer who has never placed an order.
 Update: To change the address for a customer, this change has to be repeated for all of the customer's existing orders.
 Delete: Deleting the last order for a customer loses all information about the customer.
Functional dependency
The Second and Third Normal Forms address dependencies among attributes, specifically between key and nonkey fields.
By definition, a key uniquely determines a record: Knowing the key determines the values of all the other attributes in the table row, so that given a key, the values of all the other attributes in the row are fixed.
This kind of relationship can be formalized as follows. Let X and Y be attributes (or sets of attributes) of a given relationship. Then Y is functionally dependent on X if, whenever two records agree on their Xvalues, they must also agree on their Yvalues. In this case, X is called the determinant and Y is called the dependent. Since for any X there must be a single Y, this relationship represents a singlevalued functional dependency. If the set of attributes in the determinant is the smallest possible (in the sense that after dropping one or more of the attributes from X, the remaining set of attributes does no longer uniquely determine Y), then the dependency is called irreducible.
Note that functional dependency is a semantic relationship: It is the business logic of the problem domain, represented by the relation, which determines whether a certain X determines Y.
Second Normal Form
A table is in Second Normal Form (2NF) if every nonkey field is a fact about the entire key. In other words, a table is 2NF if it is 1NF and all nonkey attributes are functionally dependent on the entire primary key (that is, the dependency is irreducible).
Clearly, 2NF is only relevant when the key is composite (that is,
consisting of several fields). The following example describes a table
which is not 2NF since the WarehouseAddress
attribute depends only on WarehouseID
but not on PartID
:

To achieve 2NF, create separate tables for sets of values that apply to multiple records and relate these tables through foreign keys. The determinants of the initial table become the primary keys of the resulting tables.
Third Normal Form
A relation is in Third Normal Form (3NF) if it is 2NF and none of its attributes is a fact about another nonkey field. In other words, no nonkey field functionally depends on any other nonkey field. (Such indirect dependencies are known as transitive dependencies.)
The following example violates 3NF since the Location
is functionally dependent on the DepartmentID
:

To achieve 3NF, eliminate fields that do not depend on the key from the original table and add them to the table whose primary key is their determinant.
To summarize the normalization procedure up to and including Third Normal Form:
Every field in a record must depend on The Key (1NF), the Whole Key (2NF), and Nothing But The Key (3NF).
BoyceCodd Normal Form
BoyceCodd Normal Form (BCNF) is an extension of 3NF in the case with two or more candidate keys which are composite and overlapping (that is, they have at least one field in common). If these conditions are not fulfilled, 3NF and BCNF are equivalent. A table is BCNF if, and only if its only determinants are candidate keys.
In the following table, both {SupplierID, PartID}
, as well as {SupplierName, PartID}
, are candidate keys. The table is not BCNF since it contains two determinants (SupplierID
and SupplierName
) which are not candidate keys. (SupplierID
and SupplierName
are determinants, since they determine each other.)

However, either of the following decompositions is BCNF:

or

To achieve BCNF, remove the determinants which are not candidate keys.
Manytomany relationships and higher Normal Forms
Fourth and Fifth Normal Forms apply to situations involving manytomany relationships. In relational databases, manytomany relationships are expressed through crossreference tables.
As an example, consider a case of class enrollment. Each student can be enrolled in one or more classes and each class can contain one or more students. Clearly, there is a manytomany relationship between classes and students. This relationship can be represented by a Student/Class crossreference table:

The key for this table is the combination of StudentID
and ClassID
.
To avoid violation of 2NF, all other information about each student and
each class is stored in separate Student and Class tables, respectively.
Note that each StudentID
determines not a unique ClassID
, but a welldefined, finite set of values. This kind of behavior is referred to as multivalued dependency of ClassID
on StudentID
.
Fourth Normal Form
A table is in Fourth Normal Form (4NF) if it is 3NF and it does not represent two or more independent manytomany relationships.
Consider an example with two manytomany relationships, between
students and classes and between classes and teachers. Also, a
manytomany relationship between students and teachers is implied.
However, the business rules do not constrain this relationship in any
way  the combination of StudentID
and TeacherID
does not contain any additional information beyond the information
implied by the student/class and class/teacher relationships.
Consequentially, the student/class and class/teacher relationships are independent of each other  these relationships have no additional constraints. The following table is, then, in violation of 4NF:

As an example of the anomalies that can occur, realize that it is not possible to add a new class taught by some teacher without adding at least one student who is enrolled in this class.
To achieve 4NF, represent each independent manytomany relationship through its own crossreference table.
Fifth Normal Form
A table is in Fifth Normal Form (5NF) if it is 4NF and its information content cannot be reconstructed from several tables containing fewer attributes.
Consider again the student/class/teacher example, but now assume that there is an additional relationship between students and teachers. The previous example table is now 4NF, since all the relationships it describes are interrelated. However, it is not 5NF, since it can be reconstructed from three crossreference tables, each representing one of the three manytomany relationships:

To achieve 5NF, isolate interrelated manytomany relationships, introducing the required number of new tables to represent all business domain constraints.
Normalization in context
In practice, many databases are denormalized to greater or lesser degree. The reason most often stated has to do with performance  a denormalized database may require fewer joins and can, therefore, be faster for retrievals.
While this reasoning may be true, the usual caveats against premature optimization apply here as well as everywhere else. First, you should determine sufficiently that a performance problem exists and that the proposed denormalization improves it before introducing a conceptually suboptimal design.
Furthermore, a denormalized schema can be harder to update. The additional integrity checks that are necessary in this case may offset the performance gains for queries obtained through denormalization.
Finally, it should be noted that dealing with manytomany relationships raises some issues that cannot be fully resolved through normalization (Chris Date's article, "Normalization is no Panacea," in Resources covers this topic).
View Practical database design, Part 2 Discussion
Page: 1 2 3 4 Next Page: History tables and event logging