Changes between Version 5 and Version 6 of ConceptualModel

Timestamp:

09/01/26 16:18:43 (9 days ago)

Author:

236021

Comment:

--

ConceptualModel

@@ -1 @@
-= Conceptual Model
+= Data Requirements  =
 
 == ER Diagram
-
-[[Image(Hospital_ER_Diagram2.drawio.png, 1460px)]]
-
-== Data requirements
-
-=== Entities
-
-1. **Doctor_Level** – entity that stores doctor levels  
-- level_id (PK, bigint)  
-- level (text)
-
-2. **Doctor_Specialization** – entity that stores doctor specializations  
-- specialization_id (PK, bigint)  
-- specialization_name (text)
-
-3. **Departments** – entity that stores hospital departments  
-- department_id (PK, bigint)  
-- department_name (text)
-
-4. **Doctors** – entity that stores 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)
-
-5. **Admin** – entity that stores administrator data  
-- admin_id (PK, bigint)
-- username (text)
-- name (text)
-- lastname (text)
-- email (text)
-
-6. **Lab_Technician** – entity that stores lab technician data  
-- technician_id (PK, bigint)
-- username (text)
-- name (text)
-- lastname (text)
-- email (text)
-
-7. **Patients** – entity that stores 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)
-
-8. **Allergies** – entity that stores allergies  
-- allergy_id (PK, bigint)  
-- name (text)  
-- allergy_severity (text)
-
-9. **Symptoms** – entity that stores symptoms  
-- symptom_id (PK, bigint)  
-- name (text)  
-- description (text)
-
-10. **Appointments** – entity that stores appointments  
-- appointment_id (PK, bigint)  
-- appointment_date (date)  
-- appointment_time (time)  
-- status (text)  
-- patient_id (FK, bigint)  
-- doctor_id (FK, bigint)
-
-11. **Diagnosis** – entity that stores diagnoses  
-- diagnosis_id (PK, bigint)  
-- name (text)  
-- description (text)  
-- patient_id (FK, bigint)  
-- doctor_id (FK, bigint)
-
-12. **Procedures** – entity that stores medical procedures  
-- procedure_id (PK, bigint)  
-- procedure_type (text)  
-- procedure_date (date)  
-- description (text)  
-- cost (decimal)  
-- doctor_id (FK, bigint)  
-- diagnosis_id (FK, bigint)
-
-13. **Procedure_Results** – entity that stores procedure results  
-- result_id (PK, bigint)  
-- result_description (text)  
-- result_date (date)  
-- procedure_id (FK, bigint)
-
-14. **Prescriptions** – entity that stores prescriptions  
-- prescription_id (PK, bigint)  
-- medication_name (text)  
-- dosage (text)  
-- instructions (text)  
-- duration (text)  
-- price (decimal)  
-- doctor_id (FK, bigint)  
-- patient_id (FK, bigint)
-
-15. **Prescription_Restriction** – entity that stores prescription restrictions  
-- restriction_id (PK, bigint)  
-- allowed (boolean)  
-- prescription_id (FK, bigint)
-
-16. **Lab_Tests** – entity that stores lab tests  
-- test_id (PK, bigint)  
-- test_name (text)  
-- description (text)  
-- cost (decimal)  
-- patient_id (FK, bigint)  
-- technician_id (FK, bigint)
-
-17. **Lab_Results** – entity that stores lab results  
-- result_id (PK, bigint)  
-- results (text)  
-- result_date (date)  
-- test_id (FK, bigint)
-
-18. **Medical_Records** – entity that stores medical records  
-- record_id (PK, bigint)  
-- doctor_notes (text)  
-- patient_id (FK, bigint)  
-- doctor_id (FK, bigint)
-
-19. **Referrals** – entity that stores referrals  
-- referral_id (PK, bigint)  
-- reason (text)  
-- date (date)  
-- doctor_id (FK, bigint)  
-- patient_id (FK, bigint)
-
-20. **Medical_Report** – entity that stores medical reports  
-- report_id (PK, bigint)  
-- description (text)  
-- date (date)  
-- patient_id (FK, bigint)  
-- doctor_id (FK, bigint)
-
-21. **Billing** – entity that stores billing information  
-- bill_id (PK, bigint)  
-- payment_status (text)  
-- payment_date (date)  
-- patient_id (FK, bigint)
-
-=== Relations
-
-1. **is** (Doctor_Levels ↔ Doctors, 1:N)
-Each doctor is assigned one level, while a level can belong to many doctors.
-
-2. **has_specialization** (Doctors ↔ Doctor_Specialization, 1:N)
-Each doctor has one specialization, while a specialization can apply to many doctors.
-
-3. **works_in** (Departments ↔ Doctors, 1:N)
-Each doctor works in one department, while a department employs many doctors.
-
-4. **has_allergies** (Patients ↔ Allergies, M:N)
-Patients can have multiple allergies, and the same allergy can affect many patients.
-
-5. **contains_allergies** (Allergies ↔ Medical_Record, M:N)
-A medical record can contain multiple allergies, while the same allergy can appear in multiple medical records.
-
-6. **affects_prescription** (Allergy ↔ Prescription_Restriction, M:N)
-An allergy can be associated with multiple prescription restrictions, while the same restriction can apply to multiple allergies.
-
-7. **has_symptoms** (Patients ↔ Symptoms, M:N)
-Patients can report multiple symptoms, and each symptom can be reported by many patients.
-
-8. **indicates** (Symptoms ↔ Diagnosis, M:N)
-A diagnosis can be associated with multiple symptoms, while the same symptom can appear in multiple diagnoses.
-
-9. **contains_symptoms** (Medical_Record ↔ Symptoms, 1:N)
-Each medical record can contain multiple symptoms, while each symptom entry belongs to one medical record.
-
-10. **books** (Patients ↔ Appointments, 1:N)
-A patient can book multiple appointments, while each appointment belongs to one patient.
-
-11. **attends** (Doctors ↔ Appointments, 1:N)
-A doctor can attend multiple appointments, while each appointment is handled by one doctor.
-
-12. **updates** (Appointment ↔ Medical_Record, 1:N)
-An appointment can generate multiple updates in the medical record, while each update is associated with one appointment.
-
-13. **diagnoses** (Doctors ↔ Diagnosis, 1:N)
-A doctor can make multiple diagnoses, while each diagnosis is made by one doctor.
-
-14. **performs_allowed** (Doctor_Specialization ↔ Procedure, M:N)
-A doctor specialization can be associated with multiple procedures it allows, while the same procedure can be associated with multiple doctor specializations.
-
-15. **requires** (Diagnosis ↔ Procedure, M:N)
-A diagnosis can require multiple procedures, while the same procedure can be required by multiple diagnoses.
-
-16. **produces** (Procedure ↔ Procedure_Result, 1:N)
-A procedure can produce multiple results, while each result belongs to one procedure.
-
-17. **performs** (Department ↔ Procedure, M:N)
-A department can perform multiple procedures, while the same procedure can be performed in multiple departments.
-
-18. **billed_for** (Procedure ↔ Billing, M:N)
-A billing record can include multiple procedures, while the same procedure can appear in multiple billing records.
-
-19. **prescribes** (Doctors ↔ Prescriptions, 1:N)
-A doctor can write multiple prescriptions, while each prescription is written by one doctor.
-
-20. **restricts** (Prescriptions ↔ Prescription_Restriction, 1:N)
-A prescription can have multiple restrictions, while each restriction belongs to one prescription.
-
-21. **does_test** (Patients ↔ Lab_Tests, 1:N)
-A patient can take multiple lab tests, while each test belongs to one patient.
-
-22. **orders_test** (Doctors ↔ Lab_Tests, 1:N)
-A doctor can order multiple lab tests, while each lab test is ordered by one doctor.
-
-23. **conducts** (Lab_Technician ↔ Lab_Tests, 1:N)
-A lab technician can conduct multiple tests, while each test is conducted by one technician.
-
-24. **produces_test** (Lab_Tests ↔ Lab_Results, 1:N)
-A lab test can produce multiple results, while each result belongs to one test.
-
-25. **billed_for_test** (LabTest ↔ Billing, M:N)
-A billing record can include multiple lab tests, while the same lab test can appear in multiple billing records.
-
-26. **has** (Patient ↔ Medical_Record, 1:1)
-Each patient has one medical record, and each medical record belongs to one patient.
-
-27. **updates** (Doctors ↔ Medical_Record, M:N)
-Doctors can update multiple medical records, and records can be updated by multiple doctors.
-
-28. **issues** (Doctors ↔ Referral, 1:N)
-A doctor can issue multiple referrals, while each referral is issued by one doctor.
-
-29. **creates** (Doctors ↔ Medical_Report, 1:N)
-A doctor can create multiple medical reports, while each report is created by one doctor.
-
-30. **has** (Patient ↔ Medical_Report, 1:N)
-A patient can have multiple medical reports, while each report belongs to one patient.
-
-31. **updates** (Medical_Record ↔ Medical_Report, 1:N)
-A medical record can generate multiple reports, while each report is based on one record.
-
-32. **isn’t_allowed** (Medical_Record ↔ Prescription_Restriction, 1:M)
-Each medical record can contain multiple prescription restrictions, while each restriction belongs to one medical record.
-
-33. **processes** (Admin ↔ Billing, 1:N)
-An admin can process multiple billing records, while each bill is processed by one admin.
-
-34. **relates_to** (Diagnosis ↔ Medical_Record, M:N)
-Diagnoses can relate to multiple medical records, and records can include multiple diagnoses.
-
-35. **linked_to** (Prescription ↔ Medical_Record, M:N)
-Prescriptions can be linked to multiple medical records, and records can include multiple prescriptions.
-
-36. **includes_prescription** (Medical_Report ↔ Prescription, M:N)
-Medical reports can include multiple prescriptions, and prescriptions can appear in multiple reports.
-
-37. **includes_lab_results** (Medical_Report ↔ Lab_Results, M:N)
-Medical reports can include multiple lab results, and results can appear in multiple reports.
-
-38. **based_on** (Referral ↔ Appointment, 1:N)
-A referral can serve as the basis for multiple appointments, while each appointment is associated with one referral.
-
-39. **re_visits** (Appointment ↔ Appointment, 1:N)
-An appointment can result in multiple follow-up appointments, while each follow-up appointment is associated with one previous appointment.
-
-40. **documents** (Medical_Report ↔ Billing, 1:N)
-A medical report can generate multiple billing records, while each billing record is associated with one medical report.
+----
+
+== 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.