Data modeling
How to lay out tables for data that changes shape or time.
8 concepts · 2 technologies · 2 comparisons
Concepts
Start with CRUD- CRUDCreate, read, update, delete: the four basic things you do to stored records, mapped to SQL’s `INSERT`, `SELECT`, `UPDATE` and `DELETE` and to HTTP’s `POST`, `GET`, `PUT` or `PATCH`, and `DELETE`.
- Wide tablesOne row per entity and moment, with a typed column for every attribute or metric, so related values sit side by side.
- Hybrid (medium) tablesThe middle ground: a metric-name column plus a few typed value columns, or fixed columns plus a JSON or map column for the rest.
- Emergent schema (JSON columns)Keep the fields you know as columns and the rest in a JSON column, then promote keys to real columns once the data shows which ones matter.
- Narrow tablesOne row per entity, time and metric, with the metric’s name in one column and its value in another: the long or tall format.
- Entity-attribute-value (EAV)Store each fact as its own row of entity, attribute and value, so adding an attribute never changes the schema.
- Bitemporal modeling1 technologyRecord two timelines for every fact, when it was true in the world and when the database knew it, so you can correct the past without losing what you once believed.
- Persistence: state, events and replay3 technologiesThree ways to make data and work survive a restart: store the current state, store every change as an event, or store a program’s progress and replay it.
