Start from the data

Describe the data before naming a product: its shape, the questions the app asks, how much there is and how correct it must be. The decision chart turns those answers into a database type.

Decision chart: data and access pattern to database

Also called which database should I use, database decision chart, database selection

What: A quick map from the kind of data and the way it is used to the database type that usually fits. Start at the top and stop at the first line that matches your main need.
Why: Most wrong choices come from picking a product first. Matching on data and access pattern gets you to a sensible type in a minute, and the at-a-glance cards then help you pick a product.

Code

Users, orders, money, linked records -> relational
Varied JSON, read one record at a time -> document
Lookup by exact key, sessions, limits  -> key-value
Same costly read again and again       -> cache
Words typed into a search box, facets  -> search engine
"Find things that mean the same"       -> vector search
Totals over millions of rows, reports  -> columnar warehouse
Questions many hops through links      -> graph
Readings over time from devices        -> time series
Images, video, PDFs, backups           -> object storage
Not sure yet                           -> PostgreSQL

Watch out

Reading the chart as "one database per line". Several lines are often covered by one PostgreSQL database with extensions.

Structured, semi-structured and unstructured data

Also called data shape, semi-structured, unstructured data, structured data

What: Structured data fits rows and columns with known types. Semi-structured data has fields that vary between records, like JSON. Unstructured data has no fields at all, like images, audio and long text.
Why: Each shape has a natural home: tables, JSON columns or documents, and object storage. Most apps hold all three, so a single shape rarely decides everything.
Example: In a food delivery app, orders are structured, restaurant menus with varying options are semi-structured, and dish photos are unstructured files in object storage.

Watch out

Forcing every shape into one database, such as storing photos inside table rows.

Access patterns

Also called query patterns, data questions

What: The questions an app asks its data again and again, such as "get user by email" or "last 20 orders for a shop", with a rough idea of how often each runs.
Why: A database that answers your top five patterns easily is a good fit. Key-value and document stores in particular are designed around them, so they must be known first.
Example: Library app: find a book by title, list a member's current loans, show overdue loans, count loans per month, reserve a copy.

Watch out

Designing tables from imagined screens and discovering the real questions only after launch.

Read-write mix and data size

Also called read-heavy, write-heavy, data volume

What: Whether an app mostly reads or mostly writes, how many records it will hold after a year and how big each one is. Rough numbers are enough.
Why: Read-heavy apps benefit from indexes and caches; constant append-only writes point to time-series or columnar tools. Small numbers mean one ordinary database is plenty.
Example: 1,000 users posting twice a week makes about 104,000 posts a year: small for any modern database.

Watch out

Planning for imaginary scale and adding complex tools a small app will never need.

Consistency needs

Also called strong consistency, eventual consistency, stale data

What: How correct data must be at every moment. Strong consistency means every read sees the latest write; eventual consistency means copies catch up after a short delay.
Why: Balances, seats and stock must be exact, so they belong in a store with transactions. Like counts and trending lists can lag a little and can live in caches.

Watch out

Putting money or inventory in a store chosen only for speed, then seeing double bookings.

ACID transactions

Also called ACID, transaction, atomicity, isolation

What: A transaction groups changes so they are atomic (all or nothing), consistent (rules hold), isolated (concurrent transactions do not interfere) and durable (saved once committed).
Why: It is what stops two people buying the last seat or money vanishing between two accounts. Relational databases give it by default; many NoSQL stores now offer it too.

SQL

BEGIN;
UPDATE wallets SET balance = balance - 500 WHERE id = 1;
UPDATE wallets SET balance = balance + 500 WHERE id = 2;
COMMIT;

Watch out

Running related updates as separate statements without a transaction, so a crash halfway leaves data wrong.

Questions to ask before choosing

Also called database requirements, data brief

What: A short list answered for every project: data shapes, top access patterns, size after a year, what must be exact, any search, similarity or reporting needs, where it runs, and who will look after it.
Why: The answers make the choice explainable to a teammate, supervisor or client, and stop you bending the project around a tool you wanted to learn.

Code

1. What shapes is the data?
2. Top 5 access patterns, and how often?
3. Rows after one year? Size of each?
4. Which data must be exact?
5. Search? Similarity? Reports over everything?
6. Which cloud or host? Budget?
7. Who runs it, and what do they know?

Go deeper