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.

Foreign Key Relationships and Referential Integrity Constraints

• 3NF schemas in data warehousing environments often resemble the data model of its OLTP source systems, in which the logical consistency between data entities is expressed and enforced through primary key — foreign key relationships, also known as parent-child relationship.
• A foreign key resolves a 1-to-many relationship in relational system and ensures logical consistency: for example, you cannot have an order line item without an order header, or an employee working for a non-existent department.
• While such referential are always enforced in OLTP system, data warehousing systems often implement them as declarative, non-enforced conditions, relying on the ETL process to ensure data consistency.
• Whenever possible, foreign keys and referential integrity…