Database Design and Normalization

Example: Orders(OrderID, CustomerName, CustomerCity, ProductList) → 1NF: split ProductList into rows → 2NF/3NF: move customer data to Customers, products to Products, and line items to OrderItems.

17.1 Data Modeling Process

Conceptual model: entities and relationships (ER diagram)

Logical model: attributes, keys, normalization Physical model: data types, indexes, partitioning, storage

17.2 ER Modeling

Entities, attributes (simple, composite, multi-valued, derived), relationships

Cardinality: 1:1, 1:N, M:N (resolved with a junction/bridge table) Notations: Chen, Crow's Foot, IDEF1X

Weak entities, supertype/subtype (inheritance) patterns

17.3 Normalization

Atomic values, no repeating groups, each row unique 1NF + no partial dependency on part of a composite key

2NF + no transitive dependency (non-key → non-key) Every determinant is a candidate key No multi-valued dependencies No join dependencies

Example: Orders(OrderID, CustomerName, CustomerCity, ProductList) → 1NF: split ProductList into rows → 2NF/3NF: move customer data to Customers, products to Products, and line items to OrderItems.

17.4 Anomalies

Insertion, update, and deletion anomalies. Normalization prevents them.

17.5 Denormalization

A deliberate trade-off for read performance: summary tables, computed or persisted columns, indexed views, reporting databases.

17.6 Key Design Choices

Natural vs surrogate keys (INT IDENTITY, SEQUENCE, GUID)

Random GUID clustered keys cause page splits and fragmentation. Prefer NEWSEQUENTIALID() or INT/BIGINT.

Narrow, static, unique, ever-increasing clustering keys

17.7 Data Warehouse Modeling

Star schema: fact table plus dimension tables. Snowflake schema: normalized dimensions.

Fact types: transactional, periodic snapshot, accumulating snapshot

Slowly Changing Dimensions (SCD): Type 0, 1 (overwrite), 2 (history rows), 3 (previous-value column), 4, 6

Kimball vs Inmon vs Data Vault vs Lakehouse/medallion

17.8 Common Design Patterns and Anti-Patterns

✅ Lookup tables, audit columns (CreatedAt, ModifiedBy), soft deletes, junction tables

❌ EAV (Entity-Attribute-Value) overuse, comma-separated lists in columns, "one true lookup table", missing FKs, storing derived data without a sync plan