System Design Lesson 4: Data Modeling, SQL, NoSQL, and Storage Choices

A request flow is only useful if the system can store and retrieve the right data correctly. This lesson turns the traffic and failure assumptions from the previous lessons into a data model and storage decision. The goal is not to declare one database “best”; it is to make access patterns, correctness rules, and operational costs explicit.

Prerequisites

Complete Lesson 3: DNS, HTTP, TLS, APIs, proxies, and load balancers and review the System Design learning track. Bring the functional requirements, SLOs, traffic estimate, and failure assumptions created in Lesson 2.

Learning outcome

By the end, you can derive a small schema from an API, choose a primary store based on workload rather than fashion, identify indexes and constraints, and explain what must stay strongly consistent versus what may be asynchronous.

Model the questions the product must answer

Start with reads and writes, written as sentences: “create an order,” “show a customer’s recent orders,” “find an order by public ID,” “reserve inventory once,” and “produce a daily sales aggregate.” These are access patterns. They lead to keys, uniqueness constraints, indexes, retention, and partition candidates. A data model that cannot serve its dominant reads will force risky workarounds later.

Worked example: orders

An order service owns orders and order lines. A relational design makes the integrity rules plain: an order has one customer, lines belong to one order, a public order ID is unique, and a payment provider’s event must not be applied twice.

CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  public_id UUID NOT NULL UNIQUE,
  customer_id BIGINT NOT NULL,
  status VARCHAR(32) NOT NULL,
  created_at TIMESTAMP NOT NULL
);

CREATE TABLE order_lines (
  order_id BIGINT NOT NULL REFERENCES orders(id),
  line_number INTEGER NOT NULL,
  sku VARCHAR(64) NOT NULL,
  quantity INTEGER NOT NULL CHECK (quantity > 0),
  PRIMARY KEY (order_id, line_number)
);

CREATE INDEX ix_orders_customer_created
  ON orders (customer_id, created_at DESC);

The index supports “recent orders for this customer” because its leading column matches the equality filter and its next column supports the ordering. Verify with the database query-plan tool; do not assume an index helps every predicate.

Choose the storage model from the workload

• Relational SQL: a strong default for transactions, joins, constraints, and flexible queries. It fits orders, accounts, billing, and relationships that must stay valid together.
• Key-value: useful when the primary access is a lookup by a known key and the value can be treated as a unit. It can be excellent for sessions or a fast lookup, but does not make cross-key invariants disappear.
• Document: fits aggregates commonly fetched and changed together with evolving fields. Avoid duplicating mutable facts blindly; decide which service is authoritative.
• Wide-column, search, time-series, or object storage: choose these when their query shape is central: high write throughput by partition, relevance search, append-heavy measurements, or large immutable blobs. They complement rather than automatically replace the transactional source of truth.

Define consistency before scaling

Classify every write. A payment event needs an idempotency key and durable record before the visible state changes. A product thumbnail can be generated later. Inventory reservations may require a conditional update or transaction so two requests cannot both consume the final unit. “Eventually consistent” is not a design: name what can be stale, for how long, how users see it, and how the system repairs it.

Keep a transaction local to the data it owns. When another service must react, commit an outbox record with the primary change and publish it asynchronously; consumers deduplicate using a stable event ID. This prevents a successful database update from being silently lost because a network publish failed afterward.

Review and validation steps

1. List the top five reads and writes with expected volume and latency target.
2. For each invariant, state where it is enforced: database constraint, conditional write, transaction, or application rule.
3. Load representative data and inspect query plans for the critical reads.
4. Test duplicate delivery, partial failure, and concurrent updates; prove idempotency rather than describing it.
5. Document ownership, backup restore objective, retention, encryption, and access policy for every store.

Common mistakes

• Choosing a database before writing access patterns.
• Using application-only validation when a database constraint can protect an invariant.
• Adding indexes without measuring write overhead or checking plans.
• Using a distributed transaction to hide unclear service ownership.
• Storing files in a transactional table when object storage and metadata are the actual workload.

Production guidance

Practice restore, not just backup. Monitor connection saturation, slow queries, lock waits, replication lag, storage growth, and failed migrations. Version schema changes so application versions can overlap during rollout. Encrypt in transit and at rest, limit production data access, and keep an auditable retention/deletion path.

Next lesson

Lesson 5 covers caching and CDNs: where a cache belongs, cache-aside behavior, invalidation, TTLs, stampedes, and edge delivery. A cache improves a known read path; it is not a substitute for a correct source of truth.

Sources

• PostgreSQL documentation: constraints
• PostgreSQL documentation: EXPLAIN
• Microsoft Learn: efficient querying with EF Core