Healthcare Management System – ER Diagram & Schema
At Specialized Hospital, each patient has a unique patient_id and details such as name, date of birth, gender, contact number, address, blood group, and insurance information. Every patient must have insurance, which includes an insurance_id, provider name, coverage amount, policy number, and a link to the patient.
Patients can schedule consultations with specialists. Each consultation (consultation_id) records the patient, the specialist, date, time, and diagnosis. Specialists belong to departments, which have a department_id, name, and location.
After a consultation, the specialist issues a prescription (prescription_id) for the patient, including medicine name, dosage, duration, and notes. The pharmacy fulfills the prescription and tracks medicine quantity, fill date, and payment status.
This system maintains all patient, consultation, prescription, and pharmacy records in a connected manner for better care, accurate records, and smooth hospital operations.
Entities and Attributes
| Entity | Attributes (PK in bold, FK italic) |
|---|---|
| Patient | patient_id, name, date_of_birth, gender, contact_number, address, blood_group |
| Insurance | insurance_id, provider_name, coverage_amount, policy_number, patient_id |
| Specialist | specialist_id, name, contact_number, specialization, department_id |
| Department | department_id, name, location |
| Consultation | consultation_id, patient_id, specialist_id, date, time, diagnosis |
| Prescription | prescription_id, consultation_id, medicine_name, dosage, duration, notes |
| Pharmacy Record | pharmacy_id, prescription_id, medicine_quantity, fill_date, payment_status |
Relationships and Cardinalities
| Relationship | Cardinality |
|---|---|
| Patient – Insurance | 1:1 |
| Department – Specialist | 1:N |
| Patient – Consultation | 1:N |
| Specialist – Consultation | 1:N |
| Consultation – Prescription | 1:1 |
| Prescription – Pharmacy Record | 1:1 |
Textual ER Diagram
[Patient] 1 ── 1 [Insurance] [Department] 1 ── N [Specialist] [Patient] 1 ── N [Consultation] N ── 1 [Specialist] [Consultation] 1 ── 1 [Prescription] 1 ── 1 [Pharmacy Record]
Explanation:
- Each Patient must have exactly one Insurance (1:1).
- One Department can have many Specialists (1:N).
- A Patient can have multiple Consultations (1:N).
- A Specialist can see multiple Patients in consultations (1:N).
- Each Consultation generates one Prescription (1:1).
- Each Prescription is fulfilled with one Pharmacy Record (1:1).
কোন মন্তব্য নেই:
একটি মন্তব্য পোস্ট করুন