CSE Roadmap

Core tier · about 3 hours · 8 parts

Part 7 of 8 · 3 min read

Choosing a model, and mixing models

A checklist for the choice, and why real systems keep more than one kind of store.

Choosing a model, and mixing models

Think of a workshop. A hammer, a saw and a screwdriver all have a place. Using a saw to drive a nail is possible but silly.

The book says that different applications have different needs and that the best technology for one case may differ from another. It calls the habit of using several kinds of data store together polyglot persistence. Most large systems do it.

A worked example. An online shop could use:

Data Store Reason
Orders, customers, payments Relational Many-to-many links, strict rules, flexible queries
Product pages Document Whole page read at once, fields differ by product type
Sessions, carts, counters Key-value Fast lookup by key
Click and view events Wide-column Huge write stream, read by user and time
"People who bought this also bought" Graph Paths between products and buyers
Product search Search engine (Level 6) Text search

The cost of mixing: more systems to run, more skills, and the data must be kept in step between stores (Level 6). So start with one general store, and add another only when a clear need appears.

A checklist to choose a model:

  1. What are the main questions? Write the five most common reads and writes.
  2. How are the items linked? Trees suggest documents. Many-to-many suggests relational. Deep paths suggest graph.
  3. How big, and how fast? Very large writes suggest wide-column or key-value.
  4. How strict must the rules be? Money and stock suggest relational with transactions (Level 5).
  5. How much will the shape change? A shape that changes often favours schema-on-read, but needs discipline in code.

In an interview, choose the model aloud, with the reason tied to the questions. Say what you give up. For example: "I use a relational database for orders because they link to customers and products and need transactions. I accept that it is harder to spread across machines, and I will partition it when the numbers need it."

For the curious For the curious

The book remarks that relational and document databases are growing more alike. A relational database with a JSON column can store a document and also join. So the choice is often about which strengths are needed most, not about a hard wall between the two.

Interview angle

Answer in your head first, then open it.

Which database would you use for this system?

Name the main reads and writes, then the model that makes them cheap. Mention one alternative and why it was not chosen.

Relational or document?

Relational when data is highly linked or strict rules matter. Document when a whole record is read at once and the shape varies.

Where does a graph database fit?

Where questions are about paths several steps long. Otherwise use joins.

Why not use one database for everything?

A single store is the simplest start. Different access patterns, sizes or rules can justify more, at the price of keeping them in step.

Check yourself:

  • Name two data types in an online shop and the model that fits each.
  • What is the cost of using several kinds of store?

Words you will meet

Word Plain meaning
Data model The way a database organises information
One-to-many, many-to-one, many-to-many The three kinds of link between items
Table, row, column The parts of a relational database
Primary key, foreign key A row's unique id, and a pointer to another row's id
Join Combining rows by matching keys
SQL The query language of relational databases
Normalization Storing each fact once
Denormalization Copying facts on purpose to speed reads
Query optimizer The part that chooses how to run a query
Schema-on-write, schema-on-read Structure checked at write time, or at read time
Document database Stores whole nested records
Key-value store Stores one value per key
Wide-column store Sorted rows with many sparse columns
Graph, vertex, edge A network of things and links
Polyglot persistence Using several kinds of store together