ER diagram for hospital management system
This ER diagram models the core data of a hospital management system: departments, doctors, patients, appointments, prescriptions and bills. It shows each table's primary and foreign keys and how the tables connect through one-to-many relationships. It suits students preparing a DBMS project and developers sketching a first clinic or hospital database.
Example: ER diagram for hospital management system: click to open it in the editor and change it.
Entities and key attributes
| Entity | Primary key | Key attributes | Foreign keys |
|---|---|---|---|
| departments | id | name, floor | none |
| doctors | id | name, specialization | department_id |
| patients | id | name, dob, phone, address | none |
| appointments | id | scheduled_at, status | patient_id, doctor_id |
| prescriptions | id | medicine, dosage | appointment_id |
| bills | id | total, is_paid | patient_id |
Relationships and cardinality
- departments → doctors (1:N): a department has many doctors, and each doctor belongs to one department through department_id.
- patients → appointments (1:N): a patient can book many appointments, and each appointment is for one patient.
- doctors → appointments (1:N): a doctor handles many appointments, and each appointment is with one doctor.
- appointments → prescriptions (1:N): one appointment can produce several prescriptions, one per medicine.
- patients → bills (1:N): a patient can receive many bills over time.
appointments is the central table. It links patients and doctors, which turns their many-to-many relationship into two one-to-many relationships. Prescriptions hang off the appointment, so you can always trace a medicine back to the visit, the doctor and the patient.
How to adapt it for your project
- Change dosage from INT to VARCHAR (for example "500 mg twice daily"), or split it into amount, unit and frequency.
- Add appointment_id to bills if you want to charge per visit instead of per patient.
- Add tables such as wards, rooms or nurses for in-patient care, and link them to departments.
- Turn medicine into a foreign key to a separate medicines table to avoid spelling duplicates.
- Limit status to fixed values like scheduled, completed and cancelled.
Tip: Before you add new tables, write two or three real questions the system must answer, such as "Which unpaid bills belong to patients seen by cardiology?" Then check that you can follow the foreign keys to answer each one.
Frequently asked questions
What are the entities in an ER diagram for a hospital management system?
This diagram has six entities: departments, doctors, patients, appointments, prescriptions and bills. Each one has an integer primary key called id.
How are doctors and patients related in the hospital ER diagram?
They are linked through the appointments table, which holds both patient_id and doctor_id. This resolves the many-to-many relationship into two one-to-many relationships.
What is the cardinality between appointments and prescriptions?
It is one-to-many. One appointment can have many prescriptions, and each prescription references exactly one appointment through appointment_id.
Make your own ER diagram with AI →