# The data model — how tables relate

The decisions here outlive any one app, and most become expensive the moment a second screen depends
on them. They are separate from `lotics docs building_an_app` on purpose: **every workspace starts
with tables and many never get a custom app**, so schema design is not a chapter of app building.

`ONE FACT, ONE COLUMN` — the rules governing a single table's own columns — is stated at the tools
that add a field, `create_table` and `update_table`, where you meet it while deciding. This doc is
the other half: how tables relate to each other.

Everything below shares one signature. **Both sides read correctly on their own**, so nothing reports
the problem — no error, no empty column, no failing query. Each is found by looking for it.

## One entity, one table — and the test is measurable

Variation belongs in a column — a multi-select role, a kind, a stage — not in a second table. Two
tables for one kind of thing give the same real-world entity two rows, two ids and two halves of its
history, and each screen shows whichever half it happens to link to.

Split tables are often right. A supplier book beside a customer book is a normal shape, and merging
on suspicion is a large repoint bought for nothing. So do not argue it in the abstract:

> **List both tables' names and look for one that appears in both.**

None means the split is holding. One means it has broken — the usual cause is a party you begin to
invoice as well as buy from — and the fix is to merge before a second screen depends on the copy.

Worth writing as a test rather than a note, because a note about a condition nobody re-checks goes
stale in silence.

## One vocabulary wherever values are COPIED between tables

Two `select` fields for one concept carry DIFFERENT option keys even when their labels match — keys
are minted per field. So anything moving a value between them needs a hand-written key map.

That map is code. Put it in one named module with a test; written inline at the copy it is invisible,
untested, and silently wrong the first time somebody renames an option, because a rename leaves the
key intact and the map still compiling. Prefer a link to a shared reference table where the set is
open or growing; keep a map only for a small closed set.

Where one side genuinely holds MORE values than the other, that is not drift — it is the model
telling the truth. The wider side must **refuse** what the narrower one cannot express rather than
quietly picking the nearest value.

## A copy boundary accounts for EVERY source field

Each field on the source gets a column on the destination, a deliberate drop with the reason written
down, or a refusal.

A field with nowhere to land is data destroyed at the boundary, and it is invisible afterwards: the
destination is not empty and not obviously wrong — just a number that no longer agrees with where it
came from.

## Provenance is a LINK, not a flag and not a copy

A row created BY another row carries a link to it.

That link is what makes "is this the estimate or the actual", "where did this come from" and "have we
already imported this" answerable at all. A boolean records that something was true once; a link
stays true, survives a rename, and lets the next write UPDATE the original instead of adding a second
row beside it.

## Say what makes two rows the SAME row

Declare the natural key in the table's description.

Anything that imports, reconciles or de-duplicates has to decide identity, and with no declared key
it falls back to comparing displayed text — which is how one company arrives three times under three
spellings. Name the key: a reference number, a tax id, a link plus a period. Then match on `rec_…`
and `opt_…`, never on rendered labels.

## Keep derived chains shallow

Formulas and rollups are computed and STORED when a row is written, and one that reads another
recomputes with it. A rollup over a formula over a formula is paid three times on every touch, and
again for every row upstream of it.

**Depth costs more than row count.** This is the optimisation lever that actually exists here; row
scanning is the platform's problem, chain depth is yours.

## Changing a money formula on a live table

Formula edits recompute every row, so the only honest proof that one changed nothing it should not is
the numbers themselves:

1. Snapshot the affected totals to a file.
2. Make the change.
3. Diff. Identical is the pass.

And when a formula gains a new field, **test that the value IS the one you want, never that it
differs from it** — an empty cell reads as `""`, which differs from every option key, so the inverted
spelling silently zeroes every row written before the field existed.