Data Modeling and Denormalization

Data modeling is the process of deciding which entities a system stores, how they relate, and how they are laid out in a database. The right model depends on the queries and the update patterns. Relational design optimizes for correctness and flexibility of queries, while NoSQL design and analytics design optimize for specific read patterns by duplicating data.

Normalization

Normalization organizes tables so each fact is stored once. It removes redundancy and prevents anomalies:

• Update anomaly. A fact stored in many rows must be changed in all of them, and a partial update leaves inconsistent data.
• Insertion anomaly. A fact cannot be stored without an unrelated fact (a customer cannot be added until they place an order).
• Deletion anomaly. Removing one fact removes another (deleting the last order deletes the customer's address).

Normal forms:

Example: an orders table with columns order_id, customer_id, customer_name, customer_city, total depends transitively: customer_id determines customer_name and customer_city. In 3NF, customer attributes move to a customers table, and orders keeps only customer_id.

erDiagram
    CUSTOMER ||--o{ ORDER : places
    ORDER ||--|{ ORDER_ITEM : contains
    PRODUCT ||--o{ ORDER_ITEM : "appears in"
    CUSTOMER {
        int id PK
        string name
        string city
    }
    ORDER {
        int id PK
        int customer_id FK
        timestamp created_at
    }
    ORDER_ITEM {
        int order_id FK
        int product_id FK
        int quantity
        decimal unit_price
    }
    PRODUCT {
        int id PK
        string name
        decimal price
    }

Benefits of a normalized model: updates touch one place, storage is smaller, integrity is enforced by constraints (foreign keys, uniqueness), and any query can be answered later with joins.

Costs: reading an entity that spans many tables requires joins. Joins are efficient on one node with indexes, but they become expensive across partitions or at very high query rates.

Denormalization

Denormalization deliberately stores redundant data to make reads cheaper. Forms:

• Copied columns. Store customer_name in the orders row so order listings need no join.
• Precomputed aggregates. Keep comment_count on a post, or order_total on an order, instead of counting on every read.
• Pre-joined records. Store a whole order with its items and shipping address as one record.
• Duplicate tables per query. Keep the same data in several tables keyed differently (common in Cassandra and DynamoDB).

Trade-offs:

Example of the write cost: if customer_name is copied into every order row and a customer with 5,000 orders changes their name, 5,000 rows must be updated (or the old name kept in some rows). In the normalized model the change is one row in customers.

Denormalization suits data that is read much more often than written, or that never changes after creation. An order line's unit_price is copied from the product at purchase time on purpose: the historical price must not change when the product's price changes. That is a snapshot, not redundancy to be kept in sync.

Keeping copies consistent:

• Update all copies in one transaction, when they live in one database.
• Update them asynchronously from an event stream or change data capture, accepting a delay.
• Rebuild derived data periodically from the source of truth.
• Treat one copy as authoritative and the others as caches that can be regenerated.

Access-pattern-driven design

In NoSQL stores, the design starts from queries:

1. Enumerate the read queries and write operations with rates and latency needs.
2. Identify the entities and the aggregate that each query reads.
3. Lay out the data so each frequent query is served from one partition or document.
4. Duplicate data as needed, and define how duplicates are updated.

See key-value and document stores and wide-column, graph, and time-series stores. The result is fast for known queries and inflexible for new ones, which is the opposite of the normalized relational trade-off.

Embedding vs referencing

In document stores, a related entity is either embedded inside the parent document or referenced by ID.

Example: order lines are embedded in the order, because they are read with it, bounded in number, and never shared. A product is referenced from the order line by ID, with the name and price at purchase time copied into the line.

A middle option is the extended reference: reference by ID and also copy the few attributes that are needed for display (the author's name on a post), which avoids a lookup for common reads while allowing the full record to live elsewhere.

Materialized views

A view is a stored query. A materialized view stores the query result as a table, so reads are a table scan or index lookup instead of the full computation.

• Refresh. Recompute periodically (full refresh), on demand, or incrementally as source rows change. PostgreSQL provides REFRESH MATERIALIZED VIEW (full recomputation, optionally concurrent). Some databases maintain views incrementally.
• Staleness. Between refreshes the view lags the base data. The acceptable lag is a requirement to state.
• Distributed variants. Cassandra has materialized views maintained by the database, documented as experimental in recent releases. DynamoDB global secondary indexes and application-maintained projection tables serve the same purpose. Stream processors compute views continuously from event logs, as in event sourcing and CQRS.

flowchart LR
    W[Writes] --> S[("Source of truth: normalized tables")]
    S -->|"change events or scheduled refresh"| V[("Materialized view: denormalized for one query")]
    R[Reads] --> V

OLTP vs OLAP models

Star schema

A star schema has a central fact table with one row per event (a sale), holding numeric measures (quantity, amount) and foreign keys to surrounding dimension tables (date, product, store, customer), which are denormalized and hold descriptive attributes. Queries filter on dimensions and aggregate measures. A snowflake schema normalizes the dimensions further.

erDiagram
    FACT_SALES }o--|| DIM_DATE : on
    FACT_SALES }o--|| DIM_PRODUCT : of
    FACT_SALES }o--|| DIM_STORE : at
    FACT_SALES }o--|| DIM_CUSTOMER : by
    FACT_SALES {
        int date_key FK
        int product_key FK
        int store_key FK
        int customer_key FK
        int quantity
        decimal amount
    }

Column-oriented storage reads only the columns a query references and compresses similar values well, which fits aggregate scans.

Analytics data is usually loaded from OLTP systems through ETL or ELT pipelines or through CDC into a warehouse, so transactional load and analytical queries run on separate systems. See stream vs batch processing.

Choosing a model

• Start normalized when queries are not yet known or change often, and when integrity matters.
• Denormalize specific hot read paths when measurements show joins or fan-out are the cost.
• Use access-pattern-driven models when the store does not support joins or is partitioned.
• Separate analytical workloads from the transactional database.

Interview questions

• When is denormalization justified? When a read path is frequent and latency-sensitive, the duplicated data changes rarely relative to reads, and a mechanism exists to keep copies consistent or to tolerate staleness. Sharded stores without joins require it.
• Why is a copied unit_price in an order line not a normalization violation? It records the price at purchase time, a distinct fact from the product's current price. It must not change when the product price changes.
• Embed or reference a one-to-many relationship in a document store? Embed if the many side is bounded, read with the parent, and owned by it. Reference if it is unbounded, shared, large, or updated independently.
• Why use a star schema instead of 3NF for reporting? Reporting queries aggregate many rows across dimensions. Denormalized dimensions with a central fact table reduce joins and suit columnar scans, while 3NF favors transactional updates.