Choosing a data model: relational to vector

A data model is the shape a store gives your data: rows in tables, nested documents, values under keys, nodes joined by edges, points on a timeline, or numbers that describe meaning. Every shape makes some questions cheap and others expensive, so you choose it from the questions your system will ask most often, never from habit or fashion. This lesson starts from those questions, the access patterns, then walks through the eight models a system designer meets most: relational, document, wide-column, key-value, graph, time-series, search and vector. One small dataset is then kept in three of the shapes, with the output recorded, so you can see the trade rather than take it on trust.

Start from the access patterns

An access pattern is one way the system touches its data: who reads or writes what, by which key or filter, how often, how fresh the answer must be, and which changes must happen together or not at all. Write them down before naming any database. The requirements lesson turns a prompt into features and quality goals; the access patterns are those features restated as reads and writes.

Here is an inventory for a food-delivery app serving one large city. The rates are assumptions for the example, the kind you would estimate in the round, not measurements:

1. Show a restaurant’s menu: by restaurant id, 4,000 reads a second at the peak; a minute old is fine.
2. Place an order and take payment: by customer and order id, 120 writes a second; exact, and the order, its lines and the payment must change together.
3. Track an order on screen: by order id, 3,000 reads a second; a few seconds old is fine.
4. Record rider locations: by rider id and time, 25,000 points a second; read back within seconds.
5. Search dishes by name: by the words typed, 600 queries a second; minutes behind is fine.
6. Suggest similar dishes: by the dish’s meaning, 300 queries a second; hours behind is fine.
7. Revenue per restaurant per month: by restaurant and month, a few queries a day; a day behind is fine.

Each pattern already leans towards a shape. Placing an order changes several records that must stay consistent, which is what relational transactions are for. The tracking screen reads one order with everything it shows, which suits a document. Rider locations arrive as a firehose of timestamped points, read back as recent ranges. Search ranks text, suggestions compare meaning, and monthly revenue scans history. Seven patterns, several shapes: that is normal, and the last section of this lesson deals with its cost.

Eight models, and what each makes cheap

Relational: tables, joins and constraints

The relational model keeps each fact once, in a row of a table, and joins tables when a question spans them. Its strength is everything around the data: transactions that change several rows atomically, and constraints such as unique keys, foreign keys and checks that the database enforces on every write (PostgreSQL constraints). Ad hoc questions are easy, because SQL can join and group in ways nobody planned. The price shows at scale: joins across very large tables and writes beyond one machine need care, which the partitioning module covers.

Document: the aggregate you read together

A document store keeps one nested record, such as an order with its lines, under one key. MongoDB’s manual states the principle well: data that is accessed together should be stored together, and embedding related data avoids joins (MongoDB data modeling). Fields may differ from one document to the next, which suits catalogues whose attributes vary by category. Two limits follow from the shape: a fact copied into many documents must be updated in all of them, and a document cannot grow without bound (MongoDB caps one at 16 mebibytes, limits).

Wide-column: partitions built for heavy writes

A wide-column store such as Apache Cassandra groups rows into partitions by a partition key, spreads the partitions across machines, and keeps rows sorted inside each partition by clustering columns. Cassandra’s documentation calls its data modelling query-driven: there are no joins, so each query gets a table shaped for it and data is duplicated across tables on purpose (Cassandra data modeling). In return, a well-chosen partition key spreads data and load evenly across the cluster and keeps lookups by key fast as it grows. A question nobody designed a table for is expensive, or impossible without scanning everything.

Key-value: one key, one value

A key-value store answers one question: the value stored under this key. It is the simplest shape, and the usual home for sessions, carts, feature flags and caches. Redis describes itself as a data structure server whose most basic type is the string, a sequence of bytes under a key, and adds hashes, lists, sets and sorted sets for richer values (Redis data types). Anything that is not a lookup by key, such as “all carts older than a day”, needs a structure you build yourself.

Graph: relationships as data

A graph database stores nodes and the relationships between them, each with properties, and answers questions by walking from node to node (Neo4j). “Accounts that share a device or a phone number with this one, up to three steps away” is a short traversal in a graph and a pile of self-joins in tables. The trade runs the other way for big aggregates, such as revenue across every order, which a graph computes by visiting nodes one at a time.

