= Logical and Physical Design, DB Creation (DDL) =


== Notation ==
 * PK marked with [PK]
 * FK marked with *(TABLE)
 * UQ = unique constraint


== Relation schema ==
----

{{{
USERS & ROLES
}}}
 1. users(user_id [PK], username [UQ], password, role, first_name, last_name, is_active, patient_id*(PATIENTS), doctor_id*(DOCTORS), admin_id*(ADMIN), technician_id*(LAB_TECHNICIAN))

{{{
STAFF
}}}
 2. doctor_level(level_id [PK], level [UQ])
 3. doctor_specialization(specialization_id [PK], specialization_name [UQ])
 4. departments(department_id [PK], department_name [UQ])
 5. doctors(doctor_id [PK], first_name, last_name, email_address [UQ], level_id*(DOCTOR_LEVEL), specialization_id*(DOCTOR_SPECIALIZATION), department_id*(DEPARTMENTS))
 6. admin(admin_id [PK], username [UQ], name, lastname, email [UQ])
 7. lab_technician(technician_id [PK], username [UQ], name, lastname, email [UQ])

{{{
PATIENTS
}}}
 8. patients(patient_id [PK], first_name, last_name, email_address [UQ], date_of_birth, blood_type, gender, phone_number, embg [UQ])
 9. patient_allergies(patient_id*(PATIENTS), allergy_id*(ALLERGIES))
 10. patient_symptoms(patient_id*(PATIENTS), symptom_id*(SYMPTOMS))

{{{
APPOINTMENTS
}}}
 11. appointments(appointment_id [PK], appointment_date, appointment_time, status, patient_id*(PATIENTS), doctor_id*(DOCTORS))

{{{
CLINICAL ENTITIES
}}}
 12. allergies(allergy_id [PK], name [UQ], allergy_severity)
 13. symptoms(symptom_id [PK], name [UQ], description)
 14. diagnosis(diagnosis_id [PK], name, description, patient_id*(PATIENTS), doctor_id*(DOCTORS))
 15. diagnosis_symptoms(diagnosis_id*(DIAGNOSIS), symptom_id*(SYMPTOMS))

{{{
PROCEDURES
}}}
 16. procedures(procedure_id [PK], procedure_type, procedure_date, description, cost, doctor_id*(DOCTORS), diagnosis_id*(DIAGNOSIS))
 17. procedure_results(result_id [PK], result_description, result_date, procedure_id*(PROCEDURES))
 18. performed_procedures(performed_id [PK], procedure_date, notes, procedure_id*(PROCEDURES), doctor_id*(DOCTORS), patient_id*(PATIENTS), diagnosis_id*(DIAGNOSIS))
 19. specialization_procedures(specialization_id*(DOCTOR_SPECIALIZATION), procedure_id*(PROCEDURES))
 20. diagnosis_procedures(diagnosis_id*(DIAGNOSIS), procedure_id*(PROCEDURES))
 21. department_procedures(department_id*(DEPARTMENTS), procedure_id*(PROCEDURES))

{{{
LAB TESTS
}}}
 22. lab_tests(test_id [PK], test_name, description, cost)
 23. lab_results(result_id [PK], results, result_date, test_id*(LAB_TESTS))
 24. performed_lab_tests(performed_test_id [PK], test_date, notes, test_id*(LAB_TESTS), patient_id*(PATIENTS), doctor_id*(DOCTORS), technician_id*(LAB_TECHNICIAN))

{{{
PRESCRIPTIONS
}}}
 25. prescriptions(prescription_id [PK], medication_name)
 26. prescription_restriction(restriction_id [PK], description, prescription_id*(PRESCRIPTIONS))
 27. allergy_prescription_restrictions(allergy_id*(ALLERGIES), restriction_id*(PRESCRIPTION_RESTRICTION))

{{{
MEDICAL RECORDS
}}}
 28. medical_records(record_id [PK], patient_id*(PATIENTS))
 29. doctor_medical_records(doctor_id*(DOCTORS), record_id*(MEDICAL_RECORDS))
 30. diagnosis_medical_records(diagnosis_id*(DIAGNOSIS), record_id*(MEDICAL_RECORDS))
 31. prescription_medical_records(prescription_id*(PRESCRIPTIONS), record_id*(MEDICAL_RECORDS), dosage, frequency, duration, notes)
 32. medical_record_lab_results(record_id*(MEDICAL_RECORDS), result_id*(LAB_RESULTS))
 33. medical_record_procedure_results(record_id*(MEDICAL_RECORDS), result_id*(PROCEDURE_RESULTS))
 34. medical_record_procedures(record_id*(MEDICAL_RECORDS), procedure_id*(PROCEDURES))
 35. medical_record_symptoms(record_id*(MEDICAL_RECORDS), symptom_id*(SYMPTOMS), severity)
 36. medical_record_allergies(record_id*(MEDICAL_RECORDS), allergy_id*(ALLERGIES), reaction, severity)

{{{
REPORTS & REFERRALS
}}}
 37. medical_report(report_id [PK], description, report_date, record_id*(MEDICAL_RECORDS), doctor_id*(DOCTORS))
 38. medical_report_lab_results(report_id*(MEDICAL_REPORT), result_id*(LAB_RESULTS))
 39. referrals(referral_id [PK], reason, referral_date, record_id*(MEDICAL_RECORDS), from_doctor_id*(DOCTORS), to_doctor_id*(DOCTORS))

{{{
BILLING
}}}
 40. billing(bill_id [PK], total_cost, payment_status, payment_date, record_id*(MEDICAL_RECORDS), admin_id*(ADMIN))
 41. billing_procedures(bill_id*(BILLING), procedure_id*(PROCEDURES))
 42. billing_lab_tests(bill_id*(BILLING), test_id*(LAB_TESTS))

== DDL ==

== DML ==

== Relational Diagram ==