Designing an ER Diagram for a Hospital Management System (Patients, Doctors, Departments, Appointments)

Designing an ER diagram for a Hospital Management System starts by identifying the core Entity types, then specifying each Attribute and the Relationship between them with correct Cardinality and (optionally) Participation.

Below is a complete ER design using four main entities—Patients, Doctors, Departments, and Appointments—then explicitly defining their attributes and cardinalities, including the critical Appointment relationships that tie together Patients and Doctors.

Key modeling decisions:

• Use APPOINTMENT as a relationship-associative entity that captures the scheduling event between a PATIENT and a DOCTOR.
• Model DEPARTMENT–DOCTOR as a typical “one department has many doctors” association.
• Optionally (but commonly), derive Department for an Appointment from the doctor’s department, or store it explicitly to preserve history and support reporting.

Pro Tip: If you need accurate historical reporting (e.g., which department handled the appointment at the time), model department_id on APPOINTMENT explicitly, even if it can be inferred from the doctor later.

Entities, Attributes, and Keys (Hospital Core)

1) PATIENT

Suggested attributes

• patient_id (PK)
• full_name
• date_of_birth
• gender
• phone
• email
• address

Notes

• Keep patient_id as the stable unique identifier.
• Demographic attributes are typically immutable-ish, but you may allow updates later.

2) DEPARTMENT

Suggested attributes

• department_id (PK)
• department_name
• location
• phone

3) DOCTOR

Suggested attributes

• doctor_id (PK)
• full_name
• specialty (e.g., Cardiology, Dermatology)
• license_number
• phone
• email

4) APPOINTMENT

Suggested attributes

• appointment_id (PK)
• start_time
• end_time (optional but useful)
• appointment_type (e.g., New Visit, Follow-up)
• status (e.g., Scheduled, Completed, Canceled)
• reason (optional)
• notes (optional)
• (recommended) patient_id, doctor_id, and optionally department_id as foreign-key-like link attributes in the relational mapping stage

Relationships and Cardinalities (the critical design part)

Relationship A: Patient ↔ Appointment

Meaning: A patient can have many appointments; each appointment belongs to exactly one patient.

• [PATIENT] (1) —— (many) [APPOINTMENT]
• Cardinality:
  • PATIENT to APPOINTMENT: one-to-many
  • APPOINTMENT to PATIENT: many-to-one
• Participation:
  • Total on the Appointment side (every appointment must have a patient)

Formally:

• Each Appointment has exactly 1 patient.
• Each Patient can have 0..N appointments.

Relationship B: Doctor ↔ Appointment

Meaning: A doctor can attend many appointments; each appointment is attended by exactly one doctor.

• [DOCTOR] (1) —— (many) [APPOINTMENT]

Cardinality:

• DOCTOR to APPOINTMENT: one-to-many
• APPOINTMENT to DOCTOR: many-to-one Participation:
• Total on Appointment side (every appointment must have a doctor)

Relationship C: Department ↔ Doctor

Meaning: A department employs many doctors; each doctor belongs to exactly one department (common assumption).

• [DEPARTMENT] (1) —— (many) [DOCTOR]

Cardinality:

• DEPARTMENT to DOCTOR: one-to-many
• DOCTOR to DEPARTMENT: many-to-one

Warning: Some hospitals allow a doctor to work across multiple departments (e.g., joint appointments). If that’s required, this relationship becomes many-to-many and the model must change (e.g., a Doctor_Department associative entity). The design here assumes one primary department per doctor.

Relationship D (Option/Recommendation): Department ↔ Appointment

There are two approaches:

1. Infer Department from Doctor

   • No direct DEPARTMENT–APPOINTMENT relationship.
   • Reporting depends on joining through DOCTOR.

2. Store Department on Appointment (recommended for history)

   • DEPARTMENT (1) —— (many) APPOINTMENT
   • Appointment records remain accurate even if a doctor later changes departments.

In the ER diagram above, we modeled Department ↔ Appointment as one-to-many.

Cardinality Table (Concrete Constraints)