Data Modeling: Normalization, Star Schemas & ER Diagrams
The schema you ship on day 1 is the schema you live with on day 1,000. Get the model right and queries write themselves; get it wrong and every feature is a migration nightmare. Postgres for the app, star schema in Snowflake for analytics — that's the modern data stack, and this lesson teaches both.
Learning Objectives
After this lesson, you will be able to:
Explain the purpose of normalization and identify 1NF, 2NF, and 3NF violations
Transform an unnormalized table into third normal form step by step
Distinguish between normalized (OLTP) and denormalized (OLAP) designs and when to use each
Design a star schema with fact and dimension tables for analytics
Read and create Entity-Relationship diagrams with correct cardinality notation
Make informed trade-offs between normalization and denormalization for real-world systems
Understand slowly-changing dimensions (SCD Type 1 vs Type 2) used in data warehouses
Your Reflection
Saves automatically
What’s one thing you learned? What’s still confusing?
Before writing a single CREATE TABLE, you need to design your data model. A data model is the blueprint for how your data is organized, related, and constrained. Get it right, and your application is fast, consistent, and easy to evolve. Get it wrong, and you will spend years fighting data quality issues.
1NF Rule: Every column contains atomic (indivisible) values, and each row is unique.
A table violates 1NF when it stores multiple values in a single column:
sql
-- VIOLATES 1NF: multiple phone numbers in one column
CREATE TABLE contacts_bad (
id INTEGER PRIMARY KEY,
name TEXT,
phones TEXT -- '555-1234, 555-5678, 555-9012'
);
-- 1NF FIX: separate table for phones
CREATE TABLE contacts (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE contact_phones (
id INTEGER PRIMARY KEY,
contact_id INTEGER REFERENCES contacts(id),
phone TEXT NOT NULL
);
Try it! Think about a spreadsheet you have used. Did any column contain comma-separated lists? That is a 1NF violation. How would you split it into separate rows?
2NF Rule: 1NF + every non-key column depends on the entire primary key, not just part of it.
2NF violations only happen with composite keys (primary keys made of multiple columns). If a non-key column depends on only part of the key, it belongs in a separate table.
sql
-- VIOLATES 2NF: course_name depends only on course_code, not on (student_id, course_code)
-- The composite key is (student_id, course_code)
-- But course_name depends only on course_code (partial dependency)
-- 2NF FIX: split into two tables
CREATE TABLE courses (
course_code TEXT PRIMARY KEY,
course_name TEXT NOT NULL
);
CREATE TABLE enrollments (
student_id INTEGER,
course_code TEXT REFERENCES courses(course_code),
grade TEXT,
semester TEXT,
PRIMARY KEY (student_id, course_code, semester)
);
What Do You Think?
In the original student_enrollments_raw table, 'instructor_email' depends on 'instructor', which depends on 'course_code'. If we fix the 2NF violation by moving course_name to a courses table, does instructor_email still have a problem?
Yes. Even after achieving 2NF, instructor_email depends on instructor, which depends on course_code. This chain of dependencies (A depends on B depends on C, where C is the key) is called a transitive dependency -- and it is exactly what Third Normal Form eliminates.
3NF Rule: 2NF + no transitive dependencies. Every non-key column depends directly on the primary key, nothing else.
The classic mnemonic: "Every non-key attribute must provide a fact about the key, the whole key, and nothing but the key -- so help me Codd." (Named after Edgar F. Codd, the inventor of relational databases.)
Step through the 1NF to 2NF to 3NF transformation interactively:
Loading visualization...
sql
-- FULL 3NF DECOMPOSITION of student_enrollments_raw
-- Table 1: Students (student facts only)
CREATE TABLE students (
student_id INTEGER PRIMARY KEY,
student_name TEXT NOT NULL,
student_email TEXT UNIQUE NOT NULL
);
-- Table 2: Instructors (instructor facts only)
CREATE TABLE instructors (
instructor_id INTEGER PRIMARY KEY,
instructor_name TEXT NOT NULL,
instructor_email TEXT UNIQUE NOT NULL
);
-- Table 3: Courses (course facts + who teaches it)
CREATE TABLE courses (
course_code TEXT PRIMARY KEY,
course_name TEXT NOT NULL,
instructor_id INTEGER REFERENCES instructors(instructor_id)
);
-- Table 4: Enrollments (the relationship between students and courses)
CREATE TABLE enrollments (
enrollment_id INTEGER PRIMARY KEY,
student_id INTEGER REFERENCES students(student_id),
course_code TEXT REFERENCES courses(course_code),
grade TEXT,
semester TEXT,
UNIQUE(student_id, course_code, semester)
);
Normalization is not always the answer. When you need fast reads on large datasets -- dashboards, reports, analytics -- joining 8 tables for every query is too slow. This is where denormalization comes in: intentionally adding redundancy to speed up reads.
Common denormalization strategies:
Strategy
Example
Trade-off
Pre-joined tables
Store customer_name in orders table
Faster reads, update anomaly risk
Computed columns
Store order_total instead of computing from line items
Faster reads, must keep in sync
Summary tables
daily_sales_summary with pre-aggregated totals
Instant dashboards, stale data risk
Materialized views
Database maintains a pre-computed query result
Best of both worlds, refresh overhead
sql
-- Denormalized summary table for a dashboard
CREATE TABLE daily_sales_summary (
date DATE,
product_id INTEGER,
product_name TEXT, -- denormalized from products table
category TEXT, -- denormalized from categories table
total_units INTEGER,
total_revenue REAL,
avg_price REAL,
PRIMARY KEY (date, product_id)
);
-- Refresh this table nightly from normalized source tables
INSERT INTO daily_sales_summary
SELECT
DATE(o.order_date) AS date,
p.product_id,
p.product_name,
c.category_name,
SUM(oi.quantity),
SUM(oi.quantity * oi.unit_price),
AVG(oi.unit_price)
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
JOIN categories c ON p.category_id = c.category_id
GROUP BY DATE(o.order_date), p.product_id, p.product_name, c.category_name;
Try it! Think about a dashboard you use (Google Analytics, Shopify, GitHub Insights). What pre-computed summaries do you think exist behind the scenes?
The star schema is the most popular denormalized design for analytics. It has two components:
Fact table (center of the star): Contains measurable events -- sales, clicks, logins, transactions. Each row is one event. Contains numeric measures and foreign keys to dimensions.
Dimension tables (points of the star): Contain descriptive attributes -- who, what, where, when. Each row describes one entity.
sql
-- Create the star schema
CREATE TABLE dim_date (
date_key INTEGER PRIMARY KEY,
full_date DATE NOT NULL,
day_of_week TEXT,
month INTEGER,
quarter INTEGER,
year INTEGER,
is_holiday BOOLEAN DEFAULT FALSE
);
CREATE TABLE dim_product (
product_key INTEGER PRIMARY KEY,
product_name TEXT NOT NULL,
category TEXT,
brand TEXT,
unit_price REAL
);
CREATE TABLE dim_customer (
customer_key INTEGER PRIMARY KEY,
customer_name TEXT NOT NULL,
email TEXT,
city TEXT,
state TEXT,
segment TEXT -- 'consumer', 'corporate', 'enterprise'
);
CREATE TABLE dim_store (
store_key INTEGER PRIMARY KEY,
store_name TEXT NOT NULL,
city TEXT,
state TEXT,
region TEXT
);
CREATE TABLE fact_sales (
sale_id INTEGER PRIMARY KEY,
date_key INTEGER REFERENCES dim_date(date_key),
product_key INTEGER REFERENCES dim_product(product_key),
customer_key INTEGER REFERENCES dim_customer(customer_key),
store_key INTEGER REFERENCES dim_store(store_key),
quantity INTEGER NOT NULL,
revenue REAL NOT NULL,
discount REAL DEFAULT 0
);
A retail company has 10 million sales transactions per day. They need a dashboard showing revenue by product category, store region, and month. Should they query their normalized OLTP database directly, or build a star schema?
A star schema is the clear winner here. With 10 million rows per day, joining 8+ normalized tables for every dashboard query would take minutes. The star schema's pre-joined dimension tables and simple structure let BI tools generate queries that run in seconds. This is exactly why companies build data warehouses separate from their operational databases.
ER diagrams are the visual language of database design. They show entities (tables), attributes (columns), and relationships (foreign keys) at a glance.
Key notation:
Symbol
Meaning
Example
1 --- 1
One-to-one
User has one profile
1 --- *
One-to-many
Customer has many orders
* --- *
Many-to-many
Students enroll in many courses; courses have many students
sql
-- Many-to-many requires a junction table
-- Students <-> Courses is many-to-many
CREATE TABLE students (
student_id INTEGER PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE courses (
course_id INTEGER PRIMARY KEY,
title TEXT NOT NULL
);
-- Junction table resolves the many-to-many
CREATE TABLE student_courses (
student_id INTEGER REFERENCES students(student_id),
course_id INTEGER REFERENCES courses(course_id),
enrolled_at DATE DEFAULT CURRENT_DATE,
grade TEXT,
PRIMARY KEY (student_id, course_id)
);
You are designing the database for a music streaming app like Spotify. Think about these entities: Users, Artists, Albums, Songs, Playlists, Listening History.
sql
-- Normalized schema for a music streaming service
CREATE TABLE users (
user_id INTEGER PRIMARY KEY,
username TEXT UNIQUE NOT NULL,
email TEXT UNIQUE NOT NULL,
plan TEXT DEFAULT 'free' -- 'free', 'premium'
);
CREATE TABLE artists (
artist_id INTEGER PRIMARY KEY,
artist_name TEXT NOT NULL,
genre TEXT,
country TEXT
);
CREATE TABLE albums (
album_id INTEGER PRIMARY KEY,
album_title TEXT NOT NULL,
artist_id INTEGER REFERENCES artists(artist_id),
release_year INTEGER
);
CREATE TABLE songs (
song_id INTEGER PRIMARY KEY,
song_title TEXT NOT NULL,
album_id INTEGER REFERENCES albums(album_id),
duration_ms INTEGER,
track_number INTEGER
);
-- Many-to-many: users create playlists, playlists contain songs
CREATE TABLE playlists (
playlist_id INTEGER PRIMARY KEY,
user_id INTEGER REFERENCES users(user_id),
name TEXT NOT NULL,
is_public BOOLEAN DEFAULT TRUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE playlist_songs (
playlist_id INTEGER REFERENCES playlists(playlist_id),
song_id INTEGER REFERENCES songs(song_id),
position INTEGER,
added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (playlist_id, song_id)
);
-- Fact table: listening history (event-driven, perfect for analytics)
CREATE TABLE listening_history (
listen_id INTEGER PRIMARY KEY,
user_id INTEGER REFERENCES users(user_id),
song_id INTEGER REFERENCES songs(song_id),
listened_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
duration_ms INTEGER, -- how long they actually listened
skipped BOOLEAN DEFAULT FALSE
);
Try it! Look at this schema and identify: Which tables are "dimension-like"? Which is the "fact table"? What analytics queries could you run against listening_history?
Try SCD Type 2 yourself. The playground below ships a dim_customer history table with one customer (Alice) who moved cities twice. Notice how every fact-table query can pick the right row by joining on customer_id AND a date range — that's the whole reason Type 2 exists.
A table has columns: order_id, customer_name, customer_address, product_name, product_price, quantity. The primary key is (order_id, product_name). customer_name depends only on order_id (partial dependency). What normal form violation is this?
What's next: Advanced SQL. CASE WHEN for conditional logic, string and date functions for messy real data, and set operations (UNION/INTERSECT/EXCEPT) to combine query results. These are the power tools senior engineers reach for daily.