Design Concepts for 3NF Schemas

The following section discusses some basic concepts when modeling for a data warehousing environment using a 3NF schema approach. Some key 3NF schema design concepts that are relevant to data warehousing are as follows:

1. Identifying Candidate Primary Keys
2. Foreign Key Relationships and Referential Integrity Constraints
3. Denormalization

Identifying Candidate Primary Keys

• A primary key is an attribute that uniquely identifies a specific record in a table. Primary keys can be identified through single or multiple columns. It is normally preferred to achieve unique identification through as little columns as possible — ideally one or two — and to either use a column that is most likely not going to be updated or even changed in bulk.
• If your data model does not lead to a simple unique identification through its attributes, you would require too many attributes to uniquely identify a single record, or the data is prone to changes, the usage of a surrogate key is highly recommended.
• Specifically, 3NF schemas rely on proper and simple unique identification since queries tend to have many table-joins and all columns necessary to uniquely identify a record are needed as join condition to avoid row duplication through the join.