Data Modeling and Database Design
Domain 2 - Data Modeling and Database Design
This domain tests whether you can pick the right level of model, declare a grain, and defend normalization or denormalization with a reason rather than a preference.
Modeling levels
- Conceptual: entities and relationships in business language, no keys or data types.
- Logical: attributes, primary and foreign keys, cardinality, optionality, normalized to 3NF/BCNF, platform-independent.
- Physical: data types, indexes, partitioning, storage and vendor DDL.
- Cardinality (1:1, 1:N, M:N) and optionality (mandatory vs optional participation) are separate facts. A many-to-many relationship becomes an associative table carrying its own key.
Normalization and dimensional design
1NF removes repeating groups, 2NF removes partial dependency on part of a composite key, 3NF removes transitive dependency from non-key attributes, and BCNF requires every determinant to be a candidate key. Normalize operational (OLTP) models to protect integrity; denormalize deliberately in warehouses for read performance, accepting update anomalies as a known cost. In dimensional modeling, declare the grain first ("one row per order line"), then dimensions (descriptive context) and facts (numeric measures). Star schemas keep dimensions one join deep; snowflakes normalize dimension hierarchies at the price of extra joins. Surrogate keys insulate the warehouse from source-key changes and make slowly changing dimensions possible - Type 2 adds a new row with validity dates and a current flag.
Worked example
An order-line model: grain is one row per order line. Category, brand and department belong to a product dimension. If category naming changes and reporting needs history, model product as SCD Type 2 with effective_start, effective_end, is_current and a surrogate product_key; each fact row stores the surrogate valid at event time, so historical revenue keeps its original categorization. A customer with several phone numbers gets a separate child table, not repeated columns.
Traps
- Mixing grains in one fact table (order-level and line-level measures together).
- Fan and chasm traps: joining facts through a dimension that is not unique per key, inflating every SUM().
- Assuming 3NF is always "correct" for a warehouse, or that denormalization is always faster.
- Nullable foreign keys representing optional relationships without documented meaning.
- No history strategy, so the model can only answer "as of now".