NoSQL data modelling

Document — every relationship’s embed-or-reference decision with the reasons (cardinality, growth, read together, update frequency, size against the 16 MB document limit) and the pattern it uses; each collection as an example document with its shard key; the reads (as find queries), the writes (which documents each touches and whether it needs a multi-document transaction) and the risks. Export: one db.createCollection with a $jsonSchema validator per collection.

Wide column — one table per query: partition key, clustering columns with their order, the columns copied in, static columns, rows and bytes per partition (flagged past Cassandra’s guidance), plus counter, latest-value, rollup and fan-out timeline tables and a source-of-truth table for anything no query reads. Export: CQL.

Key-value — the key schema: one key per aggregate, list and set keys, and idx: keys that are secondary indexes kept by hand; each read as the commands it takes. Export: JSON.

Graph — nodes and relationships drawn (link entities such as an order line or a follow become relationships with properties, dashed); each read as a Cypher query. Export: uniqueness constraints.

Compare — every read pattern under each model, and the totals, in one table. Export: all four models as JSON.

Which database? — answer eight questions (the shape of the hot reads, how well the queries are known, consistency, scale, regions, latency, who runs it, memory). Each answer adds or takes away points from eight candidates with the reason shown, optionally with signals from the application itself (a multi-hop traversal favours a graph, a heavy time series a wide-column store). Answers for this example fills in a sensible set for the chosen application. Below the shortlist, CAP and PACELC are explained with a table of where common systems sit.

One-to-one or one-to-few, read together and bounded → embed. One-to-many is embedded too while the worst case stays under 1 MB — this page’s rule of thumb, well inside MongoDB’s 16 MB BSON document limit, because a growing document is rewritten and sent whole.

Read together but too large or growing without bound → subset: the newest N in the parent ($push with $slice), all of them in their own collection.

Read together, usually small, but a few parents have vastly more → outlier: embed up to a threshold, flag the parent and put the rest in overflow documents.

Unbounded time series → bucket: one document per parent per hour or day (MongoDB 5.0+ time series collections do this internally).

Many-to-one with fields the hot read needs → extended reference: keep the id and copy those fields; updating them fans out to every copy unless the copy is a deliberate snapshot (an order line’s product name at the time of purchase).

Many-to-many, bounded → an array of ids (or extended references). Unbounded or not read together → reference: the child stores the parent’s id.

An aggregate read more often than its inputs change → computed: kept in the document and updated on every write.

An entity is embedded in at most one parent; a second relationship to it is a reference.

Shard keys: the top-level field of each collection’s busiest read; a read without it is scatter-gather.

The models are derived by rules from a short description, not designed by hand: a real schema would also weigh things the description does not hold (which fields change together, privacy, retention, how the application handles stale data). Treat each model as a well-argued first draft.

Sizes and counts are illustrative orders of magnitude for a mid-sized service. Fan-out counts (how many copies an update must reach) are estimated as count(referencing) ÷ count(referenced).

"One read" means one request to one place; it says nothing about how many bytes come back or how hot the partition is — see row key design and NoSQL sizing for that.

MongoDB secondary indexes and Cypher property indexes are assumed to exist for every field a pattern looks up by; Cassandra secondary indexes and materialised views are not used — each query gets its own table, as the Cassandra guidance recommends.

The questionnaire ranks eight well-known systems and leaves out many good ones (Couchbase, HBase, Bigtable, FoundationDB, ArangoDB, TiDB…). Classes in the PACELC table describe default configurations.