OLTP vs OLAP schemas, star schema, temporal tables, soft deletes.

Schema Design — Normalisation, Denormalisation Trade-offs

Schema design choices propagate through the entire application lifetime. OLTP schemas favour normalisation (3NF) to minimise write anomalies and storage. OLAP/data warehouse schemas denormalise into star or snowflake schemas to minimise joins at query time. Temporal tables track historical changes with system-period versioning. Soft deletes (deleted_at timestamp) preserve audit history. Surrogate keys (UUID/serial) decouple application from natural key changes.

Interactive Pipeline Flow

UnderstandImplementOptimise

📦 Entities — identify nouns

Identify entities (nouns in requirements): User, Order, Product, Payment. Each entity becomes a table. Each attribute becomes a column.

Architecture Diagrams

System-period temporal tables for audit history.

Loading diagram...

Key Design Decisions

🔑Use UUIDs for public-facing IDs

Sequential integers expose total count and enable enumeration attacks. UUID v4 (random) or UUIDv7 (time-ordered, better for index locality) are standard for API-exposed identifiers.

📋Soft deletes require careful index design

WHERE deleted_at IS NULL filters must use partial indexes: CREATE INDEX ON users(email) WHERE deleted_at IS NULL. A full index including deleted rows wastes space and slows queries.

🚀Never edit applied migrations

Once a migration runs in any environment, it is immutable. Edit = checksums fail in Flyway, or drift between environments. Always add new migrations instead of modifying existing ones.

Discussion