Data Requirements

ER Diagram

---

Entities

Doctor_Level

• level_id (PK, bigint)
• level (text)

Doctor_Specialization

• specialization_id (PK, bigint)
• specialization_name (text)

Departments

• department_id (PK, bigint)
• department_name (text)

Doctors

• doctor_id (PK, bigint)
• first_name (text)
• last_name (text)
• email_address (text)
• level_id (FK, bigint)
• specialization_id (FK, bigint)
• department_id (FK, bigint)

Patients

• patient_id (PK, bigint)
• first_name (text)
• last_name (text)
• email_address (text)
• date_of_birth (date)
• blood_type (text)
• gender (text)
• phone_number (text)
• embg (text)

Admin

• admin_id (PK, bigint)
• username (text)
• name (text)
• lastname (text)
• email (text)

Lab_Technician

• technician_id (PK, bigint)
• username (text)
• name (text)
• lastname (text)
• email (text)

Users

• user_id (PK, serial)
• username (text)
• password (text)
• role (text)
• first_name (text)
• last_name (text)
• patient_id (FK, bigint, nullable)
• doctor_id (FK, bigint, nullable)
• admin_id (FK, bigint, nullable)
• technician_id (FK, bigint, nullable)
• is_active (boolean)

Appointments

• appointment_id (PK, bigint)
• appointment_date (date)
• appointment_time (time)
• status (text)
• patient_id (FK, bigint)
• doctor_id (FK, bigint)

Diagnosis

• diagnosis_id (PK, bigint)
• name (text)
• description (text)
• patient_id (FK, bigint)
• doctor_id (FK, bigint)

Procedures

• procedure_id (PK, bigint)
• procedure_type (text)
• procedure_date (date)
• description (text)
• cost (decimal)
• doctor_id (FK, bigint)
• diagnosis_id (FK, bigint)

Procedure_Results

• result_id (PK, bigint)
• result_description (text)
• result_date (date)
• procedure_id (FK, bigint)

Prescriptions

• prescription_id (PK, bigint)
• medication_name (text)

Prescription_Restriction

• restriction_id (PK, bigint)
• description (text)
• prescription_id (FK, bigint)

Lab_Tests

• test_id (PK, bigint)
• test_name (text)
• description (text)
• cost (decimal)

Lab_Results

• result_id (PK, bigint)
• results (text)
• result_date (date)
• test_id (FK, bigint)

Medical_Records

(simplified)

• record_id (PK, bigint)
• patient_id (FK, bigint)

Allergies

• allergy_id (PK, bigint)
• name (text)
• allergy_severity (text)

Symptoms

• symptom_id (PK, bigint)
• name (text)
• description (text)

Referrals

• referral_id (PK, bigint)
• reason (text)
• referral_date (date)
• record_id (FK, bigint)
• from_doctor_id (FK, bigint)
• to_doctor_id (FK, bigint)

Medical_Report

• report_id (PK, bigint)
• description (text)
• report_date (date)
• record_id (FK, bigint)
• doctor_id (FK, bigint)

Billing

• bill_id (PK, bigint)
• total_cost (decimal)
• payment_status (text)
• payment_date (date)
• record_id (FK, bigint)
• admin_id (FK, bigint)

Performed_Procedures

• performed_id (PK, bigint)
• procedure_id (FK, bigint)
• doctor_id (FK, bigint)
• patient_id (FK, bigint)
• diagnosis_id (FK, bigint, nullable)
• procedure_date (date)
• notes (text)

Performed_Lab_Tests

• performed_test_id (PK, bigint)
• test_id (FK, bigint)
• patient_id (FK, bigint)
• doctor_id (FK, bigint)
• technician_id (FK, bigint)
• test_date (date)
• notes (text)

Associative entities (M:N junctions that carry their own attributes)

Prescription_Medical_Records

• prescription_id (FK, bigint)
• record_id (FK, bigint)
• dosage (text)
• frequency (text)
• duration (text)
• notes (text)

Medical_Record_Symptoms

• record_id (FK, bigint)
• symptom_id (FK, bigint)
• severity (text)

Medical_Record_Allergies

• record_id (FK, bigint)
• allergy_id (FK, bigint)
• reaction (text)
• severity (text)

---

Relations

is (Doctor_Level ↔ Doctors, 1:N)

each doctor has one level, a level applies to many doctors.

has_specialization (Doctor_Specialization ↔ Doctors, 1:N)

each doctor has one specialization, a specialization applies to many doctors.

works_in (Departments ↔ Doctors, 1:N)

each doctor belongs to one department, a department employs many doctors.

identifies (Users ↔ Patients / Doctors / Admin / Lab_Technician, 1:1)

a user account links to exactly one underlying person record, depending on role.

has_allergies (Patients ↔ Allergies, M:N via Patient_Allergies)

patients can have multiple allergies, an allergy can affect many patients.

affects_prescription (Allergies ↔ Prescription_Restriction, M:N via Allergy_Prescription_Restrictions)

an allergy can be tied to multiple restrictions; a restriction can apply to multiple allergies.

has_symptoms (Patients ↔ Symptoms, M:N via Patient_Symptoms)

