Database Schema Design
debt(d5/e7/b9/t7)
Closest to 'specialist tool catches' (d5). Schema design issues like missing primary keys, no FK constraints, and VARCHAR(255) everywhere can be detected by tools like mysql-workbench, pgadmin, and schemacrawler as listed in detection_hints. However, semantic issues (wrong cardinality, inappropriate data types for the domain) require manual review, so this sits at the specialist-tool level.
Closest to 'cross-cutting refactor' (e7). The misconception explicitly states that fixing bad schema decisions 'requires painful multi-step migrations on live tables with millions of rows.' While quick_fix suggests designing properly upfront, remediation after go-live involves downtime risk, data migration scripts, and potentially touching every query that touches the affected tables. This is worse than a single-component refactor but not quite full architectural rework.
Closest to 'defines the system's shape' (b9). Database schema is the foundational architectural choice that every other layer depends on. Wrong FK cardinality, missing constraints, or inappropriate data types become load-bearing across the entire system. applies_to shows this affects all contexts (web, cli), and the why_it_matters confirms that schema decisions pervade everything. You either live with bad schema decisions or face a major rework.
Closest to 'serious trap' (t7). The misconception directly states the trap: developers believe 'schema design can be fixed later with migrations' when in reality some decisions are extremely costly to change post-deployment. This contradicts how migrations work in simpler cases (where they're indeed easy), leading competent developers to underestimate the permanence of early schema choices. The common_mistakes list reinforces multiple non-obvious pitfalls.
Also Known As
TL;DR
Explanation
Good schema design balances normalisation, query patterns, and future flexibility. Key decisions: appropriate data types (INT not VARCHAR for IDs), NOT NULL constraints by default with explicit NULL allowances, indexes aligned with access patterns, and soft-delete patterns vs hard deletes. Schema evolution is managed through migrations. Naming conventions (snake_case, plural table names) should be consistent. The biggest cost of bad schema design is not the initial pain but the migration tax paid on every subsequent change.
Diagram
flowchart TD
subgraph Normalisation_Goals
DUP[Eliminate duplicate data]
ANO[Prevent update anomalies]
INT[Enforce referential integrity]
end
subgraph Normal_Forms
UNF[Unnormalised: repeated groups]
NF1[1NF: atomic values no repeats]
NF2[2NF: no partial dependencies]
NF3[3NF: no transitive dependencies]
UNF --> NF1 --> NF2 --> NF3
end
subgraph Denormalisation
PERF2[When reads dominate<br/>add redundant data for speed]
MAT[Materialised views<br/>pre-computed aggregates]
end
style NF3 fill:#238636,color:#fff
style UNF fill:#f85149,color:#fff
style PERF2 fill:#d29922,color:#fff
Common Misconception
Why It Matters
Common Mistakes
- Using VARCHAR for all columns including numeric IDs — INT is smaller, faster to index, and prevents invalid values.
- No NOT NULL constraints by default — nullable columns make every query more complex and null propagation causes bugs.
- Singular vs plural table names inconsistently — pick one convention and enforce it everywhere.
- Using DATETIME instead of TIMESTAMPTZ in PostgreSQL — timezone-naive timestamps cause subtle bugs for international users.
Code Examples
-- Poor schema design:
CREATE TABLE user (
id VARCHAR(255), -- Should be INT or UUID
name VARCHAR(255), -- Nullable by default — is name optional?
email VARCHAR(255), -- No UNIQUE constraint
age VARCHAR(255), -- Age as string?
created VARCHAR(255) -- Timestamp as string?
);-- Well-designed schema:
CREATE TABLE users ( -- Plural, snake_case
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name VARCHAR(200) NOT NULL,
email VARCHAR(254) NOT NULL UNIQUE,
age SMALLINT CHECK (age >= 0 AND age < 150),
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);