Modeling
Normalize or denormalize?
Store each fact once where data is written; copy it where data is mostly read.
Normalized tables (3NF) avoid update, insertion and deletion anomalies: the app's tables. Wide, denormalized tables are fine for analytics when rebuilt from the normalized source. Views and materialized views are a middle way.
-- A flat export repeats the customer on every order: the customer is an entity of its own. WITH flat_orders(order_id, customer_email, customer_city) AS (VALUES (1, 'lea@example.com', 'Lyon'), (2, 'lea@example.com', 'Lyon'), (3, 'tom@example.com', 'Paris'), (4, 'lea@example.com', 'Lyon')) SELECT DISTINCT customer_email, customer_city FROM flat_orders ORDER BY customer_email;
| customer_email | customer_city |
|---|---|
| lea@example.com | Lyon |
| tom@example.com | Paris |