Laboratory Information System data model
Normalized (3NF) laboratory information system (LIS). A test compendium (single tests and panels) keyed by LOINC drives orders; specimens carry chain-of-custody, results are discrete per analyte with applied reference ranges and abnormal flags, and analyzers track QC runs with Westgard rule evaluation for CLIA/CAP. PHI is classified phi and masked.
Operational clinical-laboratory data: patients, ordering providers, specimens, test orders and panels, individual analyte results, analyzers, reference ranges, and quality-control runs. Orderable tests and result analytes are coded with LOINC, specimens follow HL7 specimen terminology, and QC supports CLIA/CAP. Patient PHI is classified and masked for HIPAA. It is a normalized (3NF) relational model that mirrors the source system, suited to operational databases and to landing source data faithfully.
- 12
- tables
- 137
- columns
- 18
- relationships
- 13
- PII columns
Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: PostgreSQL). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
LOINC coding for tests and results
Orderable tests, panels, and result analytes are coded with LOINC so results are universally identifiable and comparable across instruments and institutions when exchanged via HL7.
Order/specimen/result separated by grain
One order can yield multiple specimens and many discrete analyte results; modeling them as distinct tables preserves chain-of-custody and one-row-per-analyte resulting grain.
Reference ranges as their own table
Normal and critical limits vary by sex and age and change over time; keeping them in a dedicated, effective-dated table lets results link to the exact interval applied at resulting time.
Quality control modeled for CLIA/CAP
Regulatory accreditation requires documented QC; storing QC runs with control lots, target mean/SD, and Westgard-rule violations supports CLIA and CAP inspection and result release decisions.
PHI classification and masking for HIPAA
Patient identifiers and staff names (collected/verified/performed by) are classified phi/confidential and masked (SSN fully) so the governance report enforces HIPAA controls.
Tables in the Laboratory Information System model
Every table with its grain, columns, types and keys. PK marks a primary key, BK a business key and PII a column classified as personal data, which gets a masking strategy by default.
Tables (12)
laboratory
Grain: one row per performing laboratory / facility
| Column | Type | Keys |
|---|---|---|
| laboratory_key | bigint | PK |
| laboratory_id | varchar | BK |
| name | varchar | |
| clia_number | varchar | |
| cap_number | varchar | |
| lab_type | varchar | |
| director_name | varchar | PII |
| phone | varchar | |
| address_line1 | varchar | |
| city | varchar | |
| state | varchar | |
| postal_code | varchar | |
| is_active | boolean |
provider
Grain: one row per ordering provider
| Column | Type | Keys |
|---|---|---|
| provider_key | bigint | PK |
| provider_id | varchar | BK |
| npi | varchar | |
| first_name | varchar | PII |
| last_name | varchar | PII |
| specialty | varchar | |
| organization_name | varchar | |
| phone | varchar | PII |
| fax | varchar | |
| is_active | boolean |
patient
Grain: one row per patient
All PHI masked for HIPAA.
| Column | Type | Keys |
|---|---|---|
| patient_key | bigint | PK |
| mrn | varchar | BK |
| ssn | varchar | PII |
| first_name | varchar | PII |
| last_name | varchar | PII |
| date_of_birth | date | PII |
| sex | varchar | |
| race | varchar | |
| phone | varchar | PII |
| address_line1 | varchar | PII |
| city | varchar | |
| state | varchar | |
| postal_code | varchar | |
| is_active | boolean |
test_definition
Grain: one row per orderable/resultable test (LOINC)
Compendium of orderable tests keyed by LOINC.
| Column | Type | Keys |
|---|---|---|
| test_definition_key | bigint | PK |
| test_code | varchar | BK |
| loinc_code | varchar | |
| test_name | varchar | |
| category | varchar | |
| specimen_requirement | varchar | |
| result_data_type | varchar | |
| default_unit | varchar | |
| methodology | varchar | |
| turnaround_hours | smallint | |
| is_active | boolean |
test_panel
Grain: one row per orderable panel / battery
| Column | Type | Keys |
|---|---|---|
| test_panel_key | bigint | PK |
| panel_code | varchar | BK |
| loinc_code | varchar | |
| panel_name | varchar | |
| category | varchar | |
| specimen_requirement | varchar | |
| is_active | boolean |
panel_member
Grain: one row per test within a panel (junction)
| Column | Type | Keys |
|---|---|---|
| panel_member_key | bigint | PK |
| test_panel_key | bigint | |
| test_definition_key | bigint | |
| display_order | smallint | |
| is_reflex | boolean |
analyzer
Grain: one row per laboratory instrument / analyzer
| Column | Type | Keys |
|---|---|---|
| analyzer_key | bigint | PK |
| laboratory_key | bigint | |
| analyzer_id | varchar | BK |
| name | varchar | |
| manufacturer | varchar | |
| model | varchar | |
| serial_number | varchar | |
| department | varchar | |
| calibration_due_date | date | |
| status | varchar | |
| installed_date | date |
reference_range
Grain: one row per reference interval for a test
Age/sex-specific normal and critical limits.
| Column | Type | Keys |
|---|---|---|
| reference_range_key | bigint | PK |
| test_definition_key | bigint | |
| sex | varchar | |
| age_low_years | decimal | |
| age_high_years | decimal | |
| range_low | decimal | |
| range_high | decimal | |
| critical_low | decimal | |
| critical_high | decimal | |
| unit | varchar | |
| effective_date | date | |
| is_active | boolean |
test_order
Grain: one row per ordered test/panel for a patient
| Column | Type | Keys |
|---|---|---|
| test_order_key | bigint | PK |
| patient_key | bigint | |
| provider_key | bigint | |
| laboratory_key | bigint | |
| test_definition_key | bigint | |
| test_panel_key | bigint | |
| order_id | varchar | BK |
| priority | varchar | |
| clinical_info | varchar | |
| fasting_status | varchar | |
| status | varchar | |
| ordered_datetime | timestamp |
specimen
Grain: one row per collected specimen (HL7 specimen)
Chain-of-custody specimen tracking.
| Column | Type | Keys |
|---|---|---|
| specimen_key | bigint | PK |
| test_order_key | bigint | |
| patient_key | bigint | |
| specimen_id | varchar | BK |
| specimen_type | varchar | |
| container_type | varchar | |
| body_site | varchar | |
| volume_ml | decimal | |
| collection_method | varchar | |
| condition | varchar | |
| collected_datetime | timestamp | |
| received_datetime | timestamp | |
| collected_by | varchar | PII |
result
Grain: one row per resulted analyte (LOINC)
Discrete analyte results keyed by LOINC.
| Column | Type | Keys |
|---|---|---|
| result_key | bigint | PK |
| test_order_key | bigint | |
| test_definition_key | bigint | |
| specimen_key | bigint | |
| analyzer_key | bigint | |
| reference_range_key | bigint | |
| loinc_code | varchar | |
| value_numeric | decimal | |
| value_text | varchar | |
| unit | varchar | |
| abnormal_flag | varchar | |
| interpretation | varchar | |
| status | varchar | |
| verified_by | varchar | PII |
| resulted_datetime | timestamp | |
| verified_datetime | timestamp |
qc_run
Grain: one row per quality-control run on an analyzer
CLIA/CAP quality-control runs with Westgard evaluation.
| Column | Type | Keys |
|---|---|---|
| qc_run_key | bigint | PK |
| analyzer_key | bigint | |
| test_definition_key | bigint | |
| qc_run_id | varchar | BK |
| control_lot_number | varchar | |
| control_level | varchar | |
| measured_value | decimal | |
| target_mean | decimal | |
| target_sd | decimal | |
| westgard_rule_violated | varchar | |
| result_status | varchar | |
| run_datetime | timestamp | |
| performed_by | varchar | PII |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- panel_member.test_panel_key → test_panel (many-to-one)
- panel_member.test_definition_key → test_definition (many-to-one)
- analyzer.laboratory_key → laboratory (many-to-one)
- reference_range.test_definition_key → test_definition (many-to-one)
- test_order.patient_key → patient (many-to-one)
- test_order.provider_key → provider (many-to-one)
- test_order.laboratory_key → laboratory (many-to-one)
- test_order.test_definition_key → test_definition (many-to-one)
- test_order.test_panel_key → test_panel (many-to-one)
- specimen.test_order_key → test_order (many-to-one)
- specimen.patient_key → patient (many-to-one)
- result.test_order_key → test_order (many-to-one)
- result.test_definition_key → test_definition (many-to-one)
- result.specimen_key → specimen (many-to-one)
- result.analyzer_key → analyzer (many-to-one)
- result.reference_range_key → reference_range (many-to-one)
- qc_run.analyzer_key → analyzer (many-to-one)
- qc_run.test_definition_key → test_definition (many-to-one)
Related templates
All templates →From requirements to AI-ready context, in one platform.
Describe your business in a conversation, start from a template or connect your own database. Your first model is ready in minutes.