Changes between Version 8 and Version 9 of RelationalDesign


Ignore:
Timestamp:
09/22/26 11:04:41 (26 hours ago)
Author:
236021
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • RelationalDesign

    v8 v9  
    1414USERS & ROLES
    1515}}}
    16  1. users(user_id [PK], username [UQ], password, role, first_name, last_name, is_active, patient_id*(PATIENTS), doctor_id*(DOCTORS), admin_id*(ADMIN), lab_technician_id*(LAB_TECHNICIAN))
     16 1. users(user_id [PK], username [UQ], password, role, first_name, last_name, is_active)
    1717
    1818{{{
    … …  
    2222 3. doctor_specialization(specialization_id [PK], specialization_name [UQ])
    2323 4. departments(department_id [PK], department_name [UQ])
    24  5. doctors(doctor_id [PK], first_name, last_name, email_address [UQ], level_id*(DOCTOR_LEVEL), specialization_id*(DOCTOR_SPECIALIZATION), department_id*(DEPARTMENTS))
    25  6. admin(admin_id [PK], username [UQ], name, lastname, email [UQ])
    26  7. lab_technician(technician_id [PK], username [UQ], name, lastname, email [UQ])
     24 5. doctors(doctor_id [PK], first_name, last_name, email_address [UQ], level_id*(DOCTOR_LEVEL), specialization_id*(DOCTOR_SPECIALIZATION), department_id*(DEPARTMENTS), user_id [UQ]*(USERS))
     25 6. admin(admin_id [PK], username [UQ], name, lastname, email [UQ], user_id [UQ]*(USERS))
     26 7. lab_technician(technician_id [PK], username [UQ], name, lastname, email [UQ], user_id [UQ]*(USERS))
    2727
    2828{{{
    2929PATIENTS
    3030}}}
    31  8. patients(patient_id [PK], first_name, last_name, email_address [UQ], date_of_birth, blood_type, gender, phone_number, embg [UQ])
     31 8. patients(patient_id [PK], first_name, last_name, email_address [UQ], date_of_birth, blood_type, gender, phone_number, embg [UQ], user_id [UQ]*(USERS))
    3232 9. patient_allergies(patient_id*(PATIENTS), allergy_id*(ALLERGIES))
    3333 10. patient_symptoms(patient_id*(PATIENTS), symptom_id*(SYMPTOMS))
    … …  
    4949PROCEDURES
    5050}}}
    51  16. procedures(procedure_id [PK], procedure_type, procedure_date, description, cost, doctor_id*(DOCTORS), diagnosis_id*(DIAGNOSIS))
     51 16. procedures(procedure_id [PK], procedure_type, procedure_date, description, cost, doctor_id*(DOCTORS))
    5252 17. procedure_results(result_id [PK], result_description, result_date, procedure_id*(PROCEDURES))
    5353 18. performed_procedures(performed_id [PK], procedure_date, notes, procedure_id*(PROCEDURES), doctor_id*(DOCTORS), patient_id*(PATIENTS), diagnosis_id*(DIAGNOSIS))
    5454 19. specialization_procedures(specialization_id*(DOCTOR_SPECIALIZATION), procedure_id*(PROCEDURES))
    55  20. diagnosis_procedures(diagnosis_id*(DIAGNOSIS), procedure_id*(PROCEDURES))
    56  21. department_procedures(department_id*(DEPARTMENTS), procedure_id*(PROCEDURES))
     55 20. department_procedures(department_id*(DEPARTMENTS), procedure_id*(PROCEDURES))
    5756
    5857{{{
    5958LAB TESTS
    6059}}}
    61  22. lab_tests(test_id [PK], test_name, description, cost)
    62  23. lab_results(result_id [PK], results, result_date, test_id*(LAB_TESTS))
    63  24. performed_lab_tests(performed_test_id [PK], test_date, notes, created_at, test_id*(LAB_TESTS), patient_id*(PATIENTS), doctor_id*(DOCTORS), technician_id*(LAB_TECHNICIAN))
     60 21. lab_tests(test_id [PK], test_name, description, cost)
     61 22. lab_results(result_id [PK], results, result_date, test_id*(LAB_TESTS))
     62 23. performed_lab_tests(performed_test_id [PK], test_date, notes, created_at, test_id*(LAB_TESTS), patient_id*(PATIENTS), doctor_id*(DOCTORS), technician_id*(LAB_TECHNICIAN))
    6463
    6564{{{
    6665PRESCRIPTIONS
    6766}}}
    68  25. prescriptions(prescription_id [PK], medication_name)
    69  26. prescription_restriction(restriction_id [PK], description, prescription_id*(PRESCRIPTIONS))
    70  27. allergy_prescription_restrictions(allergy_id*(ALLERGIES), restriction_id*(PRESCRIPTION_RESTRICTION))
     67 24. prescriptions(prescription_id [PK], medication_name)
     68 25. prescription_restriction(restriction_id [PK], description, prescription_id*(PRESCRIPTIONS))
     69 26. allergy_prescription_restrictions(allergy_id*(ALLERGIES), restriction_id*(PRESCRIPTION_RESTRICTION))
    7170
    7271{{{
    7372MEDICAL RECORDS
    7473}}}
    75  28. medical_records(record_id [PK], patient_id*(PATIENTS))
    76  29. doctor_medical_records(doctor_id*(DOCTORS), record_id*(MEDICAL_RECORDS))
    77  30. diagnosis_medical_records(diagnosis_id*(DIAGNOSIS), record_id*(MEDICAL_RECORDS))
    78  31. prescription_medical_records(prescription_id*(PRESCRIPTIONS), record_id*(MEDICAL_RECORDS), dosage, frequency, duration, notes)
    79  32. medical_record_lab_results(record_id*(MEDICAL_RECORDS), result_id*(LAB_RESULTS))
    80  33. medical_record_procedures(record_id*(MEDICAL_RECORDS), procedure_id*(PROCEDURES))
    81  34. medical_record_symptoms(record_id*(MEDICAL_RECORDS), symptom_id*(SYMPTOMS), severity)
    82  35. medical_record_allergies(record_id*(MEDICAL_RECORDS), allergy_id*(ALLERGIES), reaction, severity)
     74 27. medical_records(record_id [PK], patient_id*(PATIENTS))
     75 28. diagnosis_medical_records(diagnosis_id*(DIAGNOSIS), record_id*(MEDICAL_RECORDS))
     76 29. prescription_medical_records(prescription_id*(PRESCRIPTIONS), record_id*(MEDICAL_RECORDS), dosage, frequency, duration, notes)
     77 30. medical_record_lab_results(record_id*(MEDICAL_RECORDS), result_id*(LAB_RESULTS))
     78 31. medical_record_procedures(record_id*(MEDICAL_RECORDS), procedure_id*(PROCEDURES))
     79 32. medical_record_symptoms(record_id*(MEDICAL_RECORDS), symptom_id*(SYMPTOMS), severity)
     80 33. medical_record_allergies(record_id*(MEDICAL_RECORDS), allergy_id*(ALLERGIES), reaction, severity)
    8381
    8482{{{
    8583REPORTS & REFERRALS
    8684}}}
    87  36. medical_report(report_id [PK], description, report_date, record_id*(MEDICAL_RECORDS), doctor_id*(DOCTORS))
    88  37. medical_report_lab_results(report_id*(MEDICAL_REPORT), result_id*(LAB_RESULTS))
    89  38. referrals(referral_id [PK], reason, referral_date, record_id*(MEDICAL_RECORDS), from_doctor_id*(DOCTORS), to_doctor_id*(DOCTORS))
     85 34. medical_report(report_id [PK], description, report_date, record_id*(MEDICAL_RECORDS), doctor_id*(DOCTORS))
     86 35. medical_report_lab_results(report_id*(MEDICAL_REPORT), result_id*(LAB_RESULTS))
     87 36. referrals(referral_id [PK], reason, referral_date, record_id*(MEDICAL_RECORDS), from_doctor_id*(DOCTORS), to_doctor_id*(DOCTORS))
    9088
    9189{{{
    9290BILLING
    9391}}}
    94  39. billing(bill_id [PK], total_cost, payment_status, payment_date, created_at, record_id*(MEDICAL_RECORDS), admin_id*(ADMIN))
    95  40. billing_procedures(bill_id*(BILLING), procedure_id*(PROCEDURES))
    96  41. billing_lab_tests(bill_id*(BILLING), test_id*(LAB_TESTS))
     92 37. billing(bill_id [PK], total_cost, payment_status, payment_date, created_at, record_id*(MEDICAL_RECORDS), admin_id*(ADMIN))
     93 38. billing_procedures(bill_id*(BILLING), procedure_id*(PROCEDURES))
     94 39. billing_lab_tests(bill_id*(BILLING), test_id*(LAB_TESTS))
    9795
    9896== DDL ==
    … …  
    107105== Relational Diagram ==
    108106
    109 [[Image(relational_schema_v02.jpg , width=1400)]]
     107[[Image(relational_schema_v03.jpg , width=1400)]]