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 favourite ER diagram question. Patients, doctors, appointments and wards. Let's draw it in Visio with crow's foot notation, and put every key in the right place.
- Crow's Foot stencil. I'm using the Crow's Foot Database Notation stencil.
- Ward. Start with ward. Ward ID is the primary key, then a name and a floor.
- Patient. Then patient. Patient ID, a name, and ward ID. That last one is a foreign key. It says which ward the patient is in. Connect ward to patient. One ward holds zero or many patients. A patient is in one and only one ward.
- Doctor. Now doctor, on the lower right. Doctor ID, a name and a specialty.
- Appointment. A patient sees many doctors, and a doctor sees many patients. That's many to many. So appointment sits between them, holding both keys. Connect patient down to appointment. One patient has zero or many appointments. And doctor across to appointment. One doctor has zero or many.
- Foreign keys. Look where the foreign keys sit. Patient ID and doctor ID are both inside appointment, on the crow's foot side of each line.
- Done. Click away to see the finished model.
- The rule. So, ward to patient is one to many. Patient to doctor is many to many, so appointment is the junction that holds both keys. Foreign keys always live on the crow's foot side.
