Hospital ADT / Patient Flow data model
Normalized (3NF) hospital ADT / patient-flow model. The census hierarchy (facility -> unit -> bed) anchors occupancy, while the admission encounter spawns transfer (A02), bed-assignment, and discharge (A03) child events that follow HL7 v2 ADT triggers. Coverage and chargemaster charges support revenue capture. PHI is classified phi and masked.
Operational hospital admission-discharge-transfer (ADT) and patient-flow data: patients, facilities, nursing units/wards, beds, inpatient admissions, bed/unit transfers, bed-occupancy intervals, discharges, attending providers, insurance coverage, and chargemaster charges. The event model follows HL7 v2 ADT trigger events. 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.
- 11
- tables
- 129
- columns
- 18
- relationships
- 11
- 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
HL7 v2 ADT trigger events as event tables
Patient flow is driven by HL7 v2 ADT messages (A01 admit, A02 transfer, A03 discharge); modeling admission, transfer, and discharge as separate event tables mirrors the message stream and preserves the true event grain.
Facility -> unit -> bed census hierarchy
Bed management requires a normalized location hierarchy; separating facility, unit, and bed avoids repeating location attributes and lets occupancy roll up cleanly to unit and facility census.
Bed-assignment intervals separate from transfer events
A transfer is an instantaneous event, but occupancy is an interval; an explicit bed_assignment table with start/end timestamps supports accurate bed-day and length-of-stay calculations.
PHI classification and masking for HIPAA
Patient identifiers, emergency contacts, and payer member IDs are classified phi and masked (SSN fully) so the governance report enforces HIPAA minimum-necessary controls across the encounter.
Tables in the Hospital ADT / Patient Flow 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 (11)
facility
Grain: one row per hospital facility / campus
| Column | Type | Keys |
|---|---|---|
| facility_key | bigint | PK |
| facility_id | varchar | BK |
| name | varchar | |
| npi | varchar | |
| facility_type | varchar | |
| bed_capacity | integer | |
| phone | varchar | |
| address_line1 | varchar | |
| city | varchar | |
| state | varchar | |
| postal_code | varchar | |
| is_active | boolean |
unit
Grain: one row per nursing unit / ward
| Column | Type | Keys |
|---|---|---|
| unit_key | bigint | PK |
| facility_key | bigint | |
| unit_id | varchar | BK |
| name | varchar | |
| unit_type | varchar | |
| floor | varchar | |
| licensed_beds | smallint | |
| specialty | varchar | |
| is_active | boolean |
bed
Grain: one row per physical bed
| Column | Type | Keys |
|---|---|---|
| bed_key | bigint | PK |
| unit_key | bigint | |
| bed_id | varchar | BK |
| room_number | varchar | |
| bed_label | varchar | |
| bed_type | varchar | |
| is_isolation | boolean | |
| status | varchar | |
| is_active | boolean |
provider
Grain: one row per attending / treating provider
| Column | Type | Keys |
|---|---|---|
| provider_key | bigint | PK |
| facility_key | bigint | |
| provider_id | varchar | BK |
| npi | varchar | |
| first_name | varchar | PII |
| last_name | varchar | PII |
| specialty | varchar | |
| role | varchar | |
| license_number | varchar | |
| pager | varchar | |
| is_active | boolean |
patient
Grain: one row per patient (master patient index)
Master patient index; 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 | |
| ethnicity | varchar | |
| marital_status | varchar | |
| phone | varchar | PII |
| address_line1 | varchar | PII |
| city | varchar | |
| state | varchar | |
| postal_code | varchar | |
| emergency_contact_name | varchar | PII |
| emergency_contact_phone | varchar | PII |
| is_deceased | boolean | |
| is_active | boolean |
admission
Grain: one row per inpatient admission / encounter (HL7 ADT A01)
The inpatient encounter; transfers/discharges are child events.
| Column | Type | Keys |
|---|---|---|
| admission_key | bigint | PK |
| patient_key | bigint | |
| facility_key | bigint | |
| provider_key | bigint | |
| admission_id | varchar | BK |
| account_number | varchar | |
| patient_class | varchar | |
| admission_type | varchar | |
| admission_source | varchar | |
| admitting_diagnosis | varchar | |
| drg_code | varchar | |
| expected_length_of_stay | smallint | |
| status | varchar | |
| admitted_datetime | timestamp |
transfer
Grain: one row per bed/unit transfer event (HL7 ADT A02)
Movement between beds/units within an admission.
| Column | Type | Keys |
|---|---|---|
| transfer_key | bigint | PK |
| admission_key | bigint | |
| from_bed_key | bigint | |
| to_bed_key | bigint | |
| transfer_reason | varchar | |
| level_of_care_change | varchar | |
| ordered_by_provider_key | bigint | |
| sequence_number | smallint | |
| transfer_datetime | timestamp |
bed_assignment
Grain: one row per occupancy interval of a bed by an admission
Census occupancy intervals; supports bed-day calculations.
| Column | Type | Keys |
|---|---|---|
| bed_assignment_key | bigint | PK |
| admission_key | bigint | |
| bed_key | bigint | |
| transfer_key | bigint | |
| assignment_type | varchar | |
| start_datetime | timestamp | |
| end_datetime | timestamp | |
| is_current | boolean |
discharge
Grain: one row per discharge event (HL7 ADT A03)
| Column | Type | Keys |
|---|---|---|
| discharge_key | bigint | PK |
| admission_key | bigint | |
| provider_key | bigint | |
| discharge_disposition | varchar | |
| discharge_location | varchar | |
| principal_diagnosis | varchar | |
| discharge_drg_code | varchar | |
| length_of_stay_days | smallint | |
| readmission_risk_score | decimal | |
| discharge_datetime | timestamp |
insurance_coverage
Grain: one row per payer coverage on an admission
| Column | Type | Keys |
|---|---|---|
| insurance_coverage_key | bigint | PK |
| admission_key | bigint | |
| patient_key | bigint | |
| payer_name | varchar | |
| payer_type | varchar | |
| coverage_order | varchar | |
| member_id | varchar | PII |
| group_number | varchar | |
| plan_name | varchar | |
| authorization_number | varchar | |
| verification_status | varchar | |
| effective_date | date | |
| termination_date | date |
charge
Grain: one row per posted charge during the stay
Chargemaster postings (CDM) for the encounter.
| Column | Type | Keys |
|---|---|---|
| charge_key | bigint | PK |
| admission_key | bigint | |
| patient_key | bigint | |
| unit_key | bigint | |
| charge_id | varchar | BK |
| charge_code | varchar | |
| cpt_hcpcs_code | varchar | |
| revenue_code | varchar | |
| description | varchar | |
| quantity | decimal | |
| unit_price | decimal | |
| total_charge | decimal | |
| department | varchar | |
| service_date | date | |
| posted_datetime | timestamp |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- unit.facility_key → facility (many-to-one)
- bed.unit_key → unit (many-to-one)
- provider.facility_key → facility (many-to-one)
- admission.patient_key → patient (many-to-one)
- admission.facility_key → facility (many-to-one)
- admission.provider_key → provider (many-to-one)
- transfer.admission_key → admission (many-to-one)
- transfer.to_bed_key → bed (many-to-one)
- bed_assignment.admission_key → admission (many-to-one)
- bed_assignment.bed_key → bed (many-to-one)
- bed_assignment.transfer_key → transfer (many-to-one)
- discharge.admission_key → admission (many-to-one)
- discharge.provider_key → provider (many-to-one)
- insurance_coverage.admission_key → admission (many-to-one)
- insurance_coverage.patient_key → patient (many-to-one)
- charge.admission_key → admission (many-to-one)
- charge.patient_key → patient (many-to-one)
- charge.unit_key → unit (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.