Time-series: append, then read ranges

A time-series store is built for measurements that arrive in time order and are read back as ranges and summaries: metrics, sensor readings, locations. Prometheus, for example, groups incoming samples into two-hour blocks, protects recent data with a write-ahead log, and compacts old blocks into larger ones in the background (Prometheus storage). Old data leaves by age: Prometheus keeps samples for 15 days unless configured otherwise. Updating an arbitrary old point, or joining it with business records, is not what these stores are for.

Search: an inverted index ranks text

A search engine keeps an inverted index: for every word, the list of documents that contain it, so a query for a few words finds and ranks matches without reading every document. PostgreSQL’s GIN index is one such structure (GIN stands for Generalized Inverted Index), and its documentation names the cost: one inserted row can mean many index entries, one per extracted word (GIN indexes). Dedicated search engines build relevance scoring, word forms and typo tolerance on the same idea.

Vector: nearest neighbours by meaning

A vector index stores embeddings, lists of numbers that a model computes so that similar meanings land close together, and finds the stored vectors nearest to a query vector. pgvector adds this to PostgreSQL with exact search and two approximate index types; its documentation says an HNSW index gives a better speed-recall trade-off than IVFFlat but builds more slowly and uses more memory (pgvector). Approximate means a fast answer may miss a true neighbour, so recall becomes a quality goal you measure.

The same data in three shapes

The script below keeps seven orders from three restaurants three ways: as relational rows in SQLite, as one document per order, and as a graph in which customers and restaurants are nodes and orders are edges. Each shape then answers the question it is best at:

# One small food-delivery dataset kept three ways: relational rows (SQLite), order documents and a graph of who
# ordered where. Each shape answers one question cheaply and makes another one expensive.
import json
import sqlite3
from collections import deque

RESTAURANTS = [(1, "Dosa Corner"), (2, "Biryani House"), (3, "Green Bowl")]
DISHES = [(11, 1, "Masala dosa", 120), (12, 1, "Filter coffee", 40), (21, 2, "Veg biryani", 220),
          (22, 2, "Raita", 50), (31, 3, "Quinoa salad", 260)]
ORDERS = [(1001, "asha", 1), (1002, "ravi", 2), (1003, "asha", 2), (1004, "meera", 3),
          (1005, "ravi", 1), (1006, "kabir", 3), (1007, "meera", 2)]
ITEMS = [(1001, 11, 2), (1001, 12, 2), (1002, 21, 1), (1002, 22, 1), (1003, 21, 2), (1004, 31, 1),
         (1005, 11, 1), (1006, 31, 2), (1007, 21, 1), (1007, 22, 2)]

# 1. Relational: one fact in one place, joined at read time.
db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE restaurants (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE dishes (id INTEGER PRIMARY KEY, restaurant_id INTEGER NOT NULL REFERENCES restaurants(id),
                     name TEXT NOT NULL, price INTEGER NOT NULL);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT NOT NULL,
                     restaurant_id INTEGER NOT NULL REFERENCES restaurants(id));
CREATE TABLE order_items (order_id INTEGER NOT NULL REFERENCES orders(id),
                          dish_id INTEGER NOT NULL REFERENCES dishes(id), qty INTEGER NOT NULL,
                          PRIMARY KEY (order_id, dish_id));
