NoSQL & Access-Pattern Modeling

Until now you built the model first and wrote queries later. In a relational database, a join can rescue a weak model. Many NoSQL stores have no join.

NoSQL is a name for stores that are not relational databases. Many of them split data across many machines, so they can stay fast with huge data. The price is that the model must fit the questions.

So the order of work turns around. First you list the questions the app asks. Then you shape the data so each question is one cheap read.

Recap: Normalization & Denormalization showed that a copy of a value removes a join but costs updates. Here you make copies on purpose.

What you will learn

1. How to list access patterns, and the four kinds of NoSQL store.
2. Documents: embed or reference.
3. Partition keys, sort keys, and one table for many kinds of items.
4. A second index, and one table for each query.
5. Hot partitions, the cost of copies, and when to stay relational.

An access pattern is one read or write that your app must do. Write each one down. Say what it is given and what it returns.

Take the shop behind a mobile app. The app needs three reads:

In SQL, P2 is a join of orders and order_items. P3 is a GROUP BY. Many NoSQL stores cannot do either one fast, so you shape the data for each pattern instead.

Four kinds of store

NoSQL stores come in four main kinds:

• Key-value: you give a key and get one value back. DynamoDB works this way.
• Document: the value is a JSON document, text with named fields and lists inside it. MongoDB is one.
• Wide-column: rows are grouped by a key and sorted inside each group. Cassandra is one.
• Graph: the data is things and the links between them.

Graph stores have their own chapter, Graph Data Modeling. The next sections use the first three kinds. Each section solves one pattern from the table.

A document store keeps JSON documents. For P2 you have two choices. You can embed the items inside the order document, or you can reference them by id.

Embedding puts the child data inside its parent, so one read returns both. Here are orders 101 and 105 as documents:

// Collection: orders (MongoDB-style)
{ "_id": 101, "customer_id": 1, "status": "paid", "amount": 40.00,
  "order_date": "2026-01-03",
  "items": [ { "product": "Pen", "quantity": 2 },
             { "product": "Notebook", "quantity": 1 } ] }

{ "_id": 105, "customer_id": 3, "status": "pending",
  "order_date": "2026-01-20", "items": [] }

// P2: read order 101
db.orders.findOne({ _id: 101 })

Embed when the child is read with the parent, is small, and belongs to only that parent. Items match all three. Use a reference when one of the three is false.

P1 needs a reference

Could you embed all of a customer’s orders inside the customer? No. The list has no upper bound, and a document can be at most 16 MiB in MongoDB.

So each order keeps a customer_id. You add an index on customer_id and order_date.

// An index on (customer_id, order_date) makes this one ordered read
db.orders.find({ customer_id: 3 }).sort({ order_date: -1 })

DynamoDB and Cassandra split a table into partitions. A partition is one slice of the table, kept together on a machine. The partition key decides which slice holds a row.

In DynamoDB, a row is called an item. The sort key puts the items of one partition in order. Cassandra calls it a clustering column.

A query gives one partition key. It can add a condition on the sort key, such as "begins with". The store then reads only that one slice.

Now design for P1. Use the customer as the partition key. Use the order date and id as the sort key, so one customer’s orders sit together, oldest first:

Ask for the two newest orders of customer 3. The query reads only the CUSTOMER#3 slice, from the end:

-- DynamoDB-style query, written in plain words
Query  PK = 'CUSTOMER#3'
       SK begins with 'ORDER#'
       newest first (read the slice backwards)
       limit 2

Now ask which customer made order 104. Order id is not in the partition key. Without a second index, the only way is a scan.

A scan reads every item in the table.

A scan is fine for 6 items. With millions, it is slow and costs money for every item read. A filter on a scan drops items only after the store has read them.

P2 needs an order and its items in one read. So give them the same partition key. The order is one item, and each product line is another item.

The items are different kinds, but they share one table. This is single-table design. The sort key says what kind of item each one is:

Items in one slice sort by their sort key, so HEADER comes before ITEM#. Read order 101 with one partition key:

-- DynamoDB-style query, written in plain words
Query  PK = 'ORDER#101'

Single-table design is a choice, not a rule. Many teams keep one table for each kind of item and are happy. The next table compares the two:

P1 asks for the orders of one customer. But each order now sits under its own ORDER# key. The orders of customer 3 are in two different slices, so no single partition key finds them.

A global secondary index (GSI) fixes this. It is a second view of the main table, called the base table, with a different partition key and sort key. The store keeps it up to date for you.

