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:
- What are the main questions? Write the five most common reads and writes.
- How are the items linked? Trees suggest documents. Many-to-many suggests relational. Deep paths suggest graph.
- How big, and how fast? Very large writes suggest wide-column or key-value.
- How strict must the rules be? Money and stock suggest relational with transactions (Level 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 |