Database Design Principles
Demo Creator
@seed-creator · muallif
27/07/20261 DAQIQA O'QISHYANGILANGAN448 ko'rish · 188 o'qish
Start with the Access Patterns
The best schema is one optimized for the queries your application actually runs. Read your query plans before adding indexes, not after.
A schema that looks beautiful in ER diagrams but runs slow under load is not a good schema.
Normalization to Third Normal Form
Normalize to 3NF by default to eliminate update anomalies. Denormalize deliberately for read-heavy workloads after profiling.
Avoid storing derived data (totals, averages) in main tables. Compute them in queries or maintain them in separate materialized views.
Soft Deletes
Soft deletes — setting a deleted_at timestamp instead of removing rows — preserve audit history and simplify recovery. Add a partial unique index that excludes soft-deleted rows to keep natural uniqueness constraints working.
schema.sql · sql
CREATE UNIQUE INDEX users_email_active_unique ON users (email) WHERE deleted_at IS NULL; -- Soft delete:UPDATE users SET deleted_at = NOW() WHERE id = $1; -- Resurrect:UPDATE users SET deleted_at = NULL WHERE id = $1;
The partial index only covers live rows, so a deleted email can be reused.