patients can report multiple symptoms, a symptom can be reported by many patients.

indicates (Symptoms ↔ Diagnosis, M:N via Diagnosis_Symptoms)

a diagnosis can involve multiple symptoms, a symptom can appear in multiple diagnoses.

books (Patients ↔ Appointments, 1:N)

a patient books many appointments, each appointment belongs to one patient.

attends (Doctors ↔ Appointments, 1:N)

a doctor attends many appointments, each appointment is handled by one doctor.

diagnoses (Doctors ↔ Diagnosis, 1:N)

a doctor makes many diagnoses, each diagnosis is made by one doctor.

diagnosed_with (Patients ↔ Diagnosis, 1:N)

a patient can have many diagnoses; each diagnosis belongs to one patient.

performs_allowed (Doctor_Specialization ↔ Procedures, M:N via Specialization_Procedures)

a specialization is linked to the procedures it's allowed to perform.

requires (Diagnosis ↔ Procedures, M:N via Diagnosis_Procedures)

a diagnosis can require multiple procedures, and the same procedure can be linked to multiple diagnoses ,in addition to the direct diagnosis_id FK each procedure row already carries.

produces (Procedures ↔ Procedure_Results, 1:N)

a procedure can produce multiple results, each result belongs to one procedure.

performs (Departments ↔ Procedures, M:N via Department_Procedures)

a department can perform multiple procedures, and a procedure can be performed in multiple departments.

billed_for (Procedures ↔ Billing, M:N via Billing_Procedures)

a bill can include multiple procedures, a procedure can appear on multiple bills.

restricts (Prescriptions ↔ Prescription_Restriction, 1:N)

a prescription can have multiple restrictions, each restriction belongs to one prescription.

tested_as (Lab_Tests ↔ Performed_Lab_Tests, 1:N)

a catalog test can be performed many times; each performed test refers to one catalog test.

takes_test (Patients ↔ Performed_Lab_Tests, 1:N)

a patient can undergo many performed tests, each performed test belongs to one patient.

orders_test (Doctors ↔ Performed_Lab_Tests, 1:N)

a doctor can order many tests, each performed test is ordered by one doctor.

conducts (Lab_Technician ↔ Performed_Lab_Tests, 1:N)

a technician can conduct many tests, each performed test is run by one technician.

produces_test (Lab_Tests ↔ Lab_Results, 1:N)

a catalog test can produce multiple results, each result belongs to one test.

billed_for_test (Lab_Tests ↔ Billing, M:N via Billing_Lab_Tests)

a bill can include multiple tests, a test can appear on multiple bills.

performed_as (Procedures ↔ Performed_Procedures, 1:N)

a catalog procedure can be performed many times.

performed_on (Patients ↔ Performed_Procedures, 1:N)

a patient can have many procedures performed, each performed procedure belongs to one patient.

performed_by (Doctors ↔ Performed_Procedures, 1:N)

a doctor can perform many procedures, each performed procedure has one doctor.

has (Patients ↔ Medical_Records, 1:N as constrained)

each medical record belongs to one patient.

updates (Doctors ↔ Medical_Records, M:N via Doctor_Medical_Records)

doctors can update multiple records; a record can be updated by multiple doctors.

relates_to (Diagnosis ↔ Medical_Records, M:N via Diagnosis_Medical_Records)

diagnoses can relate to multiple records, a record can include multiple diagnoses.

linked_to (Prescriptions ↔ Medical_Records, M:N via Prescription_Medical_Records)

prescriptions can be linked to multiple records, a record can include multiple prescriptions, each with its own dosage/frequency/duration/notes for that record.

contains_symptoms (Medical_Records ↔ Symptoms, M:N via Medical_Record_Symptoms)

a record can list multiple symptoms, each with its own severity.

contains_allergies (Medical_Records ↔ Allergies, M:N via Medical_Record_Allergies)

a record can list multiple allergies, each with its own reaction and severity.

includes_procedures (Medical_Records ↔ Procedures, M:N via Medical_Record_Procedures)

a record can reference multiple procedures.

includes_lab_results (Medical_Records ↔ Lab_Results, M:N via Medical_Record_Lab_Results)

a record can reference multiple lab results.

generates_referral (Medical_Records ↔ Referrals, 1:N)

a record can generate multiple referrals, each referral is based on one record.

issues (Doctors ↔ Referrals as from_doctor_id, 1:N)

a doctor can issue many referrals.

receives_referral (Doctors ↔ Referrals as to_doctor_id, 1:N)

a doctor can receive many referrals.

creates (Doctors ↔ Medical_Report, 1:N)

a doctor can create multiple reports, each report is created by one doctor.

generates_report (Medical_Records ↔ Medical_Report, 1:N)

a record can generate multiple reports, each report is based on one record.

includes_lab_results_report (Medical_Report ↔ Lab_Results, M:N via Medical_Report_Lab_Results)

a report can include multiple lab results, a result can appear in multiple reports.

processes (Admin ↔ Billing, 1:N)

an admin can process multiple bills, each bill is processed by one admin.

generates_bill (Medical_Records ↔ Billing, 1:N)

a record can generate multiple bills, each bill is tied to one record.