Database Normalization

Key Points

• Normalize until 3NF; denormalize on purpose, with a sync strategy. That's the senior's one-line rule.
• 1NF / 2NF / 3NF are about removing duplication driven by functional dependencies. BCNF is the stricter cousin of 3NF; 4NF / 5NF handle multi-valued and join dependencies and rarely come up outside textbooks.
• Transactional system → 3NF. Reporting → star schema. CQRS read model → denormalize freely. NoSQL document → denormalize for read paths. Different workloads, different doctrines.
• Denormalization buys reads, costs writes. Read amplification (one row vs five joins) is the prize; write amplification, drift, storage, and integrity complexity are the bill.
• The disciplined version is "single source of truth + projection". Materialized views, computed columns, triggers, or async read-model rebuilds — pick a sync strategy explicitly. Drift without one is a bug factory.
• EF Core models 3NF naturally — owned types capture value-object 1NF, TPH/TPT handle inheritance. NoSQL (Cosmos, MongoDB) flips the script: the question becomes how much to denormalize, not whether.

Concepts (deep dive)

What "normal form" actually means

A normal form is a constraint on the dependencies between columns. You don't pick a normal form; you eliminate the dependencies that violate it. Each form is a stricter constraint than the previous one — 3NF implies 2NF implies 1NF.

The point of normalization isn't elegance. It's that redundancy creates anomalies — three places to update means three places to forget to update.

1NF — atomic columns, no repeating groups

Non-1NF                                       1NF
─────────────────────────────────────         ────────────────────────────
Customers                                     Customers           CustomerPhones
+----+--------+--------------------+          +----+--------+     +-------------+--------+
| Id | Name   | Phones             |          | Id | Name   |     | CustomerId  | Phone  |
+----+--------+--------------------+          +----+--------+     +-------------+--------+
|  1 | Alice  | "555-1, 555-2"     |   ──►    |  1 | Alice  |     |          1  | 555-1  |
|  2 | Bob    | "555-3"            |          |  2 | Bob    |     |          1  | 555-2  |
+----+--------+--------------------+          +----+--------+     |          2  | 555-3  |
                                                                   +-------------+--------+

The non-1NF version makes "find all customers with phone 555-2" a string-search problem. 1NF makes it an index seek.

In SQL, 1NF violations show up as comma-separated lists in VARCHAR columns, JSON arrays masquerading as primary data, or numbered columns (Phone1, Phone2, Phone3). All three are red flags.

💡 Value objects are 1NF-clean by construction. An Address(Street, City, Zip) modeled as an EF Core owned type lives in three columns of the parent table — atomic, queryable, indexable.

2NF — full dependency on the whole composite key

2NF only matters when you have a composite primary key. A non-key column must depend on the entire key, not just part of it.

Non-2NF: OrderItems
+---------+-----------+--------+----------+--------------+
| OrderId | ProductId | Qty    | Price    | ProductName  |   ← ProductName depends only on ProductId
+---------+-----------+--------+----------+--------------+
|     100 |       42  |    2   |   10.00  |  Widget      |
|     101 |       42  |    1   |   10.00  |  Widget      |   ← duplicated, can drift
|     101 |       77  |    3   |    5.00  |  Sprocket    |
+---------+-----------+--------+----------+--------------+

2NF: split it
OrderItems                                Products
+---------+-----------+--------+--------+ +-----------+-------------+--------+
| OrderId | ProductId | Qty    | Price  | | ProductId | ProductName | ...    |
+---------+-----------+--------+--------+ +-----------+-------------+--------+
|     100 |       42  |    2   | 10.00  | |       42  | Widget      |        |
|     101 |       42  |    1   | 10.00  | |       77  | Sprocket    |        |
|     101 |       77  |    3   |  5.00  | +-----------+-------------+--------+
+---------+-----------+--------+--------+

