What Is Database Design and Which Schema Decisions Matter Most?
Database design is the process of turning application requirements into a concrete data model: tables (or collections), keys, relationships, constraints, and indexes that support the queries your system actually runs. The decisions that matter most are normalization vs. denormalization, primary key choice, index strategy, and SQL vs. NoSQL modeling. Get these right and the database stays fast as data grows; get them wrong and you face missing-index full scans, hot partitions, and unbounded growth that no amount of application code can fix.
The core decisions, in order of impact
1. Normalize first, denormalize deliberately
Normalization removes duplication so a single fact lives in one place. Third normal form (3NF) is the usual default for transactional (OLTP) workloads: each table holds one entity, and foreign keys link them.
Denormalization duplicates data on purpose to avoid joins on hot read paths. It's a trade-off, not an upgrade:
| Approach | Wins | Costs |
|---|---|---|
| Normalized (3NF) | Consistent writes, no update anomalies, smaller storage | More joins, slower complex reads |
| Denormalized | Fast reads, fewer joins | Update anomalies, must keep copies in sync |
Rule of thumb: normalize until a measured read path hurts, then denormalize that specific path — not the whole schema.
2. Choose primary keys that match access patterns
- Surrogate keys (auto-increment integer, UUID) are stable and decoupled from business data. Auto-increment is compact and index-friendly but leaks row counts and can bottleneck on a single sequence. UUIDs distribute writes but are larger and, if random, fragment indexes — prefer time-ordered UUIDs (UUIDv7-style) when you need distribution.
- Natural keys (email, ISBN) carry meaning but change, which ripples through foreign keys. Use them as unique constraints, not primary keys, unless truly immutable.
3. Index for the queries you run, not the columns you have
An index speeds reads and slows writes. Add one when a query filters, sorts, or joins on a column often enough to justify the write cost.
- Composite indexes are ordered: an index on
(user_id, created_at)serves queries filtering byuser_idand sorting bycreated_at, but not queries filtering bycreated_atalone. - Covering indexes include all columns a query needs, avoiding a table lookup.
- Every index is a maintenance cost on inserts and updates — audit for unused indexes.
4. Pick data types that fit the domain
Use the smallest type that holds the real range: INT vs BIGINT, VARCHAR(n) vs TEXT, exact DECIMAL for money (never floating point). Wrong types waste storage and can silently truncate or round.
SQL vs. NoSQL modeling
The modeling approach follows the access pattern, not the hype.
| Relational (SQL) | Document / key-value / wide-column | |
|---|---|---|
| Model around | Entities and relationships | Queries and access paths |
| Joins | Native | Usually avoided; embed or duplicate |
| Schema | Enforced, evolves via migrations | Flexible or schema-on-read |
| Best when | Data is relational, integrity matters, queries vary | Access patterns are known and stable, scale-out matters |
In NoSQL you often design the table around the query ("query-first" modeling): duplicate data into the shape each read needs. That's the same denormalization trade-off, just mandatory rather than optional.
Common failure modes
- Missing indexes — a query that scans a growing table degrades linearly. Watch for full scans on filtered columns.
- Hot partitions — a partition key with skewed traffic (e.g., one celebrity user) overloads a single node. Choose high-cardinality, evenly distributed keys.
- Unbounded growth — tables that only grow (logs, events, history) eventually exhaust storage and slow backups. Plan retention, archiving, or partitioning up front.
- Over-indexing — too many indexes slow every write and bloat storage.
- Ignoring the write path — a schema that's fast to read but requires updating many rows per write will bottleneck under load.
How to practice these decisions
System Designer (systemdesigner.net) frames its material around learning concepts and putting them into practice, with learning paths covering fundamentals and core patterns, plus whiteboards for sketching architectures and guided project templates for full system design documentation. Its stated emphasis is on engineering judgment and understanding trade-offs — which is exactly the skill database design tests. For interview prep, the site's interview frameworks and practice problems give a structure to walk through schema decisions out loud.
A concrete exercise: take a known feature (a comments system, an order history), write the three queries it must serve fastest, then design the schema so those queries hit an index. That single loop — requirements → queries → keys and indexes — is database design in miniature.