""")
db.executemany("INSERT INTO restaurants VALUES (?, ?)", RESTAURANTS)
db.executemany("INSERT INTO dishes VALUES (?, ?, ?, ?)", DISHES)
db.executemany("INSERT INTO orders VALUES (?, ?, ?)", ORDERS)
db.executemany("INSERT INTO order_items VALUES (?, ?, ?)", ITEMS)

print("Relational rows: revenue per restaurant, one query joining four tables")
for name, revenue in db.execute("""
    SELECT r.name, SUM(i.qty * d.price) AS revenue
    FROM restaurants r JOIN orders o ON o.restaurant_id = r.id
    JOIN order_items i ON i.order_id = o.id JOIN dishes d ON d.id = i.dish_id
    GROUP BY r.id ORDER BY revenue DESC"""):
    print(f"  {name:<14}{revenue:>6}")

# 2. Documents: everything one screen shows, stored together and read by one key.
dish = {d[0]: d for d in DISHES}
name_of = dict(RESTAURANTS)
documents = {}
for order_id, customer, restaurant_id in ORDERS:
    lines = [{"dish": dish[d][2], "qty": q, "price": dish[d][3]} for o, d, q in ITEMS if o == order_id]
    documents[order_id] = {"order": order_id, "customer": customer,
                           "restaurant": {"id": restaurant_id, "name": name_of[restaurant_id]},
                           "items": lines, "total": sum(x["qty"] * x["price"] for x in lines)}

print("\nDocument: the order-tracking screen reads order 1007 with one lookup")
doc = documents[1007]
print("  {")
for key, value in doc.items():
    if isinstance(value, list):
        print(f'    "{key}": [')
        print(",\n".join("      " + json.dumps(v) for v in value))
        print("    ],")
    else:
        print(f'    "{key}": {json.dumps(value)}' + ("," if key != "total" else ""))
print("  }")
copies = sum(1 for doc in documents.values() if doc["restaurant"]["id"] == 2)
print(f"  Renaming restaurant 2 rewrites {copies} documents; the relational schema updates 1 row.")

# 3. Graph: customers and restaurants as nodes, orders as edges, walked hop by hop.
edges = {}
for _, customer, restaurant_id in ORDERS:
    edges.setdefault(("customer", customer), set()).add(("restaurant", restaurant_id))
    edges.setdefault(("restaurant", restaurant_id), set()).add(("customer", customer))


def within_two_hops(start):
    """Nodes two hops from start (customer -> restaurant -> customer), and the edges followed."""
    seen, frontier, followed = {start: 0}, deque([start]), 0
    while frontier:
        node = frontier.popleft()
        if seen[node] == 2:
            continue
        for nxt in sorted(edges[node], key=str):
            followed += 1
            if nxt not in seen:
                seen[nxt] = seen[node] + 1
                frontier.append(nxt)
    return sorted(n[1] for n, hops in seen.items() if hops == 2), followed


people, followed = within_two_hops(("customer", "asha"))
print("\nGraph: who ordered from the same restaurants as asha")
print(f"  {', '.join(people)} (found by following {followed} edges)")

Relational rows: revenue per restaurant, one query joining four tables
  Biryani House   1030
  Green Bowl       780
  Dosa Corner      440

Document: the order-tracking screen reads order 1007 with one lookup
  {
    "order": 1007,
    "customer": "meera",
    "restaurant": {"id": 2, "name": "Biryani House"},
    "items": [
      {"dish": "Veg biryani", "qty": 1, "price": 220},
      {"dish": "Raita", "qty": 2, "price": 50}
    ],
    "total": 320
  }
  Renaming restaurant 2 rewrites 3 documents; the relational schema updates 1 row.

Graph: who ordered from the same restaurants as asha
  meera, ravi (found by following 7 edges)

Read the output as three trade-offs. Revenue per restaurant is one SQL statement over four tables, because the rows can be joined and grouped any way the question asks. The tracking screen gets everything it shows from one document, but that document copied the restaurant’s name: renaming the restaurant rewrites three documents where the relational schema changes one row, and a missed copy leaves two names in circulation. The graph finds the customers who share a restaurant with asha by following seven edges; in tables the same question is a self-join of orders on restaurant, which grows heavier with every extra hop.

One system, several stores

Real systems rarely fit one shape, so most use several stores. Martin Fowler’s note on polyglot persistence expects any sizeable organisation to run several storage technologies, each for the kind of data it suits (Fowler). The food-delivery app could reasonably keep orders in PostgreSQL, sessions in a key-value store, rider locations in a time-series store and dish search in a search engine.

Every extra store has a price, and the price is paid in correctness more than in money:

• A second copy to keep in step. The search index and the suggestions are copies of data whose source of truth is elsewhere. Writing to two stores from application code can leave them disagreeing after a crash between the two writes; the module on distributed transactions covers the safe ways to feed a copy.
• A delay readers can see. A copy fed asynchronously is behind its source, so a dish renamed a second ago may still show its old name in search.
• Another system to run. Backups, upgrades, monitoring, access control and on-call knowledge, for every store.

Two rules keep this manageable. Give every fact exactly one source of truth, and make every other store a derived copy you could rebuild from it. And before adding a store, check what the one you have can do: PostgreSQL’s jsonb type stores documents in a binary format that supports indexing (JSON types), its GIN indexes support text search, and the pgvector extension adds vector search. One store that covers four patterns adequately is often a better starting point than four stores that each cover one perfectly.

Practice

def to_document(order, items, restaurant):
    """The document the order-tracking screen reads with one lookup."""
    # Replace this line with your code.
    return {}


def rename_restaurant(documents, restaurant_id, new_name):
    """Change the copied restaurant name everywhere; return how many documents changed."""
    # Replace this line with your code.
    return 0

import copy

from order_doc import rename_restaurant, to_document

ORDER = {"id": 1007, "customer": "meera", "restaurant_id": 2}
ITEMS = [{"dish": "Veg biryani", "qty": 1, "price": 220}, {"dish": "Raita", "qty": 2, "price": 50}]
BIRYANI = {"id": 2, "name": "Biryani House"}


def test_document_shape():
    """embeds the lines and the restaurant, and adds the total"""
    assert to_document(ORDER, ITEMS, BIRYANI) == {
        "order": 1007,
        "customer": "meera",
        "restaurant": {"id": 2, "name": "Biryani House"},
        "items": [{"dish": "Veg biryani", "qty": 1, "price": 220}, {"dish": "Raita", "qty": 2, "price": 50}],
        "total": 320,
    }


def test_no_items():
    """an order without lines totals 0"""
    doc = to_document({"id": 5, "customer": "kabir", "restaurant_id": 3}, [], {"id": 3, "name": "Green Bowl"})
    assert doc["items"] == [] and doc["total"] == 0


def test_lines_are_copies():
    """the document keeps its own copy of each line"""
    items = copy.deepcopy(ITEMS)
    doc = to_document(ORDER, items, BIRYANI)
    items[0]["qty"] = 9
    assert doc["items"][0]["qty"] == 1


def test_wrong_restaurant():
    """refuses to copy another restaurant's facts"""
    try:
        to_document(ORDER, ITEMS, {"id": 3, "name": "Green Bowl"})
    except ValueError:
        return
    assert False, "expected ValueError"