Price here is intentionally kept on the line item — it's the price at order time, not the product's current price. That's a deliberate denormalization for historical correctness, and it's the right call.

3NF — no transitive dependencies

The classic violation is "City and Country in the Orders table":

Non-3NF: Orders
+----+-----------+----------+----------+
| Id | ZipCode   | City     | Country  |   ← City and Country are determined by ZipCode,
+----+-----------+----------+----------+     not by the order itself.
|  1 |  10001    | New York | USA      |
|  2 |  10001    | New York | USA      |   ← duplicated
|  3 |  SW1A 1AA | London   | UK       |
+----+-----------+----------+----------+

3NF
Orders                          ZipCodes
+----+-----------+               +-----------+----------+----------+
| Id | ZipCode   |               | ZipCode   | City     | Country  |
+----+-----------+               +-----------+----------+----------+
|  1 |  10001    |               |  10001    | New York | USA      |
|  2 |  10001    |               |  SW1A 1AA | London   | UK       |
|  3 |  SW1A 1AA |               +-----------+----------+----------+
+----+-----------+

The transitive dependency: Order → ZipCode → City. Without 3NF, every order with the same zip duplicates the city/country, and a typo in one row creates a "version of New York" that diverges from the rest.

The senior phrasing: "every non-key column depends on the key, the whole key, and nothing but the key." (Bill Kent, 1983.)

BCNF — the stricter 3NF

3NF allows a non-trivial functional dependency A → B if B is a prime attribute (part of some candidate key). BCNF removes that loophole: every non-trivial dependency must have a superkey on the left-hand side.

The textbook example: a course-instructor table where (Student, Course) → Instructor and Instructor → Course (each instructor teaches one course). 3NF accepts it because Course is part of a candidate key. BCNF rejects it because Instructor → Course lacks a superkey on the left.

In real ERP / CRM data, BCNF violations that matter are rare. Most senior interviews stop at 3NF. Know that BCNF exists, know the formal statement, and you're covered.

4NF and 5NF — multi-valued and join dependencies

4NF outlaws multi-valued dependencies — when one key independently determines two unrelated multi-valued attributes (e.g., a person's hobbies and their pet types in one table). Fix: split into two tables.

5NF (PJNF — project-join normal form) outlaws non-trivial join dependencies — cases where a table can only be losslessly reconstructed by joining three or more projections.

In production .NET shops, you almost never hand-derive 4NF or 5NF. They appear naturally when you model with care. Mention them in interviews to demonstrate awareness, then move on.

The "until 3NF" senior rule

Why 3NF is the practical default:

• It eliminates the redundancy that causes update / insert / delete anomalies in transactional systems.
• It maps cleanly to ORMs — EF Core's reference / collection navigations correspond to FKs in 3NF.
• 4NF and 5NF refinements rarely change the table count or shape in practice.
• BCNF differences only matter for unusual dependency structures.

The seniors who go beyond 3NF are usually denormalizing down, not normalizing up.

Denormalization as a deliberate choice

Denormalize when normalized reads cost too much, and you have a sync strategy. Reasons that pass the bar:

1. Read amplification. A page load triggers seven joins on a hot path. A denormalized projection turns it into one table read. ✅ for read-heavy paths backed by a sync mechanism.
2. Reporting / analytics. OLAP workloads (sums, group-bys, time series) want star schemas — wide fact tables joined to small dimension tables. Normalization here actively hurts.
3. Caching layer in the database. Materialized views or "summary" tables. The DB owns the freshness contract via refresh rules.
4. Eventually-consistent CQRS read models. The write side is normalized; the read side is shaped for query. Cross-link: see CQRS Read-Model Rebuilding.
5. NoSQL / document stores. Cross-link: see NoSQL & Event Stores. Embedding child documents is normal; foreign-key joins are absent.

Trade-offs of denormalization

The disciplined pattern is single source of truth + projection: one table is canonical, others are derived deterministically from it (via materialized views, triggers, change-feed handlers, or rebuild jobs). Drift becomes "a projection is stale" — measurable and fixable — instead of "two rows disagree and we don't know which is right."

Materialized views vs computed columns vs triggers

Source table                 Denormalized representation
─────────────                ───────────────────────────
Orders + OrderItems   ──►    OrderSummary(Id, Total, ItemCount, LastItemAt)

Mechanisms:
  Computed column (persisted)   in-row, deterministic from same row
  Indexed view / matview        cross-table aggregate, refreshed on schedule or commit
  Trigger                       imperative; runs on every write
  CDC / change feed handler     async, durable, replayable
  CQRS read-model projector     async, app-owned, replayable

Mechanism by use case:

• In-row derivation (e.g., FullName from first + last) → computed column. SQL Server PERSISTED if you want to index it; Postgres GENERATED ALWAYS AS ... STORED.
• Cross-row aggregate (e.g., order total) → indexed view in SQL Server, materialized view in Postgres. SQL Server's indexed views auto-update on every write inside the same transaction (great consistency, write cost). Postgres matviews are explicit REFRESH MATERIALIZED VIEW [CONCURRENTLY].
• Cross-table denormalization with custom logic → trigger if you must keep it inside the DB; otherwise prefer CDC + projector (cleaner ops, replayable, observable).
• Cross-bounded-context projection → CQRS read model built off domain events.

Triggers are the most fragile — they look "automatic" but turn every write into a hidden multi-table transaction, and they hide where state actually lives. Use sparingly.

EF Core specifics

EF Core models 3NF schemas idiomatically. Two patterns worth knowing:

Owned types (value objects, 1NF for VOs):

public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; } = "";
    public Address ShippingAddress { get; set; } = default!;
    public Address BillingAddress { get; set; } = default!;
}

