Hospital Management System Database Schema
A hospital management system connects patient care to billing in twelve tables. Patients book appointments with doctors; visits produce medical records; records carry prescriptions that reference a medications catalog; inpatient stays flow through admissions into rooms and beds; and every visit or stay rolls up into bills, which are settled by individual payments. Two modeling choices keep the schema tight: a single staff table covers doctors, nurses, and admin via a role column (so appointments reference staff, not doctors), and billing splits into bills (the aggregate) and payments (each settlement attempt) so partial and failed payments are representable.
Last updated: 2026-09-27
Source: Original design by dbdiagramr, modeled on standard DBMS textbook schemas
Tables in the Hospital Management System schema
| Column | Type | Nullable | Key |
|---|---|---|---|
| departments | |||
| id | uuid | No | PK |
| name | varchar | No | - |
| building | varchar | Yes | - |
| floor | integer | Yes | - |
| patients | |||
| id | uuid | No | PK |
| first_name | varchar | No | - |
| last_name | varchar | No | - |
| date_of_birth | date | No | - |
| gender | varchar | Yes | - |
| blood_group | varchar(3) | Yes | - |
| phone | varchar | No | - |
| varchar | Yes | - | |
| address | text | Yes | - |
| emergency_contact | varchar | Yes | - |
| created_at | timestamptz | No | - |
| staff | |||
| id | uuid | No | PK |
| first_name | varchar | No | - |
| last_name | varchar | No | - |
| role | varchar | No | - |
| department_id | uuid | Yes | - |
| specialization | varchar | Yes | - |
| phone | varchar | Yes | - |
| hire_date | date | Yes | - |
| rooms | |||
| id | uuid | No | PK |
| number | varchar | No | - |
| room_type | varchar | No | - |
| capacity | integer | No | - |
| beds | |||
| id | uuid | No | PK |
| room_id | uuid | No | - |
| bed_number | varchar | No | - |
| status | varchar | No | - |
| appointments | |||
| id | uuid | No | PK |
| patient_id | uuid | No | - |
| doctor_id | uuid | No | - |
| department_id | uuid | Yes | - |
| appointment_at | timestamptz | No | - |
| status | varchar | No | - |
| notes | text | Yes | - |
| medical_records | |||
| id | uuid | No | PK |
| patient_id | uuid | No | - |
| doctor_id | uuid | No | - |
| appointment_id | uuid | Yes | - |
| recorded_at | timestamptz | No | - |
| diagnosis | text | No | - |
| symptoms | text | Yes | - |
| treatment_plan | text | Yes | - |
| medications | |||
| id | uuid | No | PK |
| name | varchar | No | - |
| unit | varchar | Yes | - |
| price | numeric(10,2) | Yes | - |
| prescriptions | |||
| id | uuid | No | PK |
| record_id | uuid | No | - |
| medication_id | uuid | No | - |
| dosage | varchar | No | - |
| frequency | varchar | No | - |
| duration_days | integer | Yes | - |
| admissions | |||
| id | uuid | No | PK |
| patient_id | uuid | No | - |
| bed_id | uuid | No | - |
| admitted_at | timestamptz | No | - |
| discharged_at | timestamptz | Yes | - |
| admission_type | varchar | Yes | - |
| bills | |||
| id | uuid | No | PK |
| patient_id | uuid | No | - |
| admission_id | uuid | Yes | - |
| appointment_id | uuid | Yes | - |
| total_amount | numeric(10,2) | No | - |
| insurance_covered | numeric(10,2) | No | - |
| status | varchar | No | - |
| billed_at | timestamptz | No | - |
| payments | |||
| id | uuid | No | PK |
| bill_id | uuid | No | - |
| amount | numeric(10,2) | No | - |
| method | varchar | No | - |
| paid_at | timestamptz | No | - |
Frequently asked questions
What tables does a hospital management system database need?
A standard hospital schema needs departments, patients, staff, rooms, beds, appointments, medical_records, medications, prescriptions, admissions, bills, and payments - 12 tables covering scheduling, clinical care, inpatient stays, and billing.
Should doctors and nurses be separate tables?
Usually not. A single staff table with a role column (doctor, nurse, admin) plus department and specialization columns covers all personnel, and appointments can reference any staff member. Split them only when doctors and nurses need very different columns.
How are bills and payments modeled in a hospital schema?
Bills aggregate charges against a patient (optionally tied to one admission or appointment) with insurance-covered and status columns. Payments are separate rows per settlement attempt against a bill, which models partial payments and failed transactions naturally.
Explore more schemas
Visualize your own database
Paste your PostgreSQL connection string and get an interactive ER diagram of your own schema in under 10 seconds. No signup required.
Try it free →