Lesson 4.1: Normalization vs Denormalization
Introduction
Schema design is where most AI systems fail. A schema that works great for 10k rows collapses at 10M rows. A schema optimized for writes becomes unqueryable. A schema designed for one use case breaks when ML engineers need ad-hoc analytics.
This lesson teaches you how to balance normalization and denormalization for real-world AI workloads.
The Normalization Spectrum
Highly Normalized (3NF):
-- Separate tables, no redundancy
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
email TEXT UNIQUE NOT NULL
);
CREATE TABLE user_profiles (
user_id BIGINT PRIMARY KEY REFERENCES users(id),
name TEXT,
bio TEXT
);
CREATE TABLE user_preferences (
user_id BIGINT PRIMARY KEY REFERENCES users(id),
theme TEXT,
language TEXT
);
Pros:
- No data redundancy
- Easy to update (single source of truth)
- Smaller total storage
Cons:
- Requires joins for common queries
- Slower reads (3 table JOIN)
- Complex queries
Denormalized:
-- Everything in one table
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
email TEXT UNIQUE NOT NULL,
name TEXT,
bio TEXT,
theme TEXT,
language TEXT,
last_login TIMESTAMPTZ,
order_count INT,
total_spent NUMERIC
);
Pros:
- Fast reads (no joins)
- Simple queries
- Single table scans
Cons:
- Data redundancy
- Update anomalies (total_spent must be recalculated)
- Larger storage
The AI Era Sweet Spot
Hybrid approach:
-- Core entities: Normalized
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
email TEXT UNIQUE NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- Hot data: Denormalized for speed
CREATE TABLE user_features (
user_id BIGINT PRIMARY KEY REFERENCES users(id),
-- Computed features (denormalized)
order_count_7d INT,
order_count_30d INT,
avg_order_value NUMERIC,
last_order_date DATE,
-- Refreshed by background job
computed_at TIMESTAMPTZ DEFAULT NOW()
);
-- Historical data: Normalized
CREATE TABLE orders (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL REFERENCES users(id),
total NUMERIC NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW()
);
Strategy:
- Transactional data: Normalized (users, orders, products)
- ML features: Denormalized (pre-computed for speed)
- Logs/events: Semi-normalized (append-only)
Key Takeaways
- Full normalization (3NF) minimizes redundancy but requires joins
- Full denormalization speeds reads but causes update anomalies
- AI systems benefit from hybrid approach: normalize transactions, denormalize features
- Pre-compute ML features in denormalized tables for fast inference
- Choose normalization level based on read/write patterns
- Transactional data stays normalized, analytical data gets denormalized
التالي · الدرس 23 من 45
How AI Pipelines Break Naive Schemas
قراءة 2 دقيقة
أنهيت 22 من 45 درسًا

اسأل Byte عن هذا الدرس
محادثة خاصة حول هذا الدرس. قرأ Byte الدرس كاملًا، ويجيب خلال ثوانٍ، ويُظهر القسم الذي تأتي منه كل إجابة. يمكنك السؤال بأي لغة.
الأسئلة والأجوبة
أسئلة عامة حول هذا الدرس. يجيب AI Tutor فورًا بلغتك، ويراجع فريق FreeAcademy الإجابات، ويمكن لكل المتعلمين قراءتها.

