# Hospital management system data model

> An example hospital management data model with ER diagram — patients, doctors, departments, appointments, admissions, wards and beds, diagnoses, prescriptions, invoices and insurance — with a data dictionary and SQL DDL.

Source: https://entitymodel.com/docs/examples/hospital-management/

This example models the core records of a hospital: patients and their insurance, doctors and departments, outpatient appointments, inpatient admissions to wards and beds, diagnoses, prescriptions and invoices.

## Design notes

- **Outpatient and inpatient care are separate.** An `appointment` is a scheduled visit; an `admission` is a stay with a bed, an attending doctor and admission and discharge times.
- **Diagnoses can come from either.** `diagnosis` links to the patient and, optionally, to the admission or the appointment where it was made. Codes use ICD-10.
- **Medications are a formulary.** `prescription` links a patient, a prescribing doctor and a medication, with dosage and dates.
- **Most patient data is PII or health data**, and is flagged so it can be masked, encrypted or excluded from analytics. Real systems also need audit trails and consent records.
- **Specialty and blood type are reference lists** with their values.

This is a teaching example, not a clinical system: real hospital data follows standards such as HL7 FHIR and local regulations (for example HIPAA in the United States).

## Diagram

Hospital Management System: 12 entities, 2 reference lists and 17 relationships. Each box is an entity with its attributes (PK marks the primary key); solid lines are relationships in crow's foot notation, dashed lines point to reference lists.

<!-- diagram -->

