Schema design

Data types — vì sao TIMESTAMP có timezone cứu deploy multi-region

Kiểu chuẩn SQL: INTEGER/BIGINT, VARCHAR, DECIMAL/NUMERIC, DATE/TIMESTAMP, BOOLEAN. 4 type pitfall production. Dialect: auto-increment, timezone, JSON, array.

Constraints — DB là last line of defense, không trust app validation

NOT NULL, UNIQUE, PK, FK, CHECK chuẩn SQL. Deferred constraint, cascading. Vì sao app-level validate race-prone còn DB-level enforce safe — nguyên lý agnostic.

Normalization 1NF→3NF — refactor TaskFlow tags thành junction table

1NF atomic value, 2NF no partial dependency, 3NF no transitive dependency. Codd 1970-1972. Pitfall over-normalize. TaskFlow refactor demo step-by-step — agnostic.

Denormalization tradeoff — khi nào break 3NF có chủ đích

Read vs write amplification. 3 pattern: cached column, counter table, materialized view. Sync bằng trigger/app/CDC. Khi nào nên break 3NF — nguyên lý agnostic.

Surrogate vs natural key — UUIDv7 thay UUIDv4 cứu page split

Auto-increment vs UUID v4 vs UUIDv7 vs natural key. Page split cost trên index có thứ tự, enumeration attack risk, distributed tradeoff. Snowflake — nguyên lý agnostic.

Schema migration & evolution — versioned migration & expand-contract

Versioned migration forward-only, vì sao cần (team/CI/CD/reproducible), rollback vs roll-forward, expand-contract & online DDL zero-downtime. Tool landscape — agnostic.