AIAZHCore Entities and Relationships
Every HMS database centers around the patient. The patients table stores demographics, contact information, and medical history references. Each patient can have multiple appointments, each linked to a specific doctor (from the staff table with a role filter). The appointments table is the central junction — it connects to billing, prescriptions, lab orders, and visit records. A well-designed schema normalizes data into at least 3NF (Third Normal Form) to avoid redundancy while maintaining query performance for daily operations like appointment lookups and billing reconciliation.
Patient Management Tables
The patients table includes fields like patient_id (PK), first_name, last_name, date_of_birth, gender, blood_group, phone, email, address, emergency_contact_name, emergency_contact_phone, and registration_date. A separate patient_medical_history table tracks past diagnoses, surgeries, allergies, and chronic conditions with foreign keys back to patient_id. This separation keeps the core patient record lightweight while allowing unlimited historical entries. For compliance, each record should include created_at and updated_at timestamps.
Appointment and Scheduling Schema
The appointments table is the heartbeat of the HMS. Fields include appointment_id (PK), patient_id (FK), doctor_id (FK), appointment_date, appointment_time, status (scheduled/completed/cancelled/no-show), reason_for_visit, and notes. A doctor_schedule table stores availability windows (doctor_id, day_of_week, start_time, end_time, slot_duration_minutes). The system generates available time slots by cross-referencing the schedule with existing appointments to prevent double-booking.
Billing and Insurance
The billing table tracks every financial transaction: bill_id (PK), appointment_id (FK), patient_id (FK), bill_date, total_amount, discount, tax, paid_amount, balance, and payment_status. An insurance_claims table stores policy details including provider_name, policy_number, coverage_percentage, claim_status, and claim_amount. For complex billing scenarios like partial insurance coverage with patient copays, the schema supports split payments through a payment_transactions table that records each payment method and its contribution to the bill.
Pharmacy and Inventory
The pharmacy_inventory table manages medicines: item_id (PK), medicine_name, generic_name, category, manufacturer, batch_number, expiry_date, quantity_in_stock, unit_price, and reorder_level. A prescriptions table connects doctors to medicines: prescription_id (PK), appointment_id (FK), medicine_id (FK), dosage, frequency, duration, and instructions. When a prescription is filled, the inventory quantity decrements automatically and triggers a reorder alert when stock falls below the threshold.
Lab and Diagnostics
The lab_orders table records tests requested during appointments: lab_order_id (PK), appointment_id (FK), test_name, test_category (blood/urine/imaging), ordered_by (doctor_id), order_date, status (pending/collected/processing/completed), and result_date. A lab_results table stores the actual values: result_id (PK), lab_order_id (FK), parameter_name, parameter_value, normal_range, and interpretation. For imaging, a radiology_images table stores file references.
Staff Management
The staff table unifies all hospital employees: staff_id (PK), first_name, last_name, role (doctor/nurse/admin/lab_technician/pharmacist/receptionist), specialization, department_id (FK), phone, email, hire_date, and shift_preference. A departments table lists hospital units (Cardiology, Orthopedics, Emergency, Radiology, etc.). The staff_shifts table assigns actual shifts: shift_id (PK), staff_id (FK), shift_date, start_time, end_time, and notes.
Best Practices for HMS Database Design
Always use UUIDs or composite primary keys for distributed systems. Implement soft deletes (is_active flag) instead of hard deletions for patient records. Add database-level constraints for critical fields like patient email uniqueness and appointment time validation. Index foreign key columns that appear in JOIN operations — especially patient_id, doctor_id, and appointment_id. For compliance with HIPAA or similar regulations, add an audit_log table that records every INSERT, UPDATE, and DELETE operation with the user_id, timestamp, and old/new values. Partition large tables like billing and lab_results by month or year to maintain query performance as data grows.
Full System Prompt — Copy & Paste
Complete Hospital Management System design prompt. Covers database, API, authentication, business logic, and frontend. Paste this into the Business Prompt Builder.
Design and build a complete Hospital Management System (HMS) with the following components. Use Node.js/Express for the backend, PostgreSQL for the database, and React for the frontend. All API endpoints must use JWT authentication with role-based access control.
--- DATABASE SCHEMA ---
Use PostgreSQL with UUID primary keys, foreign key constraints, indexes on all FK columns, and soft deletes (is_active flag) on patient and staff records.
1. patients: patient_id (PK), first_name, last_name, date_of_birth, gender, blood_group, phone, email (UNIQUE), address, emergency_contact_name, emergency_contact_phone, registration_date, is_active, created_at, updated_at
2. patient_medical_history: history_id (PK), patient_id (FK), diagnosis, surgery, allergies, chronic_conditions, recorded_date, notes
3. staff: staff_id (PK), first_name, last_name, role (ENUM: doctor/nurse/admin/lab_technician/pharmacist/receptionist), specialization, department_id (FK), phone, email (UNIQUE), hire_date, shift_preference, is_active, created_at, updated_at
4. departments: department_id (PK), department_name, department_code (UNIQUE), description
5. doctor_schedule: schedule_id (PK), doctor_id (FK to staff), day_of_week (INTEGER 0-6), start_time, end_time, slot_duration_minutes, is_active
6. appointments: appointment_id (PK), patient_id (FK), doctor_id (FK to staff), appointment_date, appointment_time, status (ENUM: scheduled/completed/cancelled/no-show), reason_for_visit, notes, created_at, updated_at
7. billing: bill_id (PK), appointment_id (FK), patient_id (FK), bill_date, total_amount, discount, tax, paid_amount, balance, payment_status (ENUM: pending/partial/paid), created_at, updated_at
8. insurance_claims: claim_id (PK), bill_id (FK), patient_id (FK), provider_name, policy_number, coverage_percentage, claim_status (ENUM: submitted/approved/denied/pending), claim_amount, submitted_date, resolved_date
9. payment_transactions: payment_id (PK), bill_id (FK), payment_method (ENUM: cash/card/insurance/online), payment_amount, reference_number, payment_date, received_by (FK to staff)
10. pharmacy_inventory: item_id (PK), medicine_name, generic_name, category, manufacturer, batch_number, expiry_date, quantity_in_stock, unit_price, reorder_level, is_active, created_at, updated_at
11. prescriptions: prescription_id (PK), appointment_id (FK), medicine_id (FK to pharmacy_inventory), dosage, frequency, duration_days, instructions, prescribed_date
12. lab_orders: lab_order_id (PK), appointment_id (FK), test_name, test_category (ENUM: blood/urine/imaging/other), ordered_by (FK to staff), order_date, status (ENUM: pending/collected/processing/completed), result_date
13. lab_results: result_id (PK), lab_order_id (FK), parameter_name, parameter_value, normal_range, interpretation, recorded_by (FK to staff), recorded_at
14. staff_shifts: shift_id (PK), staff_id (FK), shift_date, start_time, end_time, notes
--- REST API ENDPOINTS ---
Patients:
- POST /api/patients — register new patient
- GET /api/patients — list all patients (with search by name, phone, email)
- GET /api/patients/:id — get patient details with medical history
- PUT /api/patients/:id — update patient info
- GET /api/patients/:id/history — get medical history
- POST /api/patients/:id/history — add medical history entry
Appointments:
- POST /api/appointments — book appointment (validate no double-booking)
- GET /api/appointments — list appointments (filter by doctor, date, status)
- PUT /api/appointments/:id/status — update status (completed/cancelled/no-show)
- GET /api/doctor-schedule/:doctorId/slots — get available time slots for a doctor on a given date
Billing:
- POST /api/billing — create bill from appointment
- GET /api/billing — list bills (filter by patient, status, date range)
- PUT /api/billing/:id/pay — process payment (cash/card/insurance/online)
- POST /api/billing/:id/insurance — submit insurance claim
- GET /api/billing/:id/transactions — get payment history for a bill
Pharmacy:
- POST /api/pharmacy — add medicine to inventory
- GET /api/pharmacy — list inventory (filter by category, expiry alerts)
- PUT /api/pharmacy/:id — update stock
- GET /api/pharmacy/low-stock — get medicines below reorder level
- POST /api/prescriptions — create prescription (auto-decrement inventory)
Lab:
- POST /api/lab-orders — order tests for an appointment
- GET /api/lab-orders — list orders (filter by status, date)
- PUT /api/lab-orders/:id/status — update order status
- POST /api/lab-orders/:id/results — add test results
- GET /api/lab-orders/:id/results — get results
Staff:
- POST /api/staff — add staff member
- GET /api/staff — list staff (filter by role, department)
- PUT /api/staff/:id — update staff info
- GET /api/staff/:id/schedule — get doctor schedule
- POST /api/staff-shifts — assign shift
--- AUTHENTICATION & ROLES ---
JWT-based authentication with these roles:
- admin: full access to all modules, staff management, system settings
- doctor: view assigned patients, manage appointments, create prescriptions, order labs, view billing
- nurse: view patients, record vitals, assist with appointments
- pharmacist: manage pharmacy inventory, fill prescriptions, view low-stock alerts
- lab_technician: view lab orders, record test results, update order status
- receptionist: register patients, book appointments, process billing payments
--- BUSINESS LOGIC ---
1. Double-booking prevention: When booking an appointment, check doctor_schedule and existing appointments to ensure the time slot is available. Return error if slot is already booked.
2. Auto-reorder alerts: When pharmacy_inventory quantity falls below reorder_level, flag the medicine in the low-stock endpoint. Send notification if quantity reaches critical threshold (reorder_level / 2).
3. Prescription inventory decrement: When a prescription is created, automatically decrement pharmacy_inventory quantity_in_stock. If stock falls below reorder_level after decrement, trigger alert.
4. Insurance claim processing: When a bill has insurance coverage, calculate the covered amount based on coverage_percentage, create an insurance_claims record, and update billing balance to patient responsibility.
5. Appointment status workflow: scheduled -> completed/cancelled/no-show. When marked completed, trigger billing creation automatically.
6. Billing calculation: total_amount = sum of consultation fee + lab fees + prescription fees. paid_amount tracks actual payments. balance = total_amount - paid_amount. payment_status auto-updates to paid when balance reaches 0.
--- FRONTEND PAGES ---
1. Dashboard: Overview cards (today's appointments, pending bills, low-stock medicines, pending lab results). Charts for appointments by department, revenue by month.
2. Patient Management: Patient list with search/filter, patient detail view with tabs (info, medical history, appointments, billing), register new patient form.
3. Appointment Calendar: Weekly/monthly calendar view showing doctor availability and booked slots. Drag-to-book interface. Filter by doctor/department.
4. Billing: Bill list with status filters, create bill from appointment, process payment modal (split payment support for cash+insurance), insurance claim submission form.
5. Pharmacy: Medicine inventory grid with stock levels, low-stock highlighted in red, expiry date warnings, add/edit medicine form, prescription list.
6. Lab: Lab order list grouped by status, order detail with result entry form, test result display with normal range comparison.
7. Staff: Staff directory with role filters, doctor schedule view, shift assignment calendar.Database Schema Prompt — Copy & Paste
Database-only prompt for generating the complete Hospital Management System schema.
Design a complete Hospital Management System (HMS) database schema with the following tables and relationships. Use PostgreSQL syntax with UUID primary keys, foreign key constraints, and indexes on all FK columns.
1. patients: patient_id (PK), first_name, last_name, date_of_birth, gender, blood_group, phone, email (UNIQUE), address, emergency_contact_name, emergency_contact_phone, registration_date, is_active, created_at, updated_at
2. patient_medical_history: history_id (PK), patient_id (FK), diagnosis, surgery, allergies, chronic_conditions, recorded_date, notes
3. staff: staff_id (PK), first_name, last_name, role (ENUM: doctor/nurse/admin/lab_technician/pharmacist/receptionist), specialization, department_id (FK), phone, email (UNIQUE), hire_date, shift_preference, is_active, created_at, updated_at
4. departments: department_id (PK), department_name, department_code (UNIQUE), description
5. doctor_schedule: schedule_id (PK), doctor_id (FK to staff), day_of_week (INTEGER 0-6), start_time, end_time, slot_duration_minutes, is_active
6. appointments: appointment_id (PK), patient_id (FK), doctor_id (FK to staff), appointment_date, appointment_time, status (ENUM: scheduled/completed/cancelled/no-show), reason_for_visit, notes, created_at, updated_at
7. billing: bill_id (PK), appointment_id (FK), patient_id (FK), bill_date, total_amount, discount, tax, paid_amount, balance, payment_status (ENUM: pending/partial/paid), created_at, updated_at
8. insurance_claims: claim_id (PK), bill_id (FK), patient_id (FK), provider_name, policy_number, coverage_percentage, claim_status (ENUM: submitted/approved/denied/pending), claim_amount, submitted_date, resolved_date
9. payment_transactions: payment_id (PK), bill_id (FK), payment_method (ENUM: cash/card/insurance/online), payment_amount, reference_number, payment_date, received_by (FK to staff)
10. pharmacy_inventory: item_id (PK), medicine_name, generic_name, category, manufacturer, batch_number, expiry_date, quantity_in_stock, unit_price, reorder_level, is_active, created_at, updated_at
11. prescriptions: prescription_id (PK), appointment_id (FK), medicine_id (FK to pharmacy_inventory), dosage, frequency, duration_days, instructions, prescribed_date
12. lab_orders: lab_order_id (PK), appointment_id (FK), test_name, test_category (ENUM: blood/urine/imaging/other), ordered_by (FK to staff), order_date, status (ENUM: pending/collected/processing/completed), result_date
13. lab_results: result_id (PK), lab_order_id (FK), parameter_name, parameter_value, normal_range, interpretation, recorded_by (FK to staff), recorded_at
14. staff_shifts: shift_id (PK), staff_id (FK), shift_date, start_time, end_time, notes
Add the following indexes: idx_appointments_patient ON appointments(patient_id), idx_appointments_doctor ON appointments(doctor_id), idx_appointments_date ON appointments(appointment_date), idx_billing_patient ON billing(patient_id), idx_prescriptions_appointment ON prescriptions(appointment_id), idx_lab_orders_appointment ON lab_orders(appointment_id). Add CHECK constraints for positive amounts in billing and valid email format in patients.