UUIDs as join keys, versioned references, and the minimal transform list — Apr 14, 2023
UUIDs as native join keys
Nikolai's proposal: store every ID and reference as a native uuid rather than a string — roughly half the disk space in Postgres, and native joins instead of parsing Patient/12345 apart at query time.
UUID v5 hashes any string deterministically and has a notion of namespace, so IDs from different sources can be merged into one database without collisions. The worry that this constrains what upstream servers may store was answered: nothing changes upstream, you synthesise the column on load.
Hashing codings and identifiers too — an indexing trick
Nikolai had gone further and hashed codings and identifiers into UUIDs too, so half a resource becomes one searchable bag of UUIDs.
The objection: plenty of use cases need to see the actual codes and do value-set queries against them, and an observation is a bag of codes throws away the data model that scaffolds the vocabulary. It survived as an indexing trick, not a spec proposal.
Versioned references — out of scope, explicitly
Should a reference naming a specific version work? A questionnaire answer pointing at the demographics as they stood when it was filled in — you don't want to retroactively pretend a different address was given.
The case against: joins nearly always want the current patient, and honouring versions means rewriting references whenever anything updates.
Agreed to declare it out of scope — but to say so explicitly rather than let it be an accident.
The minimal transformation list
Converged on three, plus identifiers:
- IDs — parsed out of references
- References — split into raw ID parts
- Date-times of varying precision — the hard one, no concrete proposal yet
- Identifiers — added on the argument that you often need to join an Observation's subject to a Patient by identifier rather than by reference
The end-user note: someone at a health plan searches by the member's ID number, not by whatever it was hashed into.
Dates stayed the hard one — nobody had a concrete proposal, only the idea that every date becomes a start and an end at the appropriate precision.
Contained resources — no good answer
You can't generate a columnar schema for contained resources in general. In practice people either rely on the data being regular enough to derive one, or dump them into a string and never look inside it again.
The tidiest observation: a contained resource is fully identified by its container's ID plus its own, so any consistent combination of the two will do as a key.