beginnerintermediateadvanced

System Design · Database Fundamentals

Database Fundamentals for System Design

The system-design-level judgment calls — model choice, polyglot persistence, transaction boundaries — not the generic SQL/NoSQL mechanics

Relational vs non-relationalconsistency + joins vs scale-out + flexibility
Choose per workloadone system can use both, deliberately
Polyglot persistenceeach store needs a stated reason
Cross-service writesno single ACID transaction spans services

1.Choosing a data model at a system-design level

The choice between a relational database and a non-relational one is not "which is better" — it is which trade-offs a specific workload can afford. Relational buys strong consistency, joins and constraints, at the cost of harder horizontal scaling for writes. Non-relational buys easier horizontal scaling and schema flexibility, at the cost of weaker built-in consistency guarantees and no cross-collection joins.

Key features

Relational (PostgreSQL/MySQL): strong consistency, joins, constraints — scales reads easily, writes harder to shard
Non-relational (MongoDB-style document, key-value, wide-column, graph): flexible schema, easier horizontal write scaling
The generic mechanics of each model (SQL joins, document embedding, ACID, indexing) belong to the PostgreSQL, MySQL or MongoDB roadmap, not here
The system-design-specific question is which trade-off a given workload's access pattern and consistency requirement can actually afford
A single system commonly uses both — relational for the transactional core, non-relational for a specific high-write or flexible-schema workload

The trade-off, at a system-design level

The trade-off, at a system-design level
Requirement	Favors	Why
Multi-row transactions across entities	Relational	ACID transactions and joins are native
Schema changes often, per-record	Non-relational (document)	No migration needed for a new field on one record
Very high write throughput, simple access pattern	Non-relational	Easier to shard writes across many nodes
Strict referential integrity (foreign keys)	Relational	Constraints are enforced by the database itself

Relational vs. non-relational, by workload

Relational

Strong consistency, native joins
Multi-row transactions across entities
Harder to shard writes

Non-relational

Flexible, per-record schema
Easier horizontal write scaling
Weaker built-in joins and consistency

Command

text

relational:     strong consistency + joins, harder write-sharding
non-relational: flexible schema + easier write-sharding, weaker built-in joins

Relational buys strong consistency and joins at the cost of harder write-sharding; non-relational buys easier write-scaling and schema flexibility at the cost of weaker built-in joins and consistency — choose per workload, not once for the whole system.

2.Polyglot persistence: multiple stores, each for a stated reason

Most real systems end up using more than one kind of database — a relational store for transactional data, a cache for hot lookups, maybe a search index for full-text queries, object storage for files. Polyglot persistence is choosing that mix deliberately, with a stated reason per store, rather than accumulating stores ad hoc.

Key features

Polyglot persistence: deliberately using more than one storage technology, each for what it is best at
Common combination: a relational database (transactional core) + a cache (hot reads) + a search index (full-text) + object storage (files)
Each store in the mix should have a stated reason — "why is this data here and not in the primary database"
The cost of polyglot persistence is operational: more systems to run, monitor, and keep consistent with each other
The alternative — forcing every workload into one store — avoids that cost but pays it back in that store's weaknesses everywhere

A typical polyglot mix, and the stated reason for each

A typical polyglot mix, and the stated reason for each
Store	Holds	Why this store specifically
PostgreSQL	Orders, accounts, payments	Needs ACID transactions and joins
Redis	Sessions, hot lookup cache	Sub-millisecond reads, ephemeral is fine
Elasticsearch	Full-text product search	Relational full-text search does not scale to this query shape
S3-style object storage	Uploaded images, PDFs	Large binary blobs do not belong in a relational row

One e-commerce system, four deliberate stores

PostgreSQL

orders, payments — needs ACID

Redis

sessions, hot cache

Elasticsearch

full-text search

photos, invoices

Command

text

for each store in the system, ask:
  what specific requirement does this store satisfy
  that the primary database does not?
  no answer → it probably shouldn't be a separate store

Using more than one storage technology is normal and often correct — but each store in the mix should have a stated, specific reason it exists, or it is pure operational cost with nothing bought in return.

3.When a transaction must span multiple services

A single database's ACID transaction only protects writes inside that one database. The moment "one logical operation" touches more than one service — each owning its own database — there is no single transaction that can wrap all of it, and the design has to decide explicitly how to keep those writes consistent instead.

Key features

A relational database's ACID transaction is scoped to that one database — it cannot span a second service's database
A "logical" transaction spanning services (reserve inventory, charge payment, create order) needs an explicit strategy, not a database feature
Common strategies: a saga (a sequence of local transactions with compensating actions), or a transactional outbox to publish events reliably
Recognizing that a boundary is being crossed is the system-design-level skill — the strategy itself belongs to distributed-transactions topics
Not every multi-write operation needs this — if all the writes are inside one service's one database, a normal local transaction is enough

Same-database vs cross-service writes

Same-database vs cross-service writes
Writes involved	Fits in one ACID transaction?	What is needed instead
Two rows, one database, one service	Yes	Nothing extra — a normal transaction
Inventory (service A) + payment (service B)	No	A saga, or an outbox-based event flow
Order (service A) + notification (service B, best-effort)	No, and often does not need to be	Fire-and-forget event; notification failure should not roll back the order

Checkout across 3 services — no single ACID transaction

thensucceedsfails

Reserve inventory

service A, own DB

Charge card

service B, own DB

Create order

service C, own DB

Release reservation

compensating action

Command

text

one database, one service:  normal ACID transaction
multiple services:          saga / outbox — designed explicitly,
                             not provided by any single database

An ACID transaction is scoped to one database — the moment an operation spans services, there is no built-in "all or nothing," and the design needs an explicit strategy (a saga, an outbox) rather than assuming atomicity it does not have.

Spotted a mistake on this page? Tell us.

Read Sheet