How do I design an ER diagram for a hospital management system?
- Expert answer
- Undergraduate
- Asked
The question
My database assignment asks me to draw an ER diagram for a hospital management system.
I do not know which entities to include or how to decide one-to-many and many-to-many relationships.
Short answer
A hospital ER diagram should identify core entities such as Patient, Doctor, Appointment, Department, Prescription and Ward, then show relationships, cardinality, keys and assumptions clearly.
Full expert answer
Database systems tutor
MSc Data Science, Oracle certified
An ER diagram assignment is not about drawing every possible hospital process. It is about modelling the scope given in the brief. A good ERD is clear about entities, attributes, relationships, cardinality and assumptions.
Start with the scope
If the assignment says "appointments and prescriptions", do not build a full national health record system. Define what your system handles:
- patient registration
- doctor details
- departments
- appointments
- prescriptions
- wards or beds, if inpatient care is included
- billing, only if the brief asks for it
Core entities
| Entity | Example attributes |
|---|---|
| Patient | patient_id, name, date_of_birth, phone |
| Doctor | doctor_id, name, speciality, department_id |
| Department | department_id, department_name |
| Appointment | appointment_id, patient_id, doctor_id, appointment_date, status |
| Prescription | prescription_id, appointment_id, medicine_name, dosage |
| Ward | ward_id, ward_name, capacity |
| Admission | admission_id, patient_id, ward_id, admit_date, discharge_date |
Relationship examples
- One department has many doctors.
- One doctor can have many appointments.
- One patient can have many appointments.
- One appointment may generate many prescription items.
- One patient can have many admissions.
- One ward can have many admissions over time.
Appointments are often the bridge between Patient and Doctor. Without Appointment, a many-to-many relationship between doctors and patients becomes messy.
Mini ERD explanation
If a patient can see many doctors, and a doctor can see many patients, that is many-to-many. In a relational database, you usually resolve it with an Appointment table:
textPatient 1 -- many Appointment many -- 1 DoctorThe Appointment table stores the date, time, reason, status and possibly notes for that consultation. It also contains foreign keys to Patient and Doctor.
Sample university-style questions and how to answer them
| Sample question | What a strong answer should do |
|---|---|
| Draw an ER diagram for hospital appointments and prescriptions. | Include Patient, Doctor, Appointment and Prescription. Show Appointment as the link between Patient and Doctor. |
| Identify primary and foreign keys in a hospital ERD. | Mark IDs as primary keys and show foreign keys where relationships are implemented. |
| Explain the cardinality between Patient and Appointment. | One patient can have many appointments, but each appointment belongs to one patient. |
| Model inpatient ward admissions. | Add Ward and Admission, with dates to handle repeated admissions over time. |
| State assumptions for a hospital database design. | Clarify scope, such as one doctor per appointment or whether appointments can involve multiple clinicians. |
Common mistakes
- Making Patient and Doctor directly many-to-many without an associative entity
- Including too many vague entities such as "Hospital" with no purpose
- Forgetting primary keys
- Confusing attributes with entities
- Not showing cardinality
- Designing for every possible hospital process instead of the assignment scope
What earns higher marks
State assumptions beside the ERD. For example: "Each appointment is assigned to one doctor, but a doctor may have many appointments." If the real world is more complex, explain that your model is simplified for the assignment scope.
Academic use note
This guide is for database design assignment support. Adapt the entity set and assumptions to your own brief before drawing the final ERD.
Sources and further reading
This answer explains a method for you to apply to your own work. Copying it into a submission would count as plagiarism, and it is indexed by similarity checkers.
All questions