Hospital Database Schema

erDiagram
  DEPARTMENT ||--o{ DOCTOR : employs
  PATIENT ||--o{ APPOINTMENT : books
  DOCTOR ||--o{ APPOINTMENT : attends
  APPOINTMENT ||--o| PRESCRIPTION : produces
  PRESCRIPTION ||--|{ PRESCRIPTION_ITEM : lists
  MEDICINE ||--o{ PRESCRIPTION_ITEM : "prescribed in"
  PATIENT ||--o{ ADMISSION : has
  WARD ||--|{ BED : contains
  BED ||--o{ ADMISSION : "assigned to"
  ADMISSION ||--|| BILL : generates
  BILL ||--|{ BILL_ITEM : contains
  PATIENT {
    int id PK
    string mrn
    string name
    date dob
  }
  DOCTOR {
    int id PK
    int department_id FK
    string name
    string specialty
  }
  APPOINTMENT {
    int id PK
    int patient_id FK
    int doctor_id FK
    datetime slot
  }
  ADMISSION {
    int id PK
    int patient_id FK
    int bed_id FK
    datetime admitted_at
  }
  BILL {
    int id PK
    int admission_id FK
    decimal total
  }