public record Address(string Street, string City, string Zip);

// Fluent config:
b.Entity<Customer>().OwnsOne(c => c.ShippingAddress, a =>
{
    a.Property(p => p.Street).HasColumnName("ShipStreet");
    a.Property(p => p.City).HasColumnName("ShipCity");
    a.Property(p => p.Zip).HasColumnName("ShipZip");
});
b.Entity<Customer>().OwnsOne(c => c.BillingAddress, a =>
{
    a.Property(p => p.Street).HasColumnName("BillStreet");
    /* ... */
});

The owned type lives in the parent's table — no join, atomic columns, fully 1NF. Cross-link: see EF Core Fundamentals.

Inheritance — TPH vs TPT:

TPH (Table-per-Hierarchy)              TPT (Table-per-Type)
─────────────────────────              ────────────────────
One wide table, Discriminator col      Base table + child tables, FK to base
Pros: fast (no joins)                  Pros: clean schema, true 3NF per type
Cons: lots of NULLs                    Cons: every read joins

TPH is intentionally non-3NF (NULLs everywhere; non-applicable columns per row) but is usually the right trade in OLTP. TPT keeps each table 3NF at the cost of joins. Pick TPH unless you have a strong reason.

NoSQL contrast — the question flips

In a relational world: "how do I split this into normal form?" In Cosmos DB or MongoDB: "how do I embed this so the read path is one document?"

Relational (3NF):                   Document (denormalized):
Order ──→ OrderItems ──→ Product    Order { items: [
                                       { productId, name, qty, priceAtOrder },
                                       ...
                                    ] }

The document model duplicates productName and priceAtOrder into the order — by design. Read latency is now a single point read. The trade is a projector or "product renamed" reconciler that updates historical orders if you want product names to track. Often you don't — the order should freeze the name at order time anyway. Cross-link: see NoSQL & Event Stores and Cosmos DB Modes — Deep.

Star schema for reporting

Fact (denormalized, wide, append-mostly)        Dimensions (small, normalized)
────────────────────────────────────────        ──────────────────────────────
FactSales                                       DimDate, DimCustomer, DimProduct, DimStore
  DateKey, CustomerKey, ProductKey, StoreKey,
  Quantity, UnitPrice, Discount, NetAmount

