Database Design Patterns: 12 Patterns You Should Know

Most database problems are not new. The table that was fine at ten thousand rows and unusable at ten million, the report that locks the system every morning, the tenant whose data leaked into another tenant's query: each one has a named solution that people have been applying for decades.

Those named solutions are database design patterns. This guide covers twelve of them: what each one solves, the move it makes, and the price you pay for using it.

A note on scope. This is about patterns, not process. If you want the step-by-step method for designing a schema from scratch, requirements through normalization to physical design, see the 7 steps in designing your database. The patterns below are what you reach for once you know the process and start hitting real constraints.

What Is a Database Design Pattern?

A database design pattern is a reusable solution to a problem that recurs when you store and query data at scale. "My reports are too slow because they join six tables every time" is a recurring problem, and the materialized view is its named solution. "Two users edited the same row and one update vanished" is a recurring problem, and optimistic locking is its named solution.

Patterns operate at three different levels, and mixing them up is a common source of confusion:

• Schema-level patterns shape your tables and columns: normalization, soft deletes, audit trails, star schemas.
• Access-level patterns shape how queries reach the data: indexing strategy, materialized views, locking.
• Distribution-level patterns shape how data spreads across machines: replication, sharding, partitioning.

The twelve below are grouped in that order, because that is roughly the order in which you will need them.

Schema Patterns

1. Normalization, and When to Denormalize

The problem: The same fact stored in several places drifts apart. A customer changes their address, three tables disagree, and now nobody knows which one is right.

The move: Normalize. Store each fact once, in one table, and reference it by key elsewhere. Third normal form is the practical target for transactional systems: every non-key column depends on the key, the whole key, and nothing but the key.

The price: Reads get more expensive, because answering a question now requires joins. A fully normalized order history might touch five tables.

When to denormalize: Deliberately, and for a measured reason. If a query runs constantly and its joins are the bottleneck, copying a column to avoid a join is a legitimate trade. What makes it safe is owning the consequence: you now have two copies of one fact, and you need a defined mechanism keeping them in step. Denormalizing by accident, because it seemed easier at the time, is how data drift starts.

2. Soft Delete and Audit Trail

The problem: A row is deleted and the business immediately needs to know what it contained, who removed it, and when.

The move: Do not physically delete. Mark the row inactive with a deleted_at timestamp and filter it out of normal queries. For a full history, write every change to a separate audit table: what changed, the old value, the new value, who did it, and when.

The price: Every query now has to remember the filter, and forgetting it is a real bug that shows deleted data to users. Tables grow without bound unless you archive. Unique constraints get complicated, because two soft-deleted rows may legitimately share a value that must be unique among live rows.

Practical note: Consider whether you need soft delete or an audit trail. They solve different problems, and teams often build one while needing the other. Soft delete is about recoverability. An audit trail is about accountability.

3. Star Schema for Analytics

The problem: Analytical questions ("revenue by region by month") run badly against a normalized transactional schema, and running them at all competes with live traffic.

The move: Model analytics separately as a star. One central fact table holds the measurements, one row per event, with foreign keys out to dimension tables that describe the context: date, product, customer, region. Dimensions are deliberately denormalized and wide.

The price: You are maintaining a second model of the same business, plus the pipeline that loads it. The data is as fresh as your last load, so analytics answers lag reality.

Why it works: Analytical queries filter and group by dimensions and aggregate over facts, and the star shape makes exactly that access pattern cheap. This is why data warehouse design looks so different from application schema design: it is optimized for a different question.

4. Multi-Tenancy

The problem: One application serves many customers, and their data must stay separated.

The move: Choose one of three levels of isolation.

• Shared schema, tenant column. Every table carries a tenant_id. Cheapest and easiest to operate, and the model that scales to the largest number of tenants.
• Schema per tenant. One database, one namespace per tenant. Stronger isolation, and per-tenant backup and restore becomes straightforward.
• Database per tenant. Strongest isolation, easiest compliance story, and the option to give a large customer dedicated resources.

