3NF vs Star Schema
Normalized for writing data correctly, or dimensional for reading it easily?
Third normal form (3NF) removes redundancy so each fact is stored once, which protects data integrity in systems that write often, such as application databases. A star schema deliberately repeats descriptive attributes in dimension tables so analytical queries need fewer joins and read like business questions. Use 3NF for operational systems and integration layers; use a star schema for the tables analysts and BI tools query.
On this page · 6 sections
- Option A
- Third normal form (3NF)
- Option B
- Star schema
- Compared on
- 6 dimensions
- Goes deeper in
- Data Modeling
In 3NF, every non-key column depends on the key, the whole key, and nothing but the key. A product's category lives in a categories table, not on every product row, and an order references a customer rather than copying the customer's address. Updates touch one row, and inconsistent copies cannot happen.
A star schema relaxes that on purpose. The product dimension carries category and department directly, so a query for revenue by department joins the fact table to one dimension instead of walking three normalized tables. Redundancy is accepted because dimensions are small compared with facts and change slowly.
| Aspect | Third normal form (3NF) | Star schema |
|---|---|---|
| Optimized for | Writes and integrity (OLTP) | Reads and analysis (OLAP) |
| Redundancy | Minimized | Accepted in dimensions |
| Joins per question | Many | Few: fact to each dimension |
| Typical home | Application databases, enterprise integration layer | Data marts and the warehouse presentation layer |
| Readability for analysts | Low; requires knowing the full model | High; mirrors business questions |
| Methodology | Codd's normal forms; Inmon's enterprise warehouse | Kimball dimensional modeling |
Use 3NF when
- The system records transactions and must keep every value consistent.
- You are building an integration layer that combines many sources before modeling.
- Storage of duplicated attributes or update anomalies would be a real problem.
Use a star schema when
- Analysts, dashboards, or Power BI will query the tables directly.
- You want measures that aggregate correctly across consistent dimensions.
- Query simplicity and speed matter more than avoiding duplicated attributes.
This is rarely either-or. Source systems are usually close to 3NF; the warehouse stages that data and remodels it into stars for analysis. The skill is knowing where each belongs, and recognizing when a normalized source needs to become a dimension.
A good exercise: take the normalized ecommerce source tables (customers, products, orders, order items, payments) and design the star schema you would build from them.
- Is a star schema normalized?
- Partly. Fact tables are normalized in the sense that they hold keys and measures, but dimension tables are intentionally denormalized, usually to second normal form or lower. A snowflake schema normalizes the dimensions further.
- Should a data warehouse use 3NF?
- Some layers can. Inmon-style warehouses keep an integrated 3NF core and publish dimensional marts from it. Kimball-style warehouses model dimensionally throughout. Either way, the layer analysts query is usually dimensional.
Go deeper in the Data Modeling hub.