Healthcare Data Model

Appointments

One row per scheduled appointment, capturing how a visit was booked and whether the patient actually showed up. Each row links a patient, a provider and a department, records the scheduled date and time, how far in advance it was booked (lead time), how long the patient waited past that time, whether they were a no-show, and the appointment's final status. This is the front door of the clinical and revenue cycle — every encounter and claim downstream starts as a booked appointment here.

Fields

Relationships

Bed Census (daily)

One row per department per day, tracking inpatient bed capacity and patient flow over time. Each row records how many beds were staffed and how many were occupied that day, alongside the day's admissions and discharges — the numbers that drive occupancy rate and reveal whether a department is filling up or emptying out. This is the operational pulse of capacity management across the health system.

Fields

Relationships

Claims

One row per claim submitted to a payer for a clinical encounter — the revenue-cycle record that turns care into cash. Each claim tracks the billed, payer-allowed and actually paid amounts, its submission and payment dates, current status, the denial reason when one applies, and the number of days it has sat in accounts receivable. This is the mart that answers how well the health system gets paid for the care it delivers, and how quickly.

Fields

Relationships

Department

Reference list of the clinical departments that make up the health system, each with the clinical specialty it serves and its staffed-bed capacity. Departments are the organizational unit behind appointments, provider assignments and daily bed census — staffed beds is the denominator for every occupancy and capacity calculation in the model.

Fields

Encounters

One row per clinical encounter — the actual visit that followed a scheduled appointment. Each encounter records its type (outpatient, inpatient or emergency department), the primary diagnosis, admission and discharge timestamps, the resulting length of stay, and whether it was an unplanned readmission within 30 days. This is where the clinical story lives: what patients are being treated for, how long they stay, and how often they come back unexpectedly.

Fields

Relationships

Patient

One row per patient, the demographic and coverage anchor for every appointment, encounter and claim in the system. Each patient carries a birth year and gender for age-banded analysis, a postal code for geographic reach, an insurance type (commercial, Medicare, Medicaid or self-pay) that determines how their care gets billed, a risk-stratification tier used by care management to flag patients who need closer follow-up, and the date they first registered with the system.

Fields

Payer

Reference list of the insurance payers and plans that reimburse the health system for care, each identified by name and plan type — HMO, PPO, EPO or government (Medicare/Medicaid). Every claim is billed to one of these payers, so this mart is the lookup behind any payer-mix or reimbursement analysis.

Fields

Provider

One row per clinician, the roster behind every appointment and encounter. Each provider is identified by name, clinical specialty, home department, and National Provider Identifier (NPI) — the standard identifier used to credential and bill for a clinician's services. This is the lens for looking at care delivery by clinician: who is seeing patients, in what specialty, and out of which department.

Fields