Give each order header two extra attributes. An attribute is one named value in an item, like a column. GSI1PK is the customer, and GSI1SK is the date and order id.

Items without these attributes do not appear in the index:

-- DynamoDB-style query on index GSI1, in plain words
Query  index GSI1
       GSI1PK = 'CUSTOMER#3'
       newest first
       limit 2

An index has costs:

• The store updates a GSI a moment after the table. So a new order may not show in the index for a short time.
• Every write to an indexed item also writes to the index, so writes cost more.

Cassandra takes a different path. The next section shows it.

In Cassandra, a query should name the partition key. The common advice is simple. Make one table for each query, and write to every table when data arrives.

Here is a table for P1. The partition key is customer_id. The rows inside each partition are sorted by order_date, newest first:

CREATE TABLE orders_by_customer (
  customer_id int,
  order_date  date,
  order_id    int,
  status      text,
  amount      decimal,
  PRIMARY KEY ((customer_id), order_date, order_id)
) WITH CLUSTERING ORDER BY (order_date DESC, order_id DESC);

SELECT order_id, status, amount
FROM orders_by_customer
WHERE customer_id = 3
LIMIT 2;

P2 gets its own table, orders_by_id, with the order id as the partition key. When order 107 arrives, your code writes it to both tables. That is the price of fast reads.

Keep each partition small. A partition that grows without end gets slow to read and hard to manage. A common fix is to add a month to the partition key.

P3 counts the orders on one day. A simple design uses the date as the partition key: DAY#2026-01-21. Every new order that day writes into that one partition.

A partition can handle only a limited amount of traffic. A partition that gets far more traffic than the others is a hot partition. On a busy day the store may slow down or refuse writes.

The fix is write sharding. You add a small number, the shard, to the key. Then one day uses several partitions.

Here is an imagined busy day. Order 106 is real. Orders 340, 341 and 342 are made up:

-- shard = the remainder of order_id divided by 3
SELECT order_id,
       'DAY#2026-01-21#' || (order_id % 3) AS partition_key
FROM busy_day
ORDER BY order_id;

Now P3 must read every shard and add the counts. That is the cost of sharding: one read becomes three.

SELECT 'DAY#2026-01-21#' || (order_id % 3) AS partition_key,
       COUNT(*) AS orders
FROM busy_day
GROUP BY order_id % 3
ORDER BY partition_key;

Say the P1 screen shows the customer’s name beside each order. Without a join, you copy the name into each order summary. One read then fills the screen.

The copy has a price. Imagine Asha changes her name to Asha Rao. Every copy of the old name must change:

-- SQL-style, to show which rows change. A NoSQL store needs one write per row.
UPDATE order_summaries
SET customer_name = 'Asha Rao'
WHERE customer_id = 1
RETURNING order_id, customer_name;

Compare the two designs for the name:

Some copies must never change. Say each order stores the country the customer had on that day. Order 101 stores IN, and Asha’s move to DE on 2026-01-08 must not change it.

Temporal & Historical Modeling covers this idea.

Pick the store from the dominant access pattern, not from fashion. Start with what the workload needs:

The shop has 5 customers and 6 orders. A relational database is the right answer today.

NoSQL is not a step up from a relational database.

Consistency

A store keeps copies of data on several machines. These copies are called replicas. A read may reach a replica that has not yet seen the newest write.

You choose per read. DynamoDB reads are eventually consistent by default. You can ask for a strongly consistent read on the base table, but not on a GSI.

In Cassandra you set a consistency level for each query. ONE waits for one replica. QUORUM waits for most replicas.

P1 can use the cheap default, because an order that is a second late is harmless. A check that an order is paid before it ships needs the latest value.

Six ideas to keep:

1. List the access patterns first. The queries are the spec.
2. In a document store, embed a small child that belongs to one parent. Reference an unbounded list.
3. The partition key decides the slice. The sort key orders the items inside it.
4. Put items that you read together under one partition key. A GSI serves a second question.
5. Spread busy keys with a shard number. The price is one read for each shard.
6. Every copy removes a read and adds a write. Stay relational when you cannot list the questions.

Check yourself

• Why is P1 a reference in MongoDB, and P2 an embed?
• In the shop table, why does P1 on CUSTOMER#3 return order 105 first?
• On the busy day, why is the count 4, and how many reads does P3 need?

Each exercise opens a canvas for an entity-relationship diagram (ERD). You draw the model, and the review checks it against sample rows.

Start with the first one. Write the questions the app asks before you draw anything.