Relational and document models

Apps stack data models in layers: objects sit on tables or documents, which sit on bytes on disk. Each layer hides the mess below it.

Declarative queries

In a declarative language like SQL, you say what result you want: which rows, how to sort, how to group. The database's query optimizer picks the indexes, join method, and order of steps. In imperative code, like a Python loop, you write each step yourself. Since a query never says how, the database can later speed it up or parallelize it.

How the relational model won

Edgar Codd proposed the relational model in 1970: data lives in tables of rows. NoSQL in the 2010s was a loose set of ideas, not one product. NewSQL tried to keep SQL and transactions while scaling like NoSQL. The lasting result is the JSON document model, which most relational databases now support too.

Objects, tables, and ORMs

App code works with objects; the database works with rows. The gap is called the impedance mismatch. An ORM writes the routine translation for you, and some also cache results or run schema migrations. But it cannot hide the tables fully. It also makes the N+1 problem easy: one query for N items, then one more query per item, instead of one join.

One-to-many as a document

A customer with several addresses and phone numbers is a one-to-many tree. One JSON document can hold the whole tree, so one read gets everything (locality). This works when the "many" is small, not thousands.

Many-to-one and many-to-many

Many customers share one city: many-to-one. Students take many courses, and courses have many students: many-to-many. Store each shared thing once and point to it by ID. A link table of (student_id, course_id) rows, with an index on each column, answers questions in both directions.

Remember

• Declarative: say what you want, the optimizer decides how.
• NoSQL was many ideas; JSON documents are what stayed.
• ORMs save typing, but watch for N+1 queries.
• Trees of small lists fit documents; shared things need IDs.