Electronic health record (EHR) schema template

Tables in this data model

• patients — 5 columns
• insurance_policies — 5 columns
• providers — 4 columns
• encounters — 6 columns
• vitals — 6 columns
• diagnoses — 4 columns
• prescriptions — 5 columns
• lab_orders — 5 columns
• allergies — 4 columns

How to use this ER diagram template

1. Click Open in the editor. The ER diagram opens on a new page in FlowDiagram — nothing is uploaded and there is no sign-up.
2. Rename, add or delete shapes and drag them where you want; connectors stay attached as things move.
3. Drop in vendor logos from the library — 1,160 of them, including every AWS, Azure and Google Cloud service icon — or restyle it with the colour and theme controls.
4. Prefer text? Edit the SQL DDL below and paste it in through ☰ → Import / export SQL; the finished model exports back to DDL.
5. Export to PNG, SVG or PDF, or press Ctrl+L for a link that carries the whole diagram.

SQL DDL source

The whole template is the text below. Paste it into FlowDiagram, keep it in Git next to the code it describes, or hand it to an AI assistant to extend.

CREATE TABLE patients (
  patient_id    INTEGER      NOT NULL PRIMARY KEY,
  mrn           VARCHAR(20)  NOT NULL UNIQUE,
  full_name     VARCHAR(150) NOT NULL,
  date_of_birth DATE         NOT NULL,
  sex           CHAR(1)
);

CREATE TABLE insurance_policies (
  policy_id   INTEGER     NOT NULL PRIMARY KEY,
  patient_id  INTEGER     NOT NULL REFERENCES patients(patient_id),
  payer_name  VARCHAR(120) NOT NULL,
  member_no   VARCHAR(40) NOT NULL,
  valid_to    DATE
);

CREATE TABLE providers (
  provider_id INTEGER      NOT NULL PRIMARY KEY,
  full_name   VARCHAR(150) NOT NULL,
  specialty   VARCHAR(80),
  npi         VARCHAR(10)  UNIQUE
);

CREATE TABLE encounters (
  encounter_id INTEGER     NOT NULL PRIMARY KEY,
  patient_id   INTEGER     NOT NULL REFERENCES patients(patient_id),
  provider_id  INTEGER     NOT NULL REFERENCES providers(provider_id),
  encounter_at TIMESTAMP   NOT NULL,
  setting      VARCHAR(20) NOT NULL,
  reason       VARCHAR(255)
);

CREATE TABLE vitals (
  vital_id     INTEGER      NOT NULL PRIMARY KEY,
  encounter_id INTEGER      NOT NULL REFERENCES encounters(encounter_id),
  bp_systolic  INTEGER,
  bp_diastolic INTEGER,
  pulse        INTEGER,
  temp_c       DECIMAL(4,1)
);

CREATE TABLE diagnoses (
  diagnosis_id INTEGER     NOT NULL PRIMARY KEY,
  encounter_id INTEGER     NOT NULL REFERENCES encounters(encounter_id),
  icd10_code   VARCHAR(10) NOT NULL,
  is_primary   BOOLEAN
);

CREATE TABLE prescriptions (
  prescription_id INTEGER      NOT NULL PRIMARY KEY,
  encounter_id    INTEGER      NOT NULL REFERENCES encounters(encounter_id),
  medication      VARCHAR(150) NOT NULL,
  dosage          VARCHAR(60),
  refills         INTEGER
);

CREATE TABLE lab_orders (
  lab_order_id INTEGER      NOT NULL PRIMARY KEY,
  encounter_id INTEGER      NOT NULL REFERENCES encounters(encounter_id),
  test_code    VARCHAR(20)  NOT NULL,
  result_value VARCHAR(60),
  resulted_at  TIMESTAMP
);

CREATE TABLE allergies (
  allergy_id INTEGER      NOT NULL PRIMARY KEY,
  patient_id INTEGER      NOT NULL REFERENCES patients(patient_id),
  substance  VARCHAR(100) NOT NULL,
  severity   VARCHAR(20)
);

Related: ER diagram from SQL · All healthcare & clinical templates · All 521 templates