CSE Roadmap

Core tier · about 3 hours · 8 parts

Part 4 of 8 · 4 min read

The document model

Storing a whole record as one nested document, and the price paid for joins.

Think of an index card that holds everything about one person: contact details, jobs, schools, all on one card. To read about the person, pick up the card. No other drawer is opened.

Document databases keep data like that.

Document database

A document database stores each record as one self-contained document, usually JSON, which can hold nested lists and objects.

Example: a user profile stored as {"id": 251, "name": "Bill", "jobs": [{"title": "Chair", "org": "Foundation"}]}.

The book uses a résumé as its example. The person has one name but several jobs and several schools. In a relational design, the jobs and schools go in separate tables that point back to the user. A document keeps them inside the profile. This has two effects.

  1. Locality. All the data comes from one place. The book says a single query is enough, while the relational form needs several queries or a messy join.
  2. Closer to the program. The record often has the same shape as the objects in code. The book calls the gap between objects and tables an impedance mismatch (a term borrowed from electronics), and says a document reduces it, though not completely.

A worked example. A profile has 3 jobs, 2 schools and 4 contact items.

  • Relational: 4 tables (user, jobs, schools, contacts). Read the whole profile with 4 queries, or one join over 4 tables.
  • Document: 1 read. If a read takes 5 ms, that is 5 ms against up to 20 ms for 4 separate reads (made up).

The one-to-many data forms a tree: the user at the top and the items below it. Documents fit trees well.

The weakness is shown by the other kind of link. If the company where the person worked becomes its own entity with a page and a logo, many people point to the same company. That is a many-to-one link. If a person can recommend another person, links go both ways between people. These need references between documents, and joins are weak or missing in many document databases. The book says that without joins, the program must make several queries and combine the results itself. That moves work from the database into the application and is usually slower.

The book's rule of thumb: a document model suits data that has a document-like structure, where the whole tree is loaded at once. It becomes awkward as data grows more interconnected. As features are added, data tends to become more connected.

The book also points to a hidden detail in the word "schemaless".

Schema-on-read

In schema-on-read, the database accepts any structure, and the program that reads the data decides what to make of it. The structure is implicit.

Example: a document may or may not have the key last_name. The reading code must handle both.

The book compares the two styles to type checking. Schema-on-write is like checking types before the program runs. Schema-on-read is like checking at run time. Neither is always right.

A worked example of the difference when a format changes. A user's full_name field is to be split into first_name and last_name.

  • Schema-on-read (document): new code writes the two new fields. Reading code checks: if first_name is missing, split full_name. Old and new documents live together. No downtime. Reading code carries the extra check as long as old documents exist.
  • Schema-on-write (relational): a migration adds the two columns and fills them from full_name. For a table of 50 million rows, this can take a long time, and many databases can do it without locking, but it must be planned.

The book adds that the two models are growing closer. Several relational databases now support JSON columns, with the ability to query inside them. Some document databases now support joins. A hybrid of the two is likely to serve most needs.

Common mistake

Choosing documents because "the schema is flexible" and ignoring the many-to-many links the product will gain in a year. Flexible does not mean free of structure. The structure is just moved into the code.

Check yourself:

  • Why does a profile with jobs and schools suit one document?
  • What happens to a document model when many documents point to the same company?