Mapping a schema

Relations, not tables

Linkr’s pages never query person or patients directly. They query relations with fixed names — linkr_patient, linkr_visit, linkr_event_measurements… — that always return the same columns, whatever the database’s model: patient_id, birth_date, gender for patients; visit_id, start_datetime, end_datetime for hospitalizations. That list of columns, with their types, is the relation’s contract.

The mapping is how each relation is produced from your tables. An OMOP database and a MIMIC database produce the same relations by different routes — and the same dashboard works on both.

The Mapping tab

On a schema’s page, the Mapping tab opens on the Source view, the one with the forms; the Diagram view places the relations on the table diagram. It is read-only until you click Edit, top right; Save — or Cmd/Ctrl + S — commits.

The sub-tabs sort the relations by clinical subject, each with its colour, the same as in the diagram. All shows them one after the other.

Patients, Hospitalizations, Unit stays and Clinical notes are single. Dictionaries, events and drugs are added in edit mode — Add dictionary, Add event relation, Add drug relation — and named: their Label, or their Key for a dictionary, gives the relation its name (linkr_event_measurements). A block’s bin removes it, after a confirmation — except the Patients block, which cannot be removed.

Filling a relation through the form

The form fits whenever one table carries the relation on its own. An empty block offers Map from a table; a filled block has three parts.

• Table — the source schema and table. Both fields suggest the tables the DDL knows, without ever forcing the value: a half-described model stays editable.
• Filter — a SQL condition on that table’s columns, to keep only part of it: category = 'Vital signs'. This is what lets you draw several event relations from one table.
• Columns — one row per contract column. Each is filled by a Column of the table, or by a Constant — a fixed value, like 'ICU' for a stay type. Required columns carry an asterisk; a column left empty comes back empty (NULL).

Outside edit mode, only the filled columns show. Some carry an ⓘ explaining what Linkr derives from them: birth_year is the fallback when birth_date is not mapped, and gender is computed on its own from gender_source_value.

Gender values

The Patients block also asks for the Gender values: the raw value of gender_source_value meaning male, the one meaning female, and optionally the one meaning unknown — M and F in OMOP, 1 and 2 elsewhere. Linkr fills gender with them, which the statistics and the sex criteria use.

An event relation’s dictionary

Every event or drug relation states which Dictionary turns its codes into names: Default — the first dictionary of the Concepts sub-tab —, a specific dictionary, or None (concepts named inline).

A dictionary also accepts, beyond its contract, extra columns — Add extra column —, which take the extra_ prefix.

Drugs

Drugs have their own sub-tab because they have their own columns. Each relation has a Kind — Administrations, what was given, or Prescriptions, what was prescribed — and its contract adds the dose columns to the event ones: quantity, amount, rate, concentration and duration, each with its unit, and is_continuous for a continuous infusion.

The event columns — value, unit — are derived from the dose columns when you do not map them. A drug relation therefore works wherever an event relation does.

Writing a relation in SQL

The form reads one table only. Everything else is written in SQL: a date of death kept in another table, a code that is only found by joining terminology and code, or an entity-attribute-value warehouse — where a single measurement is spread over several rows that have to be put back together.

Each block’s SQL button — or Write SQL on an empty block — opens the relation’s window. It opens on the SQL generated from the form; as soon as you change it, it is marked Modified, and Reset goes back to the generated SQL. The query must be a single SELECT that names its columns exactly as the contract does (AS patient_id).

1-- Values and units: two rows per measurement, matched by document
2SELECT
3  o.pat_id     AS patient_id,
4  o.code_id    AS concept_id,
5  o.ts         AS start_datetime,
6  o.sejour_id  AS visit_id,
7  o.val        AS value_number,
8  u.val        AS unit,
9  o.doc_id
10FROM obs o
11LEFT JOIN obs u
12  ON u.doc_id = o.doc_id AND u.attr = 'UNITE'
13WHERE o.attr = 'VALEUR'

Three tabs:

• SQL — the editor, and the Contract on the right: the columns to return, the required ones in bold, each with its expected type. Cmd/Ctrl + S saves, and runs the check again.
• Contract check — on the chosen database, Check describes what the query returns, without reading the data. Every contract column is filled, required, missing, of the wrong type, or not returned (NULL); columns outside the contract are ignored. A column of the wrong type is fixed with a CAST in the query.
• Preview — Run shows the first 100 rows the relation returns.

Once the SQL is saved, the block reads Defined in SQL, followed by the columns the last check found filled. Those columns are recorded with the query: that is how the rest of the app knows, for instance, whether an age criterion or a value in the timeline is available. Until a check has run, the block says so.

The SQL window saves straight away, without going through Edit, as long as you may edit the schema.

One last warning from the check is worth knowing: a window function over the whole relation (OVER without PARTITION BY) stops Linkr from filtering by patient upstream, and every patient page then scans the whole table. Use a source key as the id instead, or partition the window by patient.

One schema, several databases

A database copies the schema at the moment it is attached — the DDL as well as the mapping — and records which schema, and which version, its copy comes from. It does not point at it.

That is what makes a database robust: it keeps working if the schema is edited, deleted, or was never installed on the instance it lands on.

And it is what lets it have its own quirks. A database can override a relation of its copy: redefine it for itself only — for instance because its measurements are stored differently —, while the schema and the other databases stay untouched. In the database’s Mapping tab, such a relation carries the Overridden badge, and the SQL window checks and previews on the database directly. See A database’s mapping.

A few limits

• A column that does not exist raises no error. A typo in a column name does not break the relation: that column simply comes back empty. The contract check or the preview reveals it.
• A preset’s joins stay read-only in the form. A relation installed with a join — OMOP’s date of death, read from the death table — shows its join greyed out: the table, the filter and the plain columns stay editable, the join itself is changed in SQL.
• The check needs a database. With no database installed from the schema, a SQL relation saves but cannot be verified.

Going further

• Schemas — what a schema is, and its two halves.
• Getting and exploring a schema — the catalog, importing, the DDL and the diagram.
• A database’s mapping — overriding a relation, and following the schema’s updates.
• Concepts — what the dictionaries make possible.
• Concept mapping — aligning your local codes to a standard terminology.