A fact row repeats foreign keys; dimension tables hold descriptive attributes (CustomerName, Region, ProductCategory). Queries do star joins (FactSales × DimDate × DimCustomer) which the optimizer handles brilliantly. Snowflake schema further normalizes the dimensions (e.g., DimProduct → DimCategory) — purer, slower, more ETL.

For OLAP the rule inverts: denormalize the fact, normalize-just-enough on dimensions.

How it works under the hood

The dependency view

A relation R has a set of functional dependencies (FDs). Each NF eliminates a class of FD that causes redundancy:

1NF:  no multi-valued attributes
2NF:  for composite key (A,B), no FD A → C (partial)
3NF:  no FD X → Y where X is non-prime (transitive)
BCNF: every non-trivial FD X → Y has X as a superkey
4NF:  no non-trivial multi-valued dependency unless it's a superkey-driven one
5NF:  no non-trivial join dependency unless it's superkey-driven

Each step removes a kind of redundancy and a corresponding update anomaly.

Anomalies normalization fixes

Update anomaly:  Customer Acme has 100 orders. Acme's address changes.
                 Non-3NF: 100 rows must update. Forget one → drift.
                 3NF:     1 row updates in Customers.

Insert anomaly:  Add a new product with no orders yet.
                 Non-2NF: can't insert without a fake order row.
                 2NF:     Products is its own table; insert is trivial.

Delete anomaly:  Last order for a customer is deleted.
                 Non-3NF: customer info disappears with it.
                 3NF:     Customers row remains; only the order is gone.

These are the "why" behind the rules. If your schema is immune to all three, you're at 3NF whether you knew it or not.

Materialized view refresh, conceptually

Base tables ──change──► (CDC log / trigger / event)
                  │
                  ▼
       Refresh strategy:
         - synchronous indexed view (SQL Server) — in-transaction
         - scheduled REFRESH MATERIALIZED VIEW (Postgres)
         - REFRESH MATERIALIZED VIEW CONCURRENTLY — readers don't block
         - app-level projector reading change feed — async, replayable

Each strategy has a freshness/cost trade-off. The senior question is always: what's the staleness budget for this read? Pick the strategy that fits.

Code: correct vs wrong

❌ Wrong: 1NF violation (CSV in a column)

CREATE TABLE Customers (
    Id    INT PRIMARY KEY,
    Name  NVARCHAR(100),
    Tags  NVARCHAR(MAX)        -- "vip,new,trial" — comma-separated
);
-- "find all VIPs" requires LIKE '%vip%' — no index can help

✅ Correct: 1NF with junction table

CREATE TABLE Tags (Id INT PRIMARY KEY, Name NVARCHAR(50) UNIQUE);
CREATE TABLE CustomerTags (CustomerId INT, TagId INT, PRIMARY KEY (CustomerId, TagId));
CREATE INDEX IX_CustomerTags_TagId ON CustomerTags(TagId);

❌ Wrong: 3NF violation (transitive)

CREATE TABLE Orders (
    Id          INT PRIMARY KEY,
    CustomerId  INT,
    City        NVARCHAR(100),
    Country     NVARCHAR(100)   -- redundant; depends on CustomerId
);

✅ Correct: 3NF

CREATE TABLE Customers (
    Id      INT PRIMARY KEY,
    City    NVARCHAR(100),
    Country NVARCHAR(100)
);
CREATE TABLE Orders (
    Id          INT PRIMARY KEY,
    CustomerId  INT REFERENCES Customers(Id)
);

✅ Correct: deliberate denormalization (price at order time)

CREATE TABLE OrderItems (
    OrderId        INT,
    ProductId      INT,
    Qty            INT,
    PriceAtOrder   DECIMAL(10,2),       -- snapshot, not Product.Price
    PRIMARY KEY (OrderId, ProductId)
);