The price: It rises as you move down the list. Shared schema is one forgotten WHERE tenant_id = ? away from a data leak, so the filter belongs in a layer that cannot be skipped, not in individual queries. Database per tenant means migrations now run hundreds or thousands of times, and that becomes its own engineering problem.

How to choose: Match isolation to what your customers actually require. Regulated industries often force database per tenant. A high-volume product with many small accounts usually cannot afford anything but shared schema.

Access Patterns

5. Indexing Strategy

The problem: A query scans the whole table because nothing tells the database where to look.

The move: Index the columns your queries filter, join, and sort on. Two refinements matter more than most people realize. A composite index covers several columns, and column order decides which queries can use it: an index on (tenant_id, created_at) serves a filter on tenant_id alone, but an index on (created_at, tenant_id) does not. A covering index includes every column a query needs, so the database answers from the index without touching the table at all.

The price: Every index slows down writes, because each insert, update, and delete must maintain it. Indexes consume storage and memory. Unused indexes are pure cost, and most mature databases have several.

The discipline: Add indexes in response to measured slow queries, and read the query plan afterwards to confirm the index is actually used. An index the planner ignores is worse than no index, because it costs writes and buys nothing.

6. Optimistic and Pessimistic Locking

The problem: Two users read the same row, both edit it, and both save. One person's change silently disappears.

The move: Pick a concurrency strategy on purpose.

• Optimistic locking adds a version number to the row. On update, the writer requires the version to be unchanged since it read. If it changed, the update fails and the application decides what to do: retry, merge, or ask the user.
• Pessimistic locking takes a lock when reading, so nobody else can modify the row until the first writer finishes.

The price: Optimistic locking pushes conflict handling into the application, and under high contention it means frequent retries. Pessimistic locking blocks other work by design, which costs throughput and introduces deadlock as a real possibility.

The rule of thumb: Optimistic for low-contention data, which is most data. Pessimistic when a conflict is genuinely unacceptable or expensive to unwind, such as decrementing the last unit of inventory.

7. Materialized View

The problem: An expensive aggregate is recomputed on every request, and the underlying data barely changes between requests.

The move: Compute it once and store the result as a real table. Queries read the stored result. Refresh it on a schedule, or incrementally as the source data changes.

The price: The view is stale between refreshes, and you must decide how stale is acceptable. Refreshing costs work, and a full refresh of a large view can be heavy enough to need its own scheduling window.

Related pattern: This is the database-level version of CQRS, where reads and writes get separate models. Read more about CQRS and the other architecture-level patterns if you are designing at the service level rather than inside one database.

8. Polyglot Persistence

The problem: One database is being asked to do four jobs it was never designed for: full-text search, time-series rollups, graph traversal, and transactional writes.

The move: Use the right store per job. Keep the transactional system of record in a relational database, push search into a search index, time-series into a time-series database, and relationship queries into a graph store.

The price: Every additional store is another thing to operate, monitor, back up, and keep in sync. You inherit consistency problems between stores that did not exist when everything lived in one place.

The caution: This pattern is frequently adopted too early. A relational database handles full-text search and JSON documents adequately at moderate scale. Add a specialized store when you have measured that the general-purpose one cannot do the job, not because the specialized one is a better fit in principle.

Distribution Patterns

9. Primary-Replica Replication

The problem: Read traffic exceeds what one database can serve.

The move: One machine accepts all writes and streams its change log to replicas that serve reads. If the primary fails, a replica is promoted to take its place.

The price: Replicas lag behind the primary, usually by milliseconds but sometimes by much more under load. A user can save a change and not see it on the next page load, because that read went to a replica that has not caught up. Replicas add no write capacity at all, so this pattern does nothing for a write bottleneck.

Handling the lag: Route reads that must reflect a user's own recent write to the primary. Everything else can go to replicas.

10. Sharding

The problem: Write throughput or total data size has outgrown a single machine, and replication does not help because every replica takes every write.

The move: Split rows across machines by a shard key. Each machine owns one slice of the data and handles the writes for that slice.

The price: The shard key is the whole decision. A good one appears in nearly every query, spreads load evenly, and keeps related rows together. A poor one leaves you with queries that must fan out to every shard, transactions that span shards, and hot keys where one machine saturates while others idle.