def test_rename_counts_copies():
    """renames every copy of one restaurant and counts them"""
    docs = [to_document({"id": i, "customer": "c", "restaurant_id": r}, [], {"id": r, "name": f"R{r}"}) for i, r in [(1, 2), (2, 1), (3, 2)]]
    assert rename_restaurant(docs, 2, "Biryani Mahal") == 2
    assert [d["restaurant"]["name"] for d in docs] == ["Biryani Mahal", "R1", "Biryani Mahal"]

Interview questions

Warm-up (fresher to mid level): what is an access pattern, and why does it come before choosing a database? An access pattern is one way the system reads or writes data: what is read or written, by which key or filter, how often, how fresh it must be, and what must change together. It comes first because every data model makes some patterns cheap and others expensive: a document store reads an aggregate by id in one lookup but makes cross-document reports costly, while a relational database joins anything but needs care to scale writes. Choosing a store before listing the patterns means choosing the costs before knowing the workload.

Warm-up (fresher to mid level): why do many systems use more than one data store, and what does it cost? Because their access patterns differ: transactions, text search, time ranges and similarity search each have a shape that serves them best. The cost is that every extra store holds a copy that must be kept in step with its source of truth, lags behind it when fed asynchronously, and must be backed up, secured and operated. A good design names one source of truth per fact and treats every other store as a derived copy it can rebuild.

Key takeaways

• Write the access patterns first: what is read or written, by which key, how often, how fresh, and what must change together.
• Relational suits joins, constraints and transactions; document suits aggregates read whole; wide-column suits huge write volumes read by partition; key-value suits lookups by key.
• Graph suits multi-hop relationships; time-series suits appends read as ranges; search ranks text with an inverted index; vector finds nearest neighbours by meaning.
• Most systems use several stores. Give each fact one source of truth, treat every other store as a rebuildable copy, and check what your main database already covers before adding one.