This is not a 2NF violation — PriceAtOrder is a fact about the line item at order time, not about the product. Naming makes intent clear.

✅ Correct: star schema for reporting

CREATE TABLE FactSales (
    SaleId       BIGINT PRIMARY KEY,
    DateKey      INT,
    CustomerKey  INT,
    ProductKey   INT,
    StoreKey     INT,
    Quantity     INT,
    NetAmount    DECIMAL(12,2)
);
CREATE TABLE DimDate     (DateKey INT PRIMARY KEY, Date DATE, Year INT, Quarter INT, Month INT);
CREATE TABLE DimCustomer (CustomerKey INT PRIMARY KEY, CustomerId INT, Name NVARCHAR(200), Region NVARCHAR(50));
CREATE TABLE DimProduct  (ProductKey INT PRIMARY KEY, ProductId INT, Name NVARCHAR(200), Category NVARCHAR(100));

✅ Correct: EF Core fluent for both shapes

// 3NF transactional
b.Entity<Order>(o =>
{
    o.HasKey(x => x.Id);
    o.HasOne(x => x.Customer).WithMany(c => c.Orders).HasForeignKey(x => x.CustomerId);
    o.OwnsMany(x => x.Items, i =>
    {
        i.WithOwner().HasForeignKey("OrderId");
        i.Property<int>("Id");
        i.HasKey("OrderId", "Id");
    });
});

// Denormalized read model (CQRS-style)
b.Entity<OrderSummaryRead>(s =>
{
    s.ToTable("OrderSummaryRead");
    s.HasKey(x => x.OrderId);
    s.Property(x => x.CustomerName);  // duplicated from Customer
    s.Property(x => x.ItemCount);
    s.Property(x => x.Total);
});

The summary is a projection, not a normalized table — projector keeps it in sync.

Design patterns for this topic

Pattern 1 — "3NF for OLTP, denormalize for OLAP"

• Intent: transactional integrity on writes; reporting speed on reads.

Pattern 2 — "Single source of truth + projection"

• Intent: one canonical table; derived shapes built deterministically.

Pattern 3 — "Owned types as value objects"

• Intent: capture cohesive value objects (Address, Money) in 1NF without table sprawl.

Pattern 4 — "Snapshot at event time"

• Intent: intentionally store PriceAtOrder, CustomerNameAtOrder — historical correctness, not redundancy.

Pattern 5 — "Materialized view as read cache"

• Intent: keep writes 3NF; let the DB maintain a denormalized projection.

Pattern 6 — "CQRS read model"

• Intent: separate write model (3NF) from read model (denormalized per query shape); rebuild from events.

Pros & cons / trade-offs

When to use / when to avoid

• ✅ Use 3NF as the default for transactional OLTP schemas.
• ✅ Use star schema for reporting and analytics workloads.
• ✅ Use materialized views or indexed views when a derived shape stays close to the source.
• ✅ Use CQRS read models for cross-bounded-context projections.
• ✅ Snapshot historical values (price, name, address at event time) intentionally — and document why.
• ❌ Avoid denormalizing without a sync strategy.
• ❌ Avoid triggers for cross-table denormalization unless you've tried CDC first.
• ❌ Avoid 1NF violations (CSV columns, numbered columns) — they always end in pain.
• ❌ Avoid over-normalizing tiny lookup tables (e.g., Status enum) when the values are stable and few.
• ❌ Avoid chasing 4NF / 5NF unless you have a concrete dependency violation.

Interview Q&A

Q1. Define 3NF without using textbook language. Every non-key column tells you something about the row's primary key directly — not via another non-key column.

Q2. Show me a transitive dependency in real ERP data. Order.ShipZip → Order.ShipCity → Order.ShipCountry. The country isn't a fact about the order; it's a fact about the zip code. Move it to a ZipCodes table or denormalize intentionally with a sync mechanism.

