ER Diagram for a Hospital Management System (Visio Example, Crow's Foot)
Patients, doctors, appointments and wards. Draw the hospital ER diagram in Visio with crow's foot notation, put every primary and foreign key in the right place, and see why Appointment is the junction between Patient and Doctor.
Step by step
- Hospital ER diagram. A hospital system is a common ER diagram exercise. It has 4 main entities: Patient, Doctor, Appointment and Ward. Here it is drawn in Visio with crow's foot notation, with every primary and foreign key in the right place.
- Crow's Foot stencil. Open the Crow's Foot Database Notation stencil in the Shapes pane. It holds the entity and relationship shapes used here.
- Ward. Start with Ward. Add an entity with WardID as the primary key, then Name and Floor.
- Patient. Next, add Patient with PatientID as the primary key, then Name and WardID. WardID is a foreign key: it records which ward the patient is in. Connect Ward to Patient with a Relationship connector. Each ward holds 0 or many patients, and each patient is in exactly 1 ward.
- Doctor. Add Doctor on the lower right, with DoctorID as the primary key, then Name and Specialty.
- Appointment. A patient sees many doctors, and a doctor sees many patients. That is many-to-many, so add Appointment between them. It has AppointmentID as its primary key, plus PatientID and DoctorID. Connect Patient down to Appointment. Each patient has 0 or many appointments. Then connect Doctor across to Appointment. Each doctor also has 0 or many appointments.
- Foreign keys. Check where the foreign keys sit. PatientID and DoctorID are both inside Appointment, on the crow's foot (many) end of each line.
- Done. Click an empty part of the page to deselect the shapes and see the finished model.
- The rule. Ward to Patient is 1-to-many. Patient to Doctor is many-to-many, so Appointment is the junction entity that holds both keys. In a 1-to-many relationship, the foreign key goes in the entity on the crow's foot (many) side.
Common mistakes
- Linking Patient straight to Doctor. A many-to-many line can't be stored as tables, because neither table can hold a list of the other's IDs. Add Appointment as a junction entity that holds PatientID and DoctorID.
- Putting the foreign key on the wrong side. Ward can't hold a list of PatientIDs. The foreign key goes on the many side, so WardID sits in Patient.
- Drawing the lines but leaving out the keys. If Appointment has no PatientID and DoctorID attributes, the diagram looks right but the tables can't be joined. Add both keys inside Appointment.
- Using a mandatory many end where 0 is allowed. A new ward may have no patients yet, and a new doctor may have no appointments. Use the 0-or-many crow's foot end unless the business rule says at least 1 is required.
When a business analyst uses this
In a requirements workshop for a clinic booking system, a BA draws Patient, Doctor and Appointment live in Visio so staff can confirm rules like "a patient can see many doctors". The agreed diagram then goes into the data requirements for sign-off before developers build the tables.
Questions people ask
How do you draw an ER diagram for a hospital management system?
Start with the main entities: Ward, Patient, Doctor and Appointment. Give each a primary key, add foreign keys like WardID in Patient, then connect them with crow's foot relationship lines.
How do you convert a hospital ER diagram into relational tables?
Each entity becomes a table and each attribute a column: Ward(WardID, Name, Floor), Patient(PatientID, Name, WardID), Doctor(DoctorID, Name, Specialty) and Appointment(AppointmentID, PatientID, DoctorID). The foreign keys link the tables.
Why is Appointment needed between Patient and Doctor?
A patient sees many doctors and a doctor sees many patients, which is many-to-many. Appointment turns this into 1-to-many links from Patient and from Doctor, holding PatientID and DoctorID as foreign keys.
