ER / 3NF modeling focuses on capturing business entities and relationships without redundancy. It is the default for OLTP apps: customers, orders, products as separate normalized tables.
Dimensional modeling focuses on making analytical questions easy and fast: facts at a grain, dimensions for slicing. Some controlled redundancy (denormalized dims) is intentional.
ER (app): customers -< orders -< order_lines >- products many joins for "revenue by category last month" Dimensional (warehouse): fct_order_items surrounded by dim_date, dim_customer, dim_product BI-friendly aggregates
| | ER / 3NF | Dimensional | |---|---|---| | Goal | Write integrity, app screens | Analytics & BI | | Redundancy | Avoided | Often accepted in dims/marts | | Central idea | Entities & relationships | Facts, dimensions, grain | | Typical home | OLTP DB | Warehouse / lakehouse gold |
Interview tip
> "ER for operational systems; dimensional for analytics. Same business, different optimization target."