Q3. When would you intentionally violate 3NF? Reporting fact tables; CQRS read models; document stores; snapshot-at-event-time fields; hot-path read amplification with a materialized view.

Q4. BCNF vs 3NF — what's the difference? 3NF allows X → Y if Y is part of a candidate key (a "prime attribute"); BCNF requires X itself to be a superkey. BCNF rejects a few cases 3NF allows. In practice, BCNF differences appear in unusual dependency graphs and are rare in production.

Q5. Materialized views vs application-level read models — pros/cons? Matviews live in the DB, get transactional or refresh-based freshness, and can't span databases or services. App read models are flexible, replayable, observable, and cross-service — at the cost of more code and async lag. Pick matview for "stay close to the source"; pick app projector for cross-context or event-driven cases.

Q6. How does denormalization interact with concurrency? Every duplicated value is a potential drift point. If two transactions update the source and the projection separately, you've created lost-update or write-skew opportunities. Solutions: same-transaction updates (indexed view, trigger), serial single-writer projector (change feed), or accept eventual consistency and verify with reconciliation jobs.

Q7. Star schema vs snowflake — what's the trade? Star: dimensions denormalized; fewer joins; faster queries; more storage; easier for analysts. Snowflake: dimensions normalized; cleaner; less storage; more joins. Most modern OLAP stacks pick star.

Q8. When does NoSQL's flexibility actively hurt you? When access patterns evolve beyond the embedded shape and you need cross-document joins, ad-hoc reporting, or strong relational integrity. The "design for queries" rule means re-modeling cost grows with every new query shape.

Q9. Owned types in EF Core — what NF property do they preserve? Owned types embed value objects in the parent table — atomic columns, 1NF preserved, with the value-object boundary kept in the C# model.

Q10. TPH vs TPT — which is more "normalized"? TPT is closer to true 3NF per type; TPH deliberately accepts NULLs to win on read performance. Most OLTP picks TPH.

Q11. Lookup table (e.g., Status) — normalize or inline? Stable, small, finite enum-like values: an INT or VARCHAR column with a CHECK constraint or DB enum is fine. Reserve Statuses table for cases where the list grows or carries metadata.

Q12. The "single source of truth + projection" pattern — what does it mean? One table is canonical for a fact; every other place that fact appears is a projection derived from it deterministically. Drift is "the projection is stale", which is measurable and fixable; without this discipline, drift becomes "two values disagree and we don't know which is right".

Gotchas / common mistakes

• ⚠️ Denormalization without a sync strategy — drift accumulates silently.
• ⚠️ Over-normalizing tiny lookup tables — adds joins, no benefit.
• ⚠️ CSV / JSON in a column as a substitute for a child table — kills indexes, kills queries.
• ⚠️ Snapshot fields confused with redundancy — PriceAtOrder is not a normalization violation; document it.
• ⚠️ Triggers as the default sync mechanism — debug nightmares; prefer CDC / projectors.
• ⚠️ Pursuing BCNF / 4NF / 5NF without a concrete violation — wasted effort in nearly every OLTP shop.
• ⚠️ Denormalizing the write model — usually means you should be using CQRS instead.
• ⚠️ Forgetting NoSQL flips the question — in document stores you start denormalized and decide how much further to go.
• ⚠️ No UNIQUE constraint on natural keys — duplicates sneak in even at 3NF.
• ⚠️ Reporting queries hitting OLTP tables — denormalize into a star schema or a read replica; don't slow the transactional system to death.

Further reading

• Codd: A Relational Model of Data for Large Shared Data Banks (1970)
• Bill Kent: A Simple Guide to Five Normal Forms (1983)
• Kimball: The Data Warehouse Toolkit — star schema canon
• MS Learn: Indexed Views (SQL Server)
• Postgres: Materialized Views
• EF Core: Owned Entity Types
• EF Core: Inheritance Mapping