Consistent hashing: Use it to decide which shard owns which key. With naive modulo routing, adding a fifth shard to a four-shard cluster relocates most of your data. On a hash ring, it moves roughly the slice the new machine takes over.

The advice: Shard late. It is the most expensive pattern here in ongoing complexity, and vertical scaling plus replication plus caching solves a surprising number of problems that look like sharding problems.

11. Vertical Partitioning

The problem: A wide table has a few hot columns read constantly and several cold ones, such as large text or binary blobs, read almost never. Every read pays for the cold columns.

The move: Split the table by column group. Keep the hot, narrow columns in one table and move the cold or bulky ones into a companion table sharing the same key.

The price: Queries needing both groups now join. You have introduced a one-to-one relationship that must be kept consistent.

Note the contrast: Sharding is horizontal partitioning, splitting by row. Vertical partitioning splits by column. They solve different problems and combine freely.

12. Outbox and Change Data Capture

The problem: A row is committed and something outside the database needs to know: a search index, a cache, another service, an analytics pipeline. Writing to the database and publishing a message are two separate operations, and either can fail alone. That is a dual write, and it drifts silently.

The move: Make the notification part of the transaction.

• The outbox pattern writes the event to an outbox table in the same transaction as the data change. A separate process reads the outbox and publishes. Because the write and the event commit together, they cannot disagree.
• Change data capture skips the extra table by reading the database's own replication log and turning committed changes into a stream.

The price: Both add moving parts. The outbox needs a publisher process and a cleanup policy. CDC couples consumers to your schema, so a column rename becomes everyone's problem. Delivery is at-least-once in both cases, so consumers must tolerate duplicates.

How These Patterns Compose

Patterns are rarely used alone, and the interesting engineering is in the seams.

A typical high-traffic application normalizes its core schema, denormalizes two or three specific hot paths, indexes deliberately based on measured slow queries, serves most reads from replicas while routing read-your-own-write traffic to the primary, keeps analytics in a separate star schema loaded through change data capture, and uses optimistic locking on anything users edit concurrently. Sharding arrives last, when a single primary genuinely cannot absorb the writes.

Notice that each pattern was a response to a specific measured problem. That is the whole discipline: recognize the shape of the problem, apply the pattern that fits, and be able to say what it cost you.

Frequently Asked Questions

What are database design patterns? Database design patterns are reusable solutions to problems that recur when storing and querying data: normalization, indexing strategy, replication, sharding, partitioning, locking, materialized views, multi-tenancy, and change data capture. They span schema shape, query access paths, and how data distributes across machines.

What is the difference between database design patterns and system design patterns? Database design patterns concern the data layer specifically: how tables are shaped, how queries reach rows, how data spreads across machines. System design patterns are broader, covering communication, caching, reliability, and scaling across an entire distributed system. Several patterns, sharding and replication in particular, appear in both because the data layer is where they bite hardest.

Which database design pattern should I learn first? Indexing strategy. It is the pattern with the largest ratio of impact to effort, most performance complaints trace back to it, and understanding composite and covering indexes will resolve more real problems than anything else on this list.

Is denormalization bad practice? No, but accidental denormalization is. Copying data to avoid an expensive join is a legitimate optimization when the query matters and you have a defined mechanism keeping the copies consistent. It becomes a problem when it happens without anyone deciding it should.

When should I shard my database? Later than you think. Exhaust vertical scaling, read replicas, caching, and index tuning first. Shard when a single primary cannot absorb your write volume or your data will not fit on one machine. Sharding early means paying real, permanent complexity for scale you may never reach.

Next Steps

These twelve cover the data layer. They sit inside a larger catalog of roughly sixty architecture-level patterns spanning communication, caching, reliability, scaling, consistency, and AI infrastructure, all of which are in the complete guide to system design patterns.

To learn each pattern through the real production incident that created it, along with the trade-offs and a short interview answer for each, take my system design patterns course.

If you are preparing for interviews specifically, Grokking the System Design Interview covers applying these choices to real design questions.