Data modeling
Normalised, dimensional, or common data model — three shapes for three questions. Choosing the wrong one doesn't fail loudly; it just makes every query harder forever.
In one line
Three shapes, three questions. Normalised for writing safely. Dimensional for asking questions fast. A common data model for asking the same question across institutions. Most health data pain is a model built for one purpose being used for another.
Normalised (3NF) — the transactional shape
Every fact in one place. Patient details in patient, addresses in address, encounters in
encounter. Change an address once, everywhere is correct.
This is right for the system of record. It's what an EHR is, and it's what health data modelling demands, because writes must be correct and history must survive.
It is also miserable to query for analysis. "Diabetic patients with an HbA1c over 8 in the last year who missed an appointment" becomes eleven joins, and the query plan is a lottery.
Dimensional (star schema) — the analytical shape
Kimball's model: facts (the measurements — an encounter, a lab result, a claim line) at the centre, surrounded by dimensions (the context — patient, provider, date, location, diagnosis).
dim_patient
|
dim_date — fact_encounter — dim_provider
|
dim_diagnosis
Deliberately denormalised, deliberately redundant, and enormously easier to query: one join from the fact to each dimension you're filtering on. This is what a BI tool expects and why analytics built on a star schema feels fast while the same question against the EHR times out.
The health-specific subtlety: slowly changing dimensions. A patient moves house. Do you overwrite the address (and lose the fact that last year's admission was from a different district) or version it? For clinical analysis the answer is almost always version it — because the question "was she living in that district when she was admitted?" is exactly the kind of thing population health asks.
Common data model (OMOP) — the comparative shape
Neither of the above solves the problem OMOP exists for: my hospital's schema and yours are different, so our results are not comparable.
A CDM fixes the tables and the vocabularies, so the same analysis code runs in Pune and Boston and means the same thing. That's the trade: you accept someone else's shape and lose local nuance, and in exchange your study becomes reproducible across institutions.
The cost is real and worth naming: ETL into a CDM loses information. Local codes that don't
map, workflow subtleties that have no OMOP concept, free text. If you convert, keep the source —
and record the mapping equivalences honestly, because
narrower and unmatched are how you'll explain your numbers later.
FHIR is not a data model for analytics
A common and expensive misunderstanding. FHIR is an exchange format, not an analytical schema. It's deeply nested, reference-heavy, and optimised for moving one patient's data over a wire.
Storing FHIR resources as JSONB and querying them for population analytics works right up until it doesn't — and then you rebuild. The sane pattern is: FHIR at the boundary, Bulk Data to extract, then land it in a dimensional or CDM shape for analysis.
The decision
- Building the system of record → normalised, with versioning and bitemporal time.
- Building a dashboard your organisation reads → dimensional.
- Doing research meant to be compared or reproduced → OMOP.
- Moving data between organisations → FHIR, and then convert.
The failure mode is never picking wrong on day one. It's never picking at all — and ending up with an analytics layer that is the transactional schema with a view on top, which satisfies nobody and gets slower every year.