**[Open this model in EntityModel.com](https://entitymodel.com/#/share/v1.1Rtrb-O48a8Q6sezc5ITJ1l_kx_ZGNjYgePttVcsBEaibWJlUkdSybmL_e8FqRcpU47tKG0PyAeLI82D8-BwZvLDWVG2hcIZOIgILHZbGqEYMaeTA4IXxDimxBl4HYfTlIXIGTgbIRI--PVX7ZuLkG6djkPgVr1AeYIFjIMtJHCNtogIp-MILGIJvc-h4KGEgqcdF0hioGwNCf43FIqqM1EkHgoSAP0Jt0mMuNNxIsRDhpP8RR8URAcggQIjIngHRDQUlHEASQQilEAmthkAJgnFpHyKtphLOTkQFLxCFmWfPKNIIsFwTShHvAMSVhHNX8FxjMn6wuk45V453oXrdByYig1l-0I4HYcLKFLuDByYJIy-oMjpOCFDUKAoUOroub3rrud13cul6w7U34Xrur87HSdNoqPeU9rBiDuDf_0oFJPvjNw9zJMY7oIc8FgCMqUGYpfIdS4giSBT_NGYsiCkkVz_2-Xk2r-9s6ghQYxTAl43FDAUIvyCOAghQxfgnsYRz-EwVru3QTAWGxBBATuAU7ClXAAoBMPPqUAcQIbA43Qqd7datQgUYMlhDJ9RXAkDpmNpdZkgw-nn6WzpdBzMg4ThLWS74DvaOQPBUtRxGPojxQxFxXNuZ87AuXTdq77n_OyUNLcowiGMA4ZCyqKApNtn5TIF-YcMDhYKDmYFPOfkabmYzj47-zRTgv9IUfGUYFz8NLf4YTGT-1Fx-LCYdd19NldpHGfqrVi7S-MYzLK1OjuYB_lr9i3RGKpojyHBKAZDRul3rlOXNhrQVfCMmdhoHIyhQGC-AsN8Pedi7C8nli2x0vQ-3fS7bq_rXekEOfpTI_OknuoiWtE96FiSDSX6hj3mz0dh-sUD_X4fuN4nV8f5HFMaKYfKvKfCPpQQsNwlCIwySE5nMbmbLCaz0SSYzJbT5T_V1qwQQ0QG4B-5kxq4ZexMRZKKoPQUC-2fDazPf9E5xoSnDJIQBTI64Qgx08GmBRw85nDD1zJHq5Df2nFvkXSMJswPCmrgPbz7__h8f3XluW7Pc35-kyGWpaFQosuI8a3jQIZgFR1May3Phv3QONZhR0bHG78_up1YomMYY6KCQ0WxA3gabgDkYARZhGlM17vmgFd9Z25cxaVFFafGvCv73tSjiUbzzJhSkaxkN2JYTKkeWu_y5wZDuzys-ScBVytDNJUeWFRerB-pbu_u-vL62nYYbnYchxgSQAngkvwBzSqiNa2qtRY0euv1dMFjHCLC0f7Z9SUDnHtoaVF13L25ue25_4MjiV0AH_MNBI9QoNg4IxIUYhiLXT0QPxWAc-NwidkehmuEfx70BH8xbvC_4z3-SN9u57jrnep3Wva973y-ATzSAz_5V77nWjyQhxsUpTGKgFRLnhi-YI5Fsydq7JlbrrHWTnLp9VyZtxkqOTmjPSV3PSnIHB1Oyk2WlxLNq8q990WFezl9mDwt_YfHk86H4qLj9Zbup8GlvOj8brCQ3ag04sXCWxHMtJiS5w4IqaQt1E-ZksTKihggtMs39NVMwp9G95Px1y8Tw3UZgpwSjalFsXCUg402iAuQQEzAisYxfe2myWFPG0GGdAZeM1-pedhv2eqRrnXlX437vX3XIgCTwqEknWZ_klDT5CQHLZxq13VZ64eLotNObgJDmZ8h8JVg8bHh-ZCC72CI4-xar98wkEXLQ3SCknt3_fHNpSV-PqMIYALgGyp-RjUND1ErCnbNQCPJ7OUsklRjvnKClkdfuya1o-32SAvFPKBhmCYYGVceDubVahHc5_MvE392ELd8PsdaymqX5ezVQEdaTn90c_fJctXRwwMXcNcBK0a3ValNVtoizMMNZGt04Cwu3q-dxCWaVs7h21vXc_97pzAUApEIk3VgO4_9AgrefzIf6ZfHe6DUhxD1k97PV1s76N3upbvs9QZev37QlzZTY2FcrjcyYVroZJuIHXjd4BiBQqqL047XvDKMueUKqYGO9CTf6915t7aiASURlk8gKzmiCKwoA7AodIMoZdJcINHcS75QpasHrp0Fo7Xjq1hux8H6rvvJNKQP9rCzwsaBgHDunUAP_2HkufWb51Qu1m6djalqY014Ohp3PReUugSSipmgTnveRa2uU2HQ0xZ9tZUL-YyS7tMSoBi9qLYO2O5oqFKqGGCygixU1Pb9at_F8-VGDz8irHhL9zZrlPx-mrNnZX9hPTcfdNh7OygRS9cy5xIbVDa1pMNv0xiyA6XBikHTQivmWsjIvKu-a9-Veu6tkX13Bu4Lyl6gvONhAq5csF0bZSXKtnpFKXusU6uwLeFzjMRp2tc7f5b2mQl9b5W42tSi4yjTcEG1kP-8AzBvbx7ojmls1QKtBmknwt9cum7_L1_JOMuHjveWiHK4NkroxcL5vqHcAVDZM4kgjne1qggTgezFmZURJsA4Wzzcf9uvv5gFXUSiOvYJiWy4T3I2TF4oDtG-n01LwHsr87Jvn2VR5yRNOYP13pVabMedrj2317Psyf71tyB79hW4qYQ_nf29q9Tu7jHzF0veBJXDKHBLU6LnE0u5DPxiuTDXyWj64H-RRTmGQpyPv_Q6Dg-hRN876PC3V305gWHveIb0BTGZ1dQ5qdqeo-yVc7jSuOi77h4XnKcoCoxkb6rWwJycEwjMRgLENdyPEFswHwwDw2yW5nAn2hYU6t3o9_cNZPWiwCrrwJDsgNhAWY3dcRU31EzLgQDRQgf95CqGmbDkdGtpWUnv3UnZME4RWOBojcB9NsfzGENysor11llNs08ayFRo1YXbK1dPbka3oz2NFkM5BTWMeLP2Tu4T6rt3ksrqzb6Kck1tFeVWW-1WXZWbG7zAOM23prYnOeudOsd7nfy972aTrwvbdzOUsgOfzRfLe9tncyY2NJHa5fYPHydWNh9RhKFgjZ_NZyMrORIeYHLyYPtmskVsjUi4yxJ7TLKybX2bg3LAcpLpCuTbb50lshT8y2mi97uLP5yrAb3FBiiSQJnHgfr_6TNOZ_uMOaukUa65jEb53T4zBwnlWOCXpoq71WfquzJw_F-czh7LalKkRN-xftdt-I6gNTzw3dBOb_gWvaGd3vAten4DQf9Nin4DSf9NmnM7yflbFOd2gnON3kleql6O1SWRb3Cynw-gKHiWR1mJoRrHFbJyLZoSHlUtIzDOZk1mAy8b0Fb9KSOjQBEY7lT6JoeRUQ0oa9HPOMIMqYobLO3e9P26k39TxBBek8w_G5Iby92c6MKWw1alrMbU3TEy_kbZdw6mxCKhmjEpxG9DSLOv21AJXimBShHNuZZSzkrNxwh5p3DW5buHHJiTMW0Iqd3jGiR8xWpM9y0RS-UepcYM554KUWsWWhWH6u1cutKlyecjzrfIRUrsHrdISWvCNFmiau4ZPpYNApTi5OIdFz4ajC4bI2hDjqKdXu8mcsO-tA70mQ7kF0jr4pRdSiXsB_qPauDuRXuraCc5jp8jtptcBm3L6mw9al1GzvGaSBmVfRwWMnvnKAlztBbxiiGJ9rSXN8dtDd1anNAbvWda5XxlkanqaOUx8eOMcgsjFGSN4kBX0mEZ9TePkXKsCDSEEr0n3oqJ6nXCJlGN8-oNYY13___ENVvQ-80pac-CGgmm2Zc603Qfqx7UktrOuvz_vD7UfjUZa0l0g4wnRVZNRGtsLeEfmqEkOpU3BTQa0meHIE3y1gKr2UXTMxYsh2hrRlp1e860z6HCarfNDPahlimF4vaYahHt1IgqBbAlM7nQrenMDKbfOs4GIwZZuMF6CTf_SWiO5ed_AA)** to explore it, change it and export it. Nothing is uploaded: the model travels in the link. Or [download model.json](/docs/examples/hospital-management/model.json).

## Entities

| Entity | Type | Description | Primary Key | Display Attribute | Attributes |
| --- | --- | --- | --- | --- | --- |
| Admission | Entity | An inpatient stay, from admission to discharge. | admission_id | admitted_at | 6 |
| Appointment | Entity | A scheduled outpatient visit. | appointment_id | scheduled_at | 6 |
| Bed | Entity | A bed in a ward. | bed_id | bed_number | 4 |
| Department | Entity | A clinical department, such as Cardiology. | department_id | department_name | 3 |
| Diagnosis | Entity | A condition recorded for a patient during an admission or appointment. | diagnosis_id | description | 7 |
| Doctor | Entity | A physician on staff. | doctor_id | full_name | 6 |
| Insurance Provider | Entity | An insurance company that pays for care. | insurance_provider_id | provider_name | 2 |
| Invoice | Entity | A bill for an admission or appointment. | invoice_id | invoice_number | 8 |
| Medication | Entity | A drug in the hospital formulary. | medication_id | medication_name | 3 |
| Patient | Entity | A person who receives care. Holds personal and health data, so most attributes are PII. | patient_id | full_name | 9 |
| Prescription | Entity | A medication prescribed to a patient by a doctor. | prescription_id | dosage | 7 |
| Ward | Entity | An inpatient ward. | ward_id | ward_name | 3 |
| Blood Type | Reference | ABO and Rh blood types. | blood_type_code | blood_type_name | 2 |
| Specialty | Reference | Medical specialties. | specialty_code | specialty_name | 2 |

## Attributes

### Admission

An inpatient stay, from admission to discharge.

| Attribute | Data Type | Flags | Reference | Example | Description |
| --- | --- | --- | --- | --- | --- |
| admission_id | BIGINT | PK, Required |  | 88010 |  |
| patient_id | BIGINT | Required |  | 300451 |  |
| attending_doctor_id | INT | Required |  | 812 |  |
| bed_id | INT | Required |  | 6012 |  |
| admitted_at | TIMESTAMP | Label, Required |  | 2026-10-30T22:15:00Z |  |
| discharged_at | TIMESTAMP |  |  |  | Empty while admitted. |

### Appointment

A scheduled outpatient visit.

| Attribute | Data Type | Flags | Reference | Example | Description |
| --- | --- | --- | --- | --- | --- |
| appointment_id | BIGINT | PK, Required |  | 1200045 |  |
| patient_id | BIGINT | Required |  | 300451 |  |
| doctor_id | INT | Required |  | 812 |  |
| scheduled_at | TIMESTAMP | Label, Required |  | 2026-11-12T09:30:00Z |  |
| status | STRING | Required |  | SCHEDULED | Scheduled, completed, cancelled or no-show. |
| reason | STRING | PII |  | Chest pain follow-up |  |

### Bed

A bed in a ward.

| Attribute | Data Type | Flags | Reference | Example | Description |
| --- | --- | --- | --- | --- | --- |
| bed_id | INT | PK, Required |  | 6012 |  |
| bed_number | STRING | Label, Required |  | CCU-12 |  |
| ward_id | INT | Required |  | 6 |  |
| is_occupied | BOOLEAN | Required |  | true |  |

### Department

A clinical department, such as Cardiology.

| Attribute | Data Type | Flags | Reference | Example | Description |
| --- | --- | --- | --- | --- | --- |
| department_id | INT | PK, Required |  | 4 |  |
| department_name | STRING | Label, Required |  | Cardiology |  |
| floor | INT |  |  | 3 |  |

### Diagnosis

A condition recorded for a patient during an admission or appointment.

| Attribute | Data Type | Flags | Reference | Example | Description |
| --- | --- | --- | --- | --- | --- |
| diagnosis_id | BIGINT | PK, Required |  | 500912 |  |
| patient_id | BIGINT | Required |  | 300451 |  |
| admission_id | BIGINT |  |  | 88010 |  |
| appointment_id | BIGINT |  |  |  |  |
| icd10_code | STRING | Required, PII |  | I21.4 | ICD-10 diagnosis code. |
| description | STRING | Label, Required, PII |  | Non-ST elevation myocardial infarction |  |
| diagnosed_at | TIMESTAMP | Required |  | 2026-10-31T08:00:00Z |  |

### Doctor

A physician on staff.

| Attribute | Data Type | Flags | Reference | Example | Description |
| --- | --- | --- | --- | --- | --- |
| doctor_id | INT | PK, Required |  | 812 |  |
| license_number | STRING | Required |  | MD-778201 |  |
| full_name | STRING | Label, Required, PII |  | Dr. Aisha Patel |  |
| specialty_code | REFERENCE | Required | → specialty.specialty_code | CARD |  |
| department_id | INT | Required |  | 4 |  |
| phone | STRING | PII |  | +1 555 0123 |  |

### Insurance Provider

An insurance company that pays for care.

| Attribute | Data Type | Flags | Reference | Example | Description |
| --- | --- | --- | --- | --- | --- |
| insurance_provider_id | INT | PK, Required |  | 8 |  |
| provider_name | STRING | Label, Required |  | Blue Ridge Health Plan |  |

### Invoice

A bill for an admission or appointment.

| Attribute | Data Type | Flags | Reference | Example | Description |
| --- | --- | --- | --- | --- | --- |
| invoice_id | BIGINT | PK, Required |  | 61022 |  |
| invoice_number | STRING | Label, Required |  | INV-2026-061022 |  |
| patient_id | BIGINT | Required |  | 300451 |  |
| admission_id | BIGINT |  |  | 88010 |  |
| total_amount | DECIMAL(12,2) | Required |  | 18450.00 |  |
| insurance_covered_amount | DECIMAL(12,2) |  |  | 15000.00 |  |
| issued_on | DATE | Required |  | 2026-11-04 |  |
| paid_on | DATE |  |  |  |  |

### Medication

A drug in the hospital formulary.

| Attribute | Data Type | Flags | Reference | Example | Description |
| --- | --- | --- | --- | --- | --- |
| medication_id | INT | PK, Required |  | 1450 |  |
| medication_name | STRING | Label, Required |  | Atorvastatin 40 mg |  |
| form | STRING |  |  | Tablet |  |

### Patient

A person who receives care. Holds personal and health data, so most attributes are PII.

| Attribute | Data Type | Flags | Reference | Example | Description |
| --- | --- | --- | --- | --- | --- |
| patient_id | BIGINT | PK, Required |  | 300451 |  |
| medical_record_number | STRING | Required, PII |  | MRN-0300451 | MRN. |
| full_name | STRING | Label, Required, PII |  | Daniel Brooks |  |
| date_of_birth | DATE | Required, PII |  | 1975-02-14 |  |
| sex | STRING | PII |  | M |  |
| phone | STRING | PII |  | +1 555 0190 |  |
| blood_type_code | REFERENCE | PII | → blood_type.blood_type_code | O+ |  |
| insurance_provider_id | INT |  |  | 8 |  |
| insurance_member_id | STRING | PII |  | XGH4410021 |  |

### Prescription

A medication prescribed to a patient by a doctor.

| Attribute | Data Type | Flags | Reference | Example | Description |
| --- | --- | --- | --- | --- | --- |
| prescription_id | BIGINT | PK, Required |  | 730055 |  |
| patient_id | BIGINT | Required |  | 300451 |  |
| doctor_id | INT | Required |  | 812 |  |
| medication_id | INT | Required |  | 1450 |  |
| dosage | STRING | Label, Required |  | 40 mg once daily |  |
| start_date | DATE | Required |  | 2026-11-01 |  |
| end_date | DATE |  |  |  |  |

### Ward

An inpatient ward.

| Attribute | Data Type | Flags | Reference | Example | Description |
| --- | --- | --- | --- | --- | --- |
| ward_id | INT | PK, Required |  | 6 |  |
| ward_name | STRING | Label, Required |  | Cardiac Care Unit |  |
| department_id | INT | Required |  | 4 |  |

### Blood Type

ABO and Rh blood types.

| Attribute | Data Type | Flags | Reference | Example | Description |
| --- | --- | --- | --- | --- | --- |
| blood_type_code | STRING | PK, Required |  | O+ |  |
| blood_type_name | STRING | Label, Required |  | O positive |  |

### Specialty

Medical specialties.

| Attribute | Data Type | Flags | Reference | Example | Description |
| --- | --- | --- | --- | --- | --- |
| specialty_code | STRING | PK, Required |  | CARD |  |
| specialty_name | STRING | Label, Required |  | Cardiology |  |

## Relationships

| Relationship | Source | Target | Cardinality | Forward Label | Reverse Label | Key | Description |
| --- | --- | --- | --- | --- | --- | --- | --- |
| insured_by | Patient | Insurance Provider | N:1 | Insured By | Insures | patient.insurance_provider_id |  |
| doctor_in | Doctor | Department | N:1 | Works In | Staffed By | doctor.department_id |  |
| appointment_for | Appointment | Patient | N:1 | For | Has Appointment | appointment.patient_id |  |
| appointment_with | Appointment | Doctor | N:1 | With | Sees | appointment.doctor_id |  |
| ward_of | Ward | Department | N:1 | Run By | Runs | ward.department_id |  |
| bed_in | Bed | Ward | N:1 | In | Has Bed | bed.ward_id |  |
| admits | Admission | Patient | N:1 | Admits | Admitted In | admission.patient_id |  |
| attended_by | Admission | Doctor | N:1 | Attended By | Attends | admission.attending_doctor_id |  |
| assigned_bed | Admission | Bed | N:1 | Assigned | Occupied In | admission.bed_id |  |
| diagnosis_of | Diagnosis | Patient | N:1 | Of | Diagnosed With | diagnosis.patient_id |  |
| made_during_admission | Diagnosis | Admission | N:1 | During | Has Diagnosis | diagnosis.admission_id |  |
| made_during_appointment | Diagnosis | Appointment | N:1 | During | Has Diagnosis | diagnosis.appointment_id |  |
| prescribed_to | Prescription | Patient | N:1 | Prescribed To | Receives | prescription.patient_id |  |
| prescribed_by | Prescription | Doctor | N:1 | Prescribed By | Prescribes | prescription.doctor_id |  |
| prescribes | Prescription | Medication | N:1 | Of | Prescribed In | prescription.medication_id |  |
| billed_to | Invoice | Patient | N:1 | Billed To | Billed | invoice.patient_id |  |
| bills_admission | Invoice | Admission | N:1 | Bills | Billed In | invoice.admission_id |  |

## Reference data

### Blood Type

8 values · Source: Example values

| blood_type_code | blood_type_name |
| --- | --- |
| A+ | A positive |
| A- | A negative |
| B+ | B positive |
| B- | B negative |
| AB+ | AB positive |
| AB- | AB negative |
| O+ | O positive |
| O- | O negative |

### Specialty

6 values · Source: Example values

| specialty_code | specialty_name |
| --- | --- |
| CARD | Cardiology |
| NEUR | Neurology |
| ORTH | Orthopedics |
| PED | Pediatrics |
| ONC | Oncology |
| EM | Emergency medicine |

## SQL DDL (PostgreSQL)

Generated from the model. In the app, Export → Code also writes MySQL, Snowflake and Databricks DDL, DBML, dbt `schema.yml` and data contracts.

```sql
-- Hospital Management System — data model
-- PostgreSQL DDL, generated 2026-11-03
-- Made with EntityModel.com

-- Reference list: Specialty
CREATE TABLE specialty (
  specialty_code TEXT NOT NULL,
  specialty_name TEXT NOT NULL,
  PRIMARY KEY (specialty_code)
);
COMMENT ON TABLE specialty IS 'Medical specialties.';

-- Reference list: Blood Type
CREATE TABLE blood_type (
  blood_type_code TEXT NOT NULL,
  blood_type_name TEXT NOT NULL,
  PRIMARY KEY (blood_type_code)
);
COMMENT ON TABLE blood_type IS 'ABO and Rh blood types.';

CREATE TABLE patient (
  patient_id BIGINT NOT NULL,
  medical_record_number TEXT NOT NULL,
  full_name TEXT NOT NULL,
  date_of_birth DATE NOT NULL,
  sex TEXT,
  phone TEXT,
  blood_type_code TEXT,
  insurance_provider_id INTEGER,
  insurance_member_id TEXT,
  PRIMARY KEY (patient_id)
);
COMMENT ON TABLE patient IS 'A person who receives care. Holds personal and health data, so most attributes are PII.';
COMMENT ON COLUMN patient.medical_record_number IS 'MRN.';

CREATE TABLE department (
  department_id INTEGER NOT NULL,
  department_name TEXT NOT NULL,
  floor INTEGER,
  PRIMARY KEY (department_id)
);
COMMENT ON TABLE department IS 'A clinical department, such as Cardiology.';

CREATE TABLE doctor (
  doctor_id INTEGER NOT NULL,
  license_number TEXT NOT NULL,
  full_name TEXT NOT NULL,
  specialty_code TEXT NOT NULL,
  department_id INTEGER NOT NULL,
  phone TEXT,
  PRIMARY KEY (doctor_id)
);
COMMENT ON TABLE doctor IS 'A physician on staff.';

CREATE TABLE appointment (
  appointment_id BIGINT NOT NULL,
  patient_id BIGINT NOT NULL,
  doctor_id INTEGER NOT NULL,
  scheduled_at TIMESTAMP NOT NULL,
  status TEXT NOT NULL,
  reason TEXT,
  PRIMARY KEY (appointment_id)
);
COMMENT ON TABLE appointment IS 'A scheduled outpatient visit.';
COMMENT ON COLUMN appointment.status IS 'Scheduled, completed, cancelled or no-show.';

CREATE TABLE ward (
  ward_id INTEGER NOT NULL,
  ward_name TEXT NOT NULL,
  department_id INTEGER NOT NULL,
  PRIMARY KEY (ward_id)
);
COMMENT ON TABLE ward IS 'An inpatient ward.';

CREATE TABLE bed (
  bed_id INTEGER NOT NULL,
  bed_number TEXT NOT NULL,
  ward_id INTEGER NOT NULL,
  is_occupied BOOLEAN NOT NULL,
  PRIMARY KEY (bed_id)
);
COMMENT ON TABLE bed IS 'A bed in a ward.';

CREATE TABLE admission (
  admission_id BIGINT NOT NULL,
  patient_id BIGINT NOT NULL,
  attending_doctor_id INTEGER NOT NULL,
  bed_id INTEGER NOT NULL,
  admitted_at TIMESTAMP NOT NULL,
  discharged_at TIMESTAMP,
  PRIMARY KEY (admission_id)
);
COMMENT ON TABLE admission IS 'An inpatient stay, from admission to discharge.';
COMMENT ON COLUMN admission.discharged_at IS 'Empty while admitted.';

CREATE TABLE diagnosis (
  diagnosis_id BIGINT NOT NULL,
  patient_id BIGINT NOT NULL,
  admission_id BIGINT,
  appointment_id BIGINT,
  icd10_code TEXT NOT NULL,
  description TEXT NOT NULL,
  diagnosed_at TIMESTAMP NOT NULL,
  PRIMARY KEY (diagnosis_id)
);
COMMENT ON TABLE diagnosis IS 'A condition recorded for a patient during an admission or appointment.';
COMMENT ON COLUMN diagnosis.icd10_code IS 'ICD-10 diagnosis code.';

CREATE TABLE medication (
  medication_id INTEGER NOT NULL,
  medication_name TEXT NOT NULL,
  form TEXT,
  PRIMARY KEY (medication_id)
);
COMMENT ON TABLE medication IS 'A drug in the hospital formulary.';

CREATE TABLE prescription (
  prescription_id BIGINT NOT NULL,
  patient_id BIGINT NOT NULL,
  doctor_id INTEGER NOT NULL,
  medication_id INTEGER NOT NULL,
  dosage TEXT NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE,
  PRIMARY KEY (prescription_id)
);
COMMENT ON TABLE prescription IS 'A medication prescribed to a patient by a doctor.';

CREATE TABLE invoice (
  invoice_id BIGINT NOT NULL,
  invoice_number TEXT NOT NULL,
  patient_id BIGINT NOT NULL,
  admission_id BIGINT,
  total_amount NUMERIC(12,2) NOT NULL,
  insurance_covered_amount NUMERIC(12,2),
  issued_on DATE NOT NULL,
  paid_on DATE,
  PRIMARY KEY (invoice_id)
);
COMMENT ON TABLE invoice IS 'A bill for an admission or appointment.';

CREATE TABLE insurance_provider (
  insurance_provider_id INTEGER NOT NULL,
  provider_name TEXT NOT NULL,
  PRIMARY KEY (insurance_provider_id)
);
COMMENT ON TABLE insurance_provider IS 'An insurance company that pays for care.';

-- Keys between tables
ALTER TABLE patient ADD CONSTRAINT uq_patient_medical_record_number UNIQUE (medical_record_number);
ALTER TABLE doctor ADD CONSTRAINT uq_doctor_license_number UNIQUE (license_number);
ALTER TABLE invoice ADD CONSTRAINT uq_invoice_invoice_number UNIQUE (invoice_number);
ALTER TABLE patient ADD CONSTRAINT fk_patient_blood_type_code FOREIGN KEY (blood_type_code) REFERENCES blood_type (blood_type_code);
ALTER TABLE doctor ADD CONSTRAINT fk_doctor_specialty_code FOREIGN KEY (specialty_code) REFERENCES specialty (specialty_code);
ALTER TABLE patient ADD CONSTRAINT fk_insured_by FOREIGN KEY (insurance_provider_id) REFERENCES insurance_provider (insurance_provider_id);
ALTER TABLE doctor ADD CONSTRAINT fk_doctor_in FOREIGN KEY (department_id) REFERENCES department (department_id);
ALTER TABLE appointment ADD CONSTRAINT fk_appointment_for FOREIGN KEY (patient_id) REFERENCES patient (patient_id);
ALTER TABLE appointment ADD CONSTRAINT fk_appointment_with FOREIGN KEY (doctor_id) REFERENCES doctor (doctor_id);
ALTER TABLE ward ADD CONSTRAINT fk_ward_of FOREIGN KEY (department_id) REFERENCES department (department_id);
ALTER TABLE bed ADD CONSTRAINT fk_bed_in FOREIGN KEY (ward_id) REFERENCES ward (ward_id);
ALTER TABLE admission ADD CONSTRAINT fk_admits FOREIGN KEY (patient_id) REFERENCES patient (patient_id);
ALTER TABLE admission ADD CONSTRAINT fk_attended_by FOREIGN KEY (attending_doctor_id) REFERENCES doctor (doctor_id);
ALTER TABLE admission ADD CONSTRAINT fk_assigned_bed FOREIGN KEY (bed_id) REFERENCES bed (bed_id);
ALTER TABLE diagnosis ADD CONSTRAINT fk_diagnosis_of FOREIGN KEY (patient_id) REFERENCES patient (patient_id);
ALTER TABLE diagnosis ADD CONSTRAINT fk_made_during_admission FOREIGN KEY (admission_id) REFERENCES admission (admission_id);
ALTER TABLE diagnosis ADD CONSTRAINT fk_made_during_appointment FOREIGN KEY (appointment_id) REFERENCES appointment (appointment_id);
ALTER TABLE prescription ADD CONSTRAINT fk_prescribed_to FOREIGN KEY (patient_id) REFERENCES patient (patient_id);
ALTER TABLE prescription ADD CONSTRAINT fk_prescribed_by FOREIGN KEY (doctor_id) REFERENCES doctor (doctor_id);
ALTER TABLE prescription ADD CONSTRAINT fk_prescribes FOREIGN KEY (medication_id) REFERENCES medication (medication_id);
ALTER TABLE invoice ADD CONSTRAINT fk_billed_to FOREIGN KEY (patient_id) REFERENCES patient (patient_id);
ALTER TABLE invoice ADD CONSTRAINT fk_bills_admission FOREIGN KEY (admission_id) REFERENCES admission (admission_id);

-- Reference values
INSERT INTO specialty (specialty_code, specialty_name) VALUES
  ('CARD', 'Cardiology'),
  ('NEUR', 'Neurology'),
  ('ORTH', 'Orthopedics'),
  ('PED', 'Pediatrics'),
  ('ONC', 'Oncology'),
  ('EM', 'Emergency medicine');

INSERT INTO blood_type (blood_type_code, blood_type_name) VALUES
  ('A+', 'A positive'),
  ('A-', 'A negative'),
  ('B+', 'B positive'),
  ('B-', 'B negative'),
  ('AB+', 'AB positive'),
  ('AB-', 'AB negative'),
  ('O+', 'O positive'),
  ('O-', 'O negative');
```

## Mermaid

Paste into a `mermaid` code block on GitHub, GitLab or Notion to show the diagram there.

```mermaid
---
title: Hospital Management System
---
%% Made with EntityModel.com
erDiagram
  patient {
    bigint patient_id PK "Patient ID"
    string medical_record_number "MRN."
    string full_name "Full Name"
    date date_of_birth "Date Of Birth"
    string sex "Sex"
    string phone "Phone"
    string blood_type_code FK "Blood Type Code"
    int insurance_provider_id FK "Insurance Provider ID"
    string insurance_member_id "Insurance Member ID"
  }
  department {
    int department_id PK "Department ID"
    string department_name "Department Name"
    int floor "Floor"
  }
  doctor {
    int doctor_id PK "Doctor ID"
    string license_number "License Number"
    string full_name "Full Name"
    string specialty_code FK "Specialty Code"
    int department_id FK "Department ID"
    string phone "Phone"
  }
  appointment {
    bigint appointment_id PK "Appointment ID"
    bigint patient_id FK "Patient ID"
    int doctor_id FK "Doctor ID"
    timestamp scheduled_at "Scheduled At"
    string status "Scheduled, completed, cancelled or no-show."
    string reason "Reason"
  }
  ward {
    int ward_id PK "Ward ID"
    string ward_name "Ward Name"
    int department_id FK "Department ID"
  }
  bed {
    int bed_id PK "Bed ID"
    string bed_number "Bed Number"
    int ward_id FK "Ward ID"
    boolean is_occupied "Is Occupied"
  }
  admission {
    bigint admission_id PK "Admission ID"
    bigint patient_id FK "Patient ID"
    int attending_doctor_id FK "Attending Doctor ID"
    int bed_id FK "Bed ID"
    timestamp admitted_at "Admitted At"
    timestamp discharged_at "Empty while admitted."
  }
  diagnosis {
    bigint diagnosis_id PK "Diagnosis ID"
    bigint patient_id FK "Patient ID"
    bigint admission_id FK "Admission ID"
    bigint appointment_id FK "Appointment ID"
    string icd10_code "ICD-10 diagnosis code."
    string description "Description"
    timestamp diagnosed_at "Diagnosed At"
  }
  medication {
    int medication_id PK "Medication ID"
    string medication_name "Medication Name"
    string form "Form"
  }
  prescription {
    bigint prescription_id PK "Prescription ID"
    bigint patient_id FK "Patient ID"
    int doctor_id FK "Doctor ID"
    int medication_id FK "Medication ID"
    string dosage "Dosage"
    date start_date "Start Date"
    date end_date "End Date"
  }
  invoice {
    bigint invoice_id PK "Invoice ID"
    string invoice_number "Invoice Number"
    bigint patient_id FK "Patient ID"
    bigint admission_id FK "Admission ID"
    decimal total_amount "Total Amount"
    decimal insurance_covered_amount "Insurance Covered Amount"
    date issued_on "Issued On"
    date paid_on "Paid On"
  }
  insurance_provider {
    int insurance_provider_id PK "Insurance Provider ID"
    string provider_name "Provider Name"
  }
  specialty {
    string specialty_code PK "Specialty Code"
    string specialty_name "Specialty Name"
  }
  blood_type {
    string blood_type_code PK "Blood Type Code"
    string blood_type_name "Blood Type Name"
  }
  patient }o--|| insurance_provider : "Insured By"
  doctor }o--|| department : "Works In"
  appointment }o--|| patient : "For"
  appointment }o--|| doctor : "With"
  ward }o--|| department : "Run By"
  bed }o--|| ward : "In"
  admission }o--|| patient : "Admits"
  admission }o--|| doctor : "Attended By"
  admission }o--|| bed : "Assigned"
  diagnosis }o--|| patient : "Of"
  diagnosis }o--|| admission : "During"
  diagnosis }o--|| appointment : "During"
  prescription }o--|| patient : "Prescribed To"
  prescription }o--|| doctor : "Prescribed By"
  prescription }o--|| medication : "Of"
  invoice }o--|| patient : "Billed To"
  invoice }o--|| admission : "Bills"
  patient }o..|| blood_type : "Blood Type Code"
  doctor }o..|| specialty : "Specialty Code"
```

## Frequently asked questions

### What tables are in a hospital management system database?

Typically patient, doctor, department, appointment, admission, ward, bed, diagnosis, medication, prescription, invoice and insurance provider, with lookup tables such as specialty and blood type.

### How do you model inpatient admissions and beds?

An admission links a patient to a bed and an attending doctor, with admitted and discharged timestamps. Beds belong to wards, and wards to departments, so occupancy can be reported at each level.