SQL vs NoSQL: Choose From Data Rules and Access Patterns

Learning outcome: By the end of Day 6, you will be able to select a sensible starting database by writing down data invariants and access patterns before comparing products.

This lesson is part of Korshub's System Design in 30 Days roadmap.

The better question

“Is SQL or NoSQL better?” has no useful universal answer. A database choice is good only relative to a workload.

Start with two lists:

1. Invariants: What must always remain true? Examples include “a ledger entry must reference a real payment” and “a request identifier must not create two payment records.”

2. Access patterns: How will the application write and read the data? Examples include “find payment by ID” and “fetch one user's activity events over a time range.”

In this lesson, SQL is shorthand for a relational database that uses tables, relationships, and SQL. NoSQL is a broad category that includes document, key-value, wide-column, and graph databases. We use a document-style store for the activity-event example.

This distinction matters because “NoSQL” is not one consistency, schema, or scaling model. Modern document databases can support transactions, while relational databases can store semi-structured data. Treat SQL and NoSQL as design families, not opposing promises.

A practical comparison

The table gives tendencies, not universal product guarantees. Always verify the exact database, version, configuration, and workload.

Example 1: a payment ledger

Assume an order system writes three related facts:

• a payment record;

• an order-to-payment relationship; and

• a ledger entry.

A partial update is unacceptable. If the payment is recorded but the ledger entry is missing, the system has broken an important invariant. The application also benefits from uniqueness and relationship checks.

A relational database is a strong starting point because it can place related writes in a transaction and enforce constraints around keys and references. PostgreSQL, for example, documents primary-key, foreign-key, unique, not-null, and check constraints, along with defined transaction isolation behavior.

This does not mean every payment system must use one relational database forever. It means the relational model naturally expresses the requirements we wrote down.

Example 2: flexible activity events

Now consider an activity stream containing events such as:

{"type":"page_view","user_id":"u17","path":"/pricing","occurred_at":"..."}
{"type":"search","user_id":"u17","query":"system design","occurred_at":"..."}
{"type":"purchase","user_id":"u17","course_id":"c42","amount_minor":129900,"occurred_at":"..."}

The fields vary by event type. The common read might be “give me events for user u17 between two timestamps.” A document or key-value design can store the event as an aggregate and shape its key or index around the required read.

MongoDB documents support non-uniform records through a flexible document model. DynamoDB's official guidance makes the access-pattern-first rule explicit: understand the questions the model must answer before designing the table.

If these events later become the source of truth for billing, the requirements change. You may add stronger validation, transactions, a separate ledger, or a different store. Database decisions should follow the workload as it evolves.

Try this today

Before naming a database, write:

1. three invariants the system must protect;

2. three highest-value read or write patterns;

3. the records that must change together; and

4. which fields are stable versus intentionally variable.

Then choose a starting data model and write one sentence explaining the decisive requirement.

Completed example you can copy

Common mistakes

• Choosing from hype: popularity does not define your workload.

• Saying NoSQL has no schema: flexible schema still requires deliberate data modeling and validation.

• Saying NoSQL has no transactions: transaction support is product-specific, and modern NoSQL products may provide it.

• Assuming SQL cannot scale: scaling behavior depends on architecture, workload, and product features, not the query language alone.

• Ignoring access patterns: especially in key-value designs, a new query may require a new index or data shape.

• Forcing one database everywhere: a system can use different stores for transactional truth and read-optimized views when the added complexity is justified.

Knowledge check

1. What should come before selecting a database product?

2. Why is a relational database a sensible starting point for the payment example?

3. Why can a document model suit the activity-event example?

4. Does choosing NoSQL automatically mean giving up transactions?

Answers

1. Write the data invariants and access patterns.

2. Related records must change safely, and database constraints can protect relationships and uniqueness.

3. Event fields vary, while the main read can be designed around a known aggregate such as user plus time.

4. No. Transaction support depends on the specific database and configuration.

Continue learning with Korshub

Practice turning requirements into database decisions with Mastering the System Design Interview. Course details may change, so use the course page as the current source of truth.

Navigation

• Previous: Day 5 - Content Delivery Networks

• Roadmap: System Design in 30 Days

• Next: Day 7 - Database Indexing