Health IT

EHR Systems Guide

Complete guide to Electronic Health Record (EHR) system architecture. Learn database design patterns, patient data models, clinical workflows, and interoperability standards for modern healthcare IT.

What is an EHR System?

An Electronic Health Record (EHR) is a digital version of a patient's complete medical history maintained by healthcare providers over time. Unlike EMRs (Electronic Medical Records) which are limited to a single organization, EHRs are designed to be shared across different healthcare settings and organizations.

Modern EHR systems integrate clinical, administrative, and financial data to support care coordination, population health management, and value-based care initiatives. They serve as the central data platform for healthcare delivery.

EMR vs EHR: Key Differences

EMR (Electronic Medical Record)

•Digital version of paper chart
•Used within ONE organization
•Limited sharing capabilities
•Focus on clinical documentation
•Provider-centric view

EHR (Electronic Health Record)

•Comprehensive health record
•Shared across MULTIPLE organizations
•Interoperability with HIE, labs, pharmacies
•Supports care coordination & analytics
•Patient-centric longitudinal view

Core EHR Database Schema

Production-grade PostgreSQL schema for EHR core modules:

-- ============================================
-- PATIENT DEMOGRAPHICS
-- ============================================
CREATE TABLE patient (
  id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  
  -- Identifiers
  mrn                 VARCHAR(50) UNIQUE NOT NULL,     -- Medical Record Number
  ssn_encrypted       BYTEA,                           -- Encrypted SSN
  
  -- Demographics
  first_name          VARCHAR(100) NOT NULL,
  middle_name         VARCHAR(100),
  last_name           VARCHAR(100) NOT NULL,
  maiden_name         VARCHAR(100),
  dob                 DATE NOT NULL,
  gender              VARCHAR(20),
  sex_at_birth        VARCHAR(20),
  
  -- Contact
  phone_primary       VARCHAR(20),
  phone_secondary     VARCHAR(20),
  email               VARCHAR(255),
  
  -- Address
  address_line1       VARCHAR(255),
  address_line2       VARCHAR(255),
  city                VARCHAR(100),
  state               VARCHAR(2),
  zip_code            VARCHAR(10),
  country             VARCHAR(3) DEFAULT 'USA',
  
  -- Status
  status              VARCHAR(20) DEFAULT 'active',    -- active, inactive, deceased
  deceased_date       DATE,
  
  -- Preferences
  preferred_language  VARCHAR(50),
  race                VARCHAR(100),
  ethnicity           VARCHAR(100),
  
  -- Metadata
  created_at          TIMESTAMP DEFAULT NOW(),
  updated_at          TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_patient_mrn ON patient(mrn);
CREATE INDEX idx_patient_name ON patient(last_name, first_name);
CREATE INDEX idx_patient_dob ON patient(dob);

-- ============================================
-- ENCOUNTERS (Visits)
-- ============================================
CREATE TABLE encounter (
  id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  
  -- Patient
  patient_id          UUID NOT NULL REFERENCES patient(id),
  
  -- Visit Details
  encounter_number    VARCHAR(50) UNIQUE NOT NULL,
  encounter_type      VARCHAR(100),                    -- inpatient, outpatient, emergency
  visit_type          VARCHAR(100),                    -- office visit, telehealth, etc.
  
  -- Location
  facility_id         UUID,
  department_id       UUID,
  room_number         VARCHAR(20),
  
  -- Provider
  attending_provider  UUID,                            -- Reference to provider table
  referring_provider  UUID,
  
  -- Timing
  admission_date      TIMESTAMP NOT NULL,
  discharge_date      TIMESTAMP,
  
  -- Status
  status              VARCHAR(50),                     -- arrived, in-progress, completed, cancelled
  
  -- Classification
  chief_complaint     TEXT,
  admit_reason        TEXT,
  discharge_disposition VARCHAR(100),
  
  -- Financial
  insurance_id        UUID,
  authorization_num   VARCHAR(100),
  
  created_at          TIMESTAMP DEFAULT NOW(),
  updated_at          TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_encounter_patient ON encounter(patient_id);
CREATE INDEX idx_encounter_date ON encounter(admission_date);
CREATE INDEX idx_encounter_status ON encounter(status);

-- ============================================
-- CLINICAL NOTES
-- ============================================
CREATE TABLE clinical_note (
  id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  
  encounter_id        UUID NOT NULL REFERENCES encounter(id),
  patient_id          UUID NOT NULL REFERENCES patient(id),
  
  -- Note Details
  note_type           VARCHAR(100) NOT NULL,           -- progress, H&P, discharge, consult
  note_title          VARCHAR(255),
  note_text           TEXT NOT NULL,
  
  -- Provider
  author_id           UUID NOT NULL,
  signed_by           UUID,
  signed_at           TIMESTAMP,
  
  -- Status
  status              VARCHAR(50) DEFAULT 'draft',     -- draft, signed, amended, deleted
  
  -- Metadata
  created_at          TIMESTAMP DEFAULT NOW(),
  updated_at          TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_note_encounter ON clinical_note(encounter_id);
CREATE INDEX idx_note_patient ON clinical_note(patient_id);
CREATE INDEX idx_note_type ON clinical_note(note_type);

-- ============================================
-- PROBLEM LIST (Diagnoses)
-- ============================================
CREATE TABLE problem (
  id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  
  patient_id          UUID NOT NULL REFERENCES patient(id),
  encounter_id        UUID REFERENCES encounter(id),
  
  -- Problem Details
  icd10_code          VARCHAR(10),
  snomed_code         VARCHAR(20),
  problem_description TEXT NOT NULL,
  
  -- Classification
  problem_type        VARCHAR(50),                     -- diagnosis, symptom, finding
  severity            VARCHAR(20),                     -- mild, moderate, severe
  
  -- Status
  status              VARCHAR(50) DEFAULT 'active',    -- active, resolved, chronic
  onset_date          DATE,
  resolved_date       DATE,
  
  -- Clinical
  is_chronic          BOOLEAN DEFAULT FALSE,
  
  created_at          TIMESTAMP DEFAULT NOW(),
  updated_at          TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_problem_patient ON problem(patient_id);
CREATE INDEX idx_problem_status ON problem(status);
CREATE INDEX idx_problem_icd10 ON problem(icd10_code);

-- ============================================
-- MEDICATIONS
-- ============================================
CREATE TABLE medication (
  id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  
  patient_id          UUID NOT NULL REFERENCES patient(id),
  encounter_id        UUID REFERENCES encounter(id),
  
  -- Drug Information
  rxcui               VARCHAR(20),                     -- RxNorm code
  ndc                 VARCHAR(20),
  drug_name           VARCHAR(500) NOT NULL,
  
  -- Dosage
  strength            VARCHAR(100),
  dose_form           VARCHAR(100),                    -- tablet, capsule, injection
  route               VARCHAR(50),                     -- oral, IV, topical
  frequency           VARCHAR(100),                    -- BID, TID, QID, PRN
  
  -- Instructions
  sig                 TEXT,                            -- Prescription instructions
  quantity            DECIMAL(10,2),
  refills             INTEGER,
  
  -- Dates
  start_date          DATE NOT NULL,
  end_date            DATE,
  
  -- Provider
  prescriber_id       UUID,
  prescriber_npi      VARCHAR(10),
  
  -- Status
  status              VARCHAR(50) DEFAULT 'active',    -- active, discontinued, completed
  
  created_at          TIMESTAMP DEFAULT NOW(),
  updated_at          TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_medication_patient ON medication(patient_id);
CREATE INDEX idx_medication_status ON medication(status);
CREATE INDEX idx_medication_rxcui ON medication(rxcui);

-- ============================================
-- VITAL SIGNS
-- ============================================
CREATE TABLE vital_signs (
  id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  
  patient_id          UUID NOT NULL REFERENCES patient(id),
  encounter_id        UUID REFERENCES encounter(id),
  
  -- Vital Measurements
  temperature         DECIMAL(5,2),                    -- Fahrenheit
  temperature_unit    VARCHAR(1) DEFAULT 'F',          -- F or C
  
  bp_systolic         INTEGER,                         -- mmHg
  bp_diastolic        INTEGER,                         -- mmHg
  
  heart_rate          INTEGER,                         -- bpm
  respiratory_rate    INTEGER,                         -- per minute
  
  oxygen_saturation   DECIMAL(5,2),                    -- SpO2 %
  oxygen_flow_rate    DECIMAL(5,2),                    -- L/min
  
  height              DECIMAL(6,2),                    -- inches
  weight              DECIMAL(6,2),                    -- pounds
  bmi                 DECIMAL(5,2),                    -- calculated
  
  -- Context
  measured_at         TIMESTAMP NOT NULL DEFAULT NOW(),
  measured_by         UUID,
  
  created_at          TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_vitals_patient ON vital_signs(patient_id);
CREATE INDEX idx_vitals_encounter ON vital_signs(encounter_id);
CREATE INDEX idx_vitals_measured ON vital_signs(measured_at);

-- ============================================
-- LAB RESULTS
-- ============================================
CREATE TABLE lab_result (
  id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  
  patient_id          UUID NOT NULL REFERENCES patient(id),
  encounter_id        UUID REFERENCES encounter(id),
  order_id            UUID,
  
  -- Test Details
  loinc_code          VARCHAR(10),
  test_name           VARCHAR(255) NOT NULL,
  
  -- Result
  result_value        VARCHAR(500),
  result_numeric      DECIMAL(18,6),
  result_unit         VARCHAR(50),
  
  -- Reference Range
  reference_low       DECIMAL(18,6),
  reference_high      DECIMAL(18,6),
  reference_text      VARCHAR(255),
  
  -- Interpretation
  abnormal_flag       VARCHAR(20),                     -- H, L, N, HH, LL
  status              VARCHAR(50),                     -- final, preliminary, corrected
  
  -- Timing
  collected_at        TIMESTAMP,
  resulted_at         TIMESTAMP,
  
  -- Provider
  ordering_provider   UUID,
  
  created_at          TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_lab_patient ON lab_result(patient_id);
CREATE INDEX idx_lab_loinc ON lab_result(loinc_code);
CREATE INDEX idx_lab_resulted ON lab_result(resulted_at);

EHR Core Modules

Patient Management

Demographics, registration, insurance, MPI (Master Patient Index), patient portal

Key tables: patient, patient_insurance, patient_contact

Scheduling & Registration

Appointments, provider calendars, patient check-in, encounter creation

Key tables: appointment, schedule, encounter

Clinical Documentation

Progress notes, H&P, discharge summaries, templates, clinical decision support

Key tables: clinical_note, problem, medication, allergy

Clinical Orders & Results

Lab orders, imaging, medications, procedures, result management

Key tables: order, lab_result, imaging_result, medication_order

Revenue Cycle Management

Charge capture, claims generation, billing, denial management, collections

Key tables: charge, claim, payment, adjustment

Common EHR Queries

Get Patient Summary

SELECT 
  p.mrn,
  p.first_name || ' ' || p.last_name as patient_name,
  p.dob,
  p.gender,
  COUNT(DISTINCT e.id) as total_visits,
  COUNT(DISTINCT pr.id) as active_problems,
  COUNT(DISTINCT m.id) as active_medications
FROM patient p
LEFT JOIN encounter e ON p.id = e.patient_id
LEFT JOIN problem pr ON p.id = pr.patient_id AND pr.status = 'active'
LEFT JOIN medication m ON p.id = m.patient_id AND m.status = 'active'
WHERE p.mrn = 'MRN12345'
GROUP BY p.id, p.mrn, p.first_name, p.last_name, p.dob, p.gender;

Find Patients with Diabetes Not on Metformin

SELECT DISTINCT
  p.mrn,
  p.first_name || ' ' || p.last_name as patient_name,
  pr.problem_description,
  pr.onset_date
FROM patient p
JOIN problem pr ON p.id = pr.patient_id
WHERE pr.icd10_code LIKE 'E11%'  -- Type 2 Diabetes
  AND pr.status = 'active'
  AND NOT EXISTS (
    SELECT 1 FROM medication m
    WHERE m.patient_id = p.id
      AND m.drug_name ILIKE '%metformin%'
      AND m.status = 'active'
  );

Related Healthcare Guides

HIPAA Compliance →HL7 FHIR →Healthcare Analytics →Healthcare Glossary →

mdatool.