Index: sql/ddl.sql =================================================================== --- sql/ddl.sql (revision c613cbf6f9fd9bd8bfca2ae505ed47ced972227a) +++ sql/ddl.sql (revision 48d2bedc68ea10341495edcd217325613e05065f) @@ -1,3 +1,2 @@ - BEGIN; @@ -11,9 +10,7 @@ DROP TABLE IF EXISTS prescription_medical_records CASCADE; DROP TABLE IF EXISTS diagnosis_medical_records CASCADE; -DROP TABLE IF EXISTS doctor_medical_records CASCADE; DROP TABLE IF EXISTS billing_lab_tests CASCADE; DROP TABLE IF EXISTS billing_procedures CASCADE; DROP TABLE IF EXISTS department_procedures CASCADE; -DROP TABLE IF EXISTS diagnosis_procedures CASCADE; DROP TABLE IF EXISTS specialization_procedures CASCADE; DROP TABLE IF EXISTS diagnosis_symptoms CASCADE; @@ -43,885 +40,884 @@ DROP TABLE IF EXISTS doctor_specialization CASCADE; DROP TABLE IF EXISTS doctor_level CASCADE; - - - +DROP TABLE IF EXISTS users CASCADE; + + +-- USERS & ROLES --------------------------------------------------------- + +CREATE TABLE users ( + user_id SERIAL PRIMARY KEY, + username VARCHAR(255) NOT NULL UNIQUE, + password VARCHAR(255) NOT NULL, + role VARCHAR(50) NOT NULL, + first_name VARCHAR(100), + last_name VARCHAR(100), + is_active BOOLEAN DEFAULT true +); + + +-- STAFF ------------------------------------------------------------------- CREATE TABLE doctor_level ( - level_id BIGINT, - level TEXT NOT NULL, - - CONSTRAINT doctor_level_PK - PRIMARY KEY (level_id), - - CONSTRAINT doctor_level_level_UQ - UNIQUE (level) + level_id BIGINT, + level TEXT NOT NULL, + + CONSTRAINT doctor_level_PK + PRIMARY KEY (level_id), + + CONSTRAINT doctor_level_level_UQ + UNIQUE (level) ); CREATE TABLE doctor_specialization ( - specialization_id BIGINT, - specialization_name TEXT NOT NULL, - - CONSTRAINT doctor_specialization_PK - PRIMARY KEY (specialization_id), - - CONSTRAINT doctor_specialization_name_UQ - UNIQUE (specialization_name) + specialization_id BIGINT, + specialization_name TEXT NOT NULL, + + CONSTRAINT doctor_specialization_PK + PRIMARY KEY (specialization_id), + + CONSTRAINT doctor_specialization_name_UQ + UNIQUE (specialization_name) ); CREATE TABLE departments ( - department_id BIGINT, - department_name TEXT NOT NULL, - - CONSTRAINT departments_PK - PRIMARY KEY (department_id), - - CONSTRAINT departments_name_UQ - UNIQUE (department_name) + department_id BIGINT, + department_name TEXT NOT NULL, + + CONSTRAINT departments_PK + PRIMARY KEY (department_id), + + CONSTRAINT departments_name_UQ + UNIQUE (department_name) ); CREATE TABLE doctors ( - doctor_id BIGINT, - - first_name TEXT NOT NULL, - last_name TEXT NOT NULL, - - email_address TEXT NOT NULL, - - level_id BIGINT NOT NULL, - specialization_id BIGINT NOT NULL, - department_id BIGINT NOT NULL, - - CONSTRAINT doctors_PK - PRIMARY KEY (doctor_id), - - CONSTRAINT doctors_email_UQ - UNIQUE (email_address), - - CONSTRAINT doctors_email_chk - CHECK (email_address LIKE '%@%'), - - CONSTRAINT doctors_level_FK - FOREIGN KEY (level_id) - REFERENCES doctor_level(level_id) - ON DELETE RESTRICT, - - CONSTRAINT doctors_specialization_FK - FOREIGN KEY (specialization_id) - REFERENCES doctor_specialization(specialization_id) - ON DELETE RESTRICT, - - CONSTRAINT doctors_department_FK - FOREIGN KEY (department_id) - REFERENCES departments(department_id) - ON DELETE RESTRICT -); - - + doctor_id BIGINT, + + first_name TEXT NOT NULL, + last_name TEXT NOT NULL, + + email_address TEXT NOT NULL, + + level_id BIGINT NOT NULL, + specialization_id BIGINT NOT NULL, + department_id BIGINT NOT NULL, + + user_id BIGINT NOT NULL, + + CONSTRAINT doctors_PK + PRIMARY KEY (doctor_id), + + CONSTRAINT doctors_email_UQ + UNIQUE (email_address), + + CONSTRAINT doctors_email_chk + CHECK (email_address LIKE '%@%'), + + CONSTRAINT doctors_user_UQ + UNIQUE (user_id), + + CONSTRAINT doctors_level_FK + FOREIGN KEY (level_id) + REFERENCES doctor_level(level_id) + ON DELETE RESTRICT, + + CONSTRAINT doctors_specialization_FK + FOREIGN KEY (specialization_id) + REFERENCES doctor_specialization(specialization_id) + ON DELETE RESTRICT, + + CONSTRAINT doctors_department_FK + FOREIGN KEY (department_id) + REFERENCES departments(department_id) + ON DELETE RESTRICT, + + CONSTRAINT doctors_user_FK + FOREIGN KEY (user_id) + REFERENCES users(user_id) + ON DELETE RESTRICT +); + +CREATE TABLE admin ( + admin_id BIGINT, + + username TEXT NOT NULL, + name TEXT NOT NULL, + lastname TEXT NOT NULL, + + email TEXT NOT NULL, + + user_id BIGINT NOT NULL, + + CONSTRAINT admin_PK + PRIMARY KEY (admin_id), + + CONSTRAINT admin_username_UQ + UNIQUE (username), + + CONSTRAINT admin_email_UQ + UNIQUE (email), + + CONSTRAINT admin_email_chk + CHECK (email LIKE '%@adminmedora%'), + + CONSTRAINT admin_user_UQ + UNIQUE (user_id), + + CONSTRAINT admin_user_FK + FOREIGN KEY (user_id) + REFERENCES users(user_id) + ON DELETE RESTRICT +); + +CREATE TABLE lab_technician ( + technician_id BIGINT, + + username TEXT NOT NULL, + name TEXT NOT NULL, + lastname TEXT NOT NULL, + + email TEXT NOT NULL, + + user_id BIGINT NOT NULL, + + CONSTRAINT lab_technician_PK + PRIMARY KEY (technician_id), + + CONSTRAINT lab_technician_username_UQ + UNIQUE (username), + + CONSTRAINT lab_technician_email_UQ + UNIQUE (email), + + CONSTRAINT lab_technician_email_chk + CHECK (email LIKE '%@labmedora%'), + + CONSTRAINT lab_technician_user_UQ + UNIQUE (user_id), + + CONSTRAINT lab_technician_user_FK + FOREIGN KEY (user_id) + REFERENCES users(user_id) + ON DELETE RESTRICT +); + + +-- PATIENTS ------------------------------------------------------------------ CREATE TABLE patients ( - patient_id BIGINT, - - first_name TEXT NOT NULL, - last_name TEXT NOT NULL, - - email_address TEXT, - date_of_birth DATE NOT NULL, - - blood_type TEXT, - gender TEXT, - - phone_number TEXT, - embg TEXT NOT NULL, - - CONSTRAINT patients_PK - PRIMARY KEY (patient_id), - - CONSTRAINT patients_email_UQ - UNIQUE (email_address), - - CONSTRAINT patients_embg_UQ - UNIQUE (embg), - - CONSTRAINT patients_gender_chk - CHECK (gender IN ('MALE', 'FEMALE')), - - CONSTRAINT patients_blood_type_chk - CHECK ( - blood_type IN ( - 'A+', 'A-', - 'B+', 'B-', - 'AB+', 'AB-', - 'O+', 'O-' - ) - ) -); - -CREATE TABLE admin ( - admin_id BIGINT, - - username TEXT NOT NULL, - name TEXT NOT NULL, - lastname TEXT NOT NULL, - - email TEXT NOT NULL, - - CONSTRAINT admin_PK - PRIMARY KEY (admin_id), - - CONSTRAINT admin_username_UQ - UNIQUE (username), - - CONSTRAINT admin_email_UQ - UNIQUE (email), - - CONSTRAINT admin_email_chk - CHECK (email LIKE '%@adminmedora%') -); - -CREATE TABLE lab_technician ( - technician_id BIGINT, - - username TEXT NOT NULL, - name TEXT NOT NULL, - lastname TEXT NOT NULL, - - email TEXT NOT NULL, - - CONSTRAINT lab_technician_PK - PRIMARY KEY (technician_id), - - CONSTRAINT lab_technician_username_UQ - UNIQUE (username), - - CONSTRAINT lab_technician_email_UQ - UNIQUE (email), - - CONSTRAINT lab_technician_email_chk - CHECK (email LIKE '%@labmedora%') -); - - + patient_id BIGINT, + + first_name TEXT NOT NULL, + last_name TEXT NOT NULL, + + email_address TEXT, + date_of_birth DATE NOT NULL, + + blood_type TEXT, + gender TEXT, + + phone_number TEXT, + embg TEXT NOT NULL, + + user_id BIGINT NOT NULL, + + CONSTRAINT patients_PK + PRIMARY KEY (patient_id), + + CONSTRAINT patients_email_UQ + UNIQUE (email_address), + + CONSTRAINT patients_embg_UQ + UNIQUE (embg), + + CONSTRAINT patients_embg_format_chk + CHECK (embg ~ '^[0-9]{13}$'), + + CONSTRAINT patients_gender_chk + CHECK (gender IN ('MALE', 'FEMALE')), + + CONSTRAINT patients_blood_type_chk + CHECK ( + blood_type IN ( + 'A+', 'A-', + 'B+', 'B-', + 'AB+', 'AB-', + 'O+', 'O-' + ) + ), + + CONSTRAINT patients_user_UQ + UNIQUE (user_id), + + CONSTRAINT patients_user_FK + FOREIGN KEY (user_id) + REFERENCES users(user_id) + ON DELETE RESTRICT +); + + +-- APPOINTMENTS -------------------------------------------------------------- CREATE TABLE appointments ( - appointment_id BIGINT, - - appointment_date DATE NOT NULL, - appointment_time TIME NOT NULL, - - status TEXT NOT NULL, - - patient_id BIGINT NOT NULL, - doctor_id BIGINT NOT NULL, - - CONSTRAINT appointments_PK - PRIMARY KEY (appointment_id), - - CONSTRAINT appointments_status_chk - CHECK ( - status IN ( - 'SCHEDULED', - 'COMPLETED', - 'CANCELLED', - 'IN_PROGRESS' - ) - ), - - CONSTRAINT appointments_patient_FK - FOREIGN KEY (patient_id) - REFERENCES patients(patient_id) - ON DELETE RESTRICT, - - CONSTRAINT appointments_doctor_FK - FOREIGN KEY (doctor_id) - REFERENCES doctors(doctor_id) - ON DELETE RESTRICT + appointment_id BIGINT, + + appointment_date DATE NOT NULL, + appointment_time TIME NOT NULL, + + status TEXT NOT NULL, + + patient_id BIGINT NOT NULL, + doctor_id BIGINT NOT NULL, + + CONSTRAINT appointments_PK + PRIMARY KEY (appointment_id), + + CONSTRAINT appointments_status_chk + CHECK ( + status IN ( + 'SCHEDULED', + 'COMPLETED', + 'CANCELLED', + 'IN_PROGRESS' + ) + ), + + CONSTRAINT appointments_patient_FK + FOREIGN KEY (patient_id) + REFERENCES patients(patient_id) + ON DELETE RESTRICT, + + CONSTRAINT appointments_doctor_FK + FOREIGN KEY (doctor_id) + REFERENCES doctors(doctor_id) + ON DELETE RESTRICT +); + + +-- CLINICAL ENTITIES ----------------------------------------------------- + +CREATE TABLE allergies ( + allergy_id BIGINT, + + name TEXT NOT NULL, + allergy_severity TEXT NOT NULL, + + CONSTRAINT allergies_PK + PRIMARY KEY (allergy_id), + + CONSTRAINT allergies_name_UQ + UNIQUE (name), + + CONSTRAINT allergies_name_chk + CHECK (length(trim(name)) > 0), + + CONSTRAINT allergies_severity_chk + CHECK (allergy_severity IN ('LOW', 'MEDIUM', 'HIGH', 'CRITICAL')) +); + +CREATE TABLE symptoms ( + symptom_id BIGINT, + + name TEXT NOT NULL, + description TEXT, + + CONSTRAINT symptoms_PK + PRIMARY KEY (symptom_id), + + CONSTRAINT symptoms_name_UQ + UNIQUE (name), + + CONSTRAINT symptoms_name_chk + CHECK (length(trim(name)) > 0), + + CONSTRAINT symptoms_description_chk + CHECK ( + description IS NULL + OR length(trim(description)) > 0 + ) ); CREATE TABLE diagnosis ( - diagnosis_id BIGINT, - - name TEXT NOT NULL, - description TEXT, - - patient_id BIGINT NOT NULL, - doctor_id BIGINT NOT NULL, - - CONSTRAINT diagnosis_PK - PRIMARY KEY (diagnosis_id), - - CONSTRAINT diagnosis_patient_FK - FOREIGN KEY (patient_id) - REFERENCES patients(patient_id) - ON DELETE RESTRICT, - - CONSTRAINT diagnosis_doctor_FK - FOREIGN KEY (doctor_id) - REFERENCES doctors(doctor_id) - ON DELETE RESTRICT -); - - - + diagnosis_id BIGINT, + + name TEXT NOT NULL, + description TEXT, + + patient_id BIGINT NOT NULL, + doctor_id BIGINT NOT NULL, + + CONSTRAINT diagnosis_PK + PRIMARY KEY (diagnosis_id), + + CONSTRAINT diagnosis_patient_FK + FOREIGN KEY (patient_id) + REFERENCES patients(patient_id) + ON DELETE RESTRICT, + + CONSTRAINT diagnosis_doctor_FK + FOREIGN KEY (doctor_id) + REFERENCES doctors(doctor_id) + ON DELETE RESTRICT +); + +CREATE TABLE diagnosis_symptoms ( + diagnosis_id BIGINT NOT NULL, + symptom_id BIGINT NOT NULL, + + CONSTRAINT diagnosis_symptoms_PK + PRIMARY KEY (diagnosis_id, symptom_id), + + CONSTRAINT diagnosis_symptoms_diagnosis_FK + FOREIGN KEY (diagnosis_id) + REFERENCES diagnosis(diagnosis_id) + ON DELETE RESTRICT, + + CONSTRAINT diagnosis_symptoms_symptom_FK + FOREIGN KEY (symptom_id) + REFERENCES symptoms(symptom_id) + ON DELETE RESTRICT +); + + +-- PROCEDURES -------------------------------------------------------------- CREATE TABLE procedures ( - procedure_id BIGINT, - - procedure_type TEXT NOT NULL, - procedure_date DATE NOT NULL, - - description TEXT, - cost DECIMAL NOT NULL, - - doctor_id BIGINT NOT NULL, - diagnosis_id BIGINT NOT NULL, - - CONSTRAINT procedures_PK - PRIMARY KEY (procedure_id), - - CONSTRAINT procedures_cost_chk - CHECK (cost >= 0), - - CONSTRAINT procedures_type_chk - CHECK (length(trim(procedure_type)) > 0), - - CONSTRAINT procedures_doctor_FK - FOREIGN KEY (doctor_id) - REFERENCES doctors(doctor_id) - ON DELETE RESTRICT, - - CONSTRAINT procedures_diagnosis_FK - FOREIGN KEY (diagnosis_id) - REFERENCES diagnosis(diagnosis_id) - ON DELETE RESTRICT -); - - - + procedure_id BIGINT, + + procedure_type TEXT NOT NULL, + procedure_date DATE NOT NULL, + + description TEXT, + cost DECIMAL NOT NULL, + + doctor_id BIGINT NOT NULL, + + CONSTRAINT procedures_PK + PRIMARY KEY (procedure_id), + + CONSTRAINT procedures_cost_chk + CHECK (cost >= 0), + + CONSTRAINT procedures_type_chk + CHECK (length(trim(procedure_type)) > 0), + + CONSTRAINT procedures_doctor_FK + FOREIGN KEY (doctor_id) + REFERENCES doctors(doctor_id) + ON DELETE RESTRICT +); CREATE TABLE procedure_results ( - result_id BIGINT, - - result_description TEXT, - result_date DATE NOT NULL, - - procedure_id BIGINT NOT NULL, - - CONSTRAINT procedure_results_PK - PRIMARY KEY (result_id), - - CONSTRAINT procedure_results_description_chk - CHECK ( - result_description IS NULL - OR length(trim(result_description)) > 0 - ), - - CONSTRAINT procedure_results_procedure_FK - FOREIGN KEY (procedure_id) - REFERENCES procedures(procedure_id) - ON DELETE RESTRICT -); + result_id BIGINT, + + result_description TEXT, + result_date DATE NOT NULL, + + procedure_id BIGINT NOT NULL, + + CONSTRAINT procedure_results_PK + PRIMARY KEY (result_id), + + CONSTRAINT procedure_results_description_chk + CHECK ( + result_description IS NULL + OR length(trim(result_description)) > 0 + ), + + CONSTRAINT procedure_results_procedure_FK + FOREIGN KEY (procedure_id) + REFERENCES procedures(procedure_id) + ON DELETE RESTRICT +); + +CREATE TABLE specialization_procedures ( + specialization_id BIGINT NOT NULL, + procedure_id BIGINT NOT NULL, + + CONSTRAINT specialization_procedures_PK + PRIMARY KEY (specialization_id, procedure_id), + + CONSTRAINT specialization_procedures_specialization_FK + FOREIGN KEY (specialization_id) + REFERENCES doctor_specialization(specialization_id) + ON DELETE RESTRICT, + + CONSTRAINT specialization_procedures_procedure_FK + FOREIGN KEY (procedure_id) + REFERENCES procedures(procedure_id) + ON DELETE RESTRICT +); + +CREATE TABLE department_procedures ( + department_id BIGINT NOT NULL, + procedure_id BIGINT NOT NULL, + + CONSTRAINT department_procedures_PK + PRIMARY KEY (department_id, procedure_id), + + CONSTRAINT department_procedures_department_FK + FOREIGN KEY (department_id) + REFERENCES departments(department_id) + ON DELETE RESTRICT, + + CONSTRAINT department_procedures_procedure_FK + FOREIGN KEY (procedure_id) + REFERENCES procedures(procedure_id) + ON DELETE RESTRICT +); + + +-- LAB TESTS ----------------------------------------------------------------- + +CREATE TABLE lab_tests ( + test_id BIGINT, + + test_name TEXT NOT NULL, + description TEXT, + cost DECIMAL NOT NULL, + + CONSTRAINT lab_tests_PK + PRIMARY KEY (test_id), + + CONSTRAINT lab_tests_cost_chk + CHECK (cost >= 0), + + CONSTRAINT lab_tests_name_chk + CHECK (length(trim(test_name)) > 0) +); + +CREATE TABLE lab_results ( + result_id BIGINT, + + results TEXT NOT NULL, + result_date DATE NOT NULL, + + test_id BIGINT NOT NULL, + + CONSTRAINT lab_results_PK + PRIMARY KEY (result_id), + + CONSTRAINT lab_results_results_chk + CHECK (length(trim(results)) > 0), + + CONSTRAINT lab_results_date_chk + CHECK (result_date <= CURRENT_DATE), + + CONSTRAINT lab_results_test_FK + FOREIGN KEY (test_id) + REFERENCES lab_tests(test_id) + ON DELETE RESTRICT +); + + +-- PRESCRIPTIONS --------------------------------------------------------- CREATE TABLE prescriptions ( - prescription_id BIGINT, - - medication_name TEXT NOT NULL, - - CONSTRAINT prescriptions_PK - PRIMARY KEY (prescription_id) + prescription_id BIGINT, + + medication_name TEXT NOT NULL, + + CONSTRAINT prescriptions_PK + PRIMARY KEY (prescription_id) ); CREATE TABLE prescription_restriction ( - restriction_id BIGINT, - - description TEXT NOT NULL, - - prescription_id BIGINT NOT NULL, - - CONSTRAINT prescription_restriction_PK - PRIMARY KEY (restriction_id), - - CONSTRAINT prescription_restriction_desc_chk - CHECK (length(trim(description)) > 0), - - CONSTRAINT prescription_restriction_prescription_FK - FOREIGN KEY (prescription_id) - REFERENCES prescriptions(prescription_id) - ON DELETE RESTRICT -); - - -CREATE TABLE lab_tests ( - test_id BIGINT, - - test_name TEXT NOT NULL, - description TEXT, - cost DECIMAL NOT NULL, - - CONSTRAINT lab_tests_PK - PRIMARY KEY (test_id), - - CONSTRAINT lab_tests_cost_chk - CHECK (cost >= 0), - - CONSTRAINT lab_tests_name_chk - CHECK (length(trim(test_name)) > 0) -); - - -CREATE TABLE lab_results ( - result_id BIGINT, - - results TEXT NOT NULL, - result_date DATE NOT NULL, - - test_id BIGINT NOT NULL, - - CONSTRAINT lab_results_PK - PRIMARY KEY (result_id), - - CONSTRAINT lab_results_results_chk - CHECK (length(trim(results)) > 0), - - CONSTRAINT lab_results_date_chk - CHECK (result_date <= CURRENT_DATE), - - CONSTRAINT lab_results_test_FK - FOREIGN KEY (test_id) - REFERENCES lab_tests(test_id) - ON DELETE RESTRICT -); + restriction_id BIGINT, + + description TEXT NOT NULL, + + prescription_id BIGINT NOT NULL, + + CONSTRAINT prescription_restriction_PK + PRIMARY KEY (restriction_id), + + CONSTRAINT prescription_restriction_desc_chk + CHECK (length(trim(description)) > 0), + + CONSTRAINT prescription_restriction_prescription_FK + FOREIGN KEY (prescription_id) + REFERENCES prescriptions(prescription_id) + ON DELETE RESTRICT +); + +CREATE TABLE allergy_prescription_restrictions ( + allergy_id BIGINT NOT NULL, + restriction_id BIGINT NOT NULL, + + CONSTRAINT allergy_prescription_restrictions_PK + PRIMARY KEY (allergy_id, restriction_id), + + CONSTRAINT allergy_prescription_restrictions_allergy_FK + FOREIGN KEY (allergy_id) + REFERENCES allergies(allergy_id) + ON DELETE RESTRICT, + + CONSTRAINT allergy_prescription_restrictions_restriction_FK + FOREIGN KEY (restriction_id) + REFERENCES prescription_restriction(restriction_id) + ON DELETE RESTRICT +); + + +-- PATIENTS <-> ALLERGIES / SYMPTOMS ----------------------------------------- + +CREATE TABLE patient_allergies ( + patient_id BIGINT NOT NULL, + allergy_id BIGINT NOT NULL, + + CONSTRAINT patient_allergies_PK + PRIMARY KEY (patient_id, allergy_id), + + CONSTRAINT patient_allergies_patient_FK + FOREIGN KEY (patient_id) + REFERENCES patients(patient_id) + ON DELETE RESTRICT, + + CONSTRAINT patient_allergies_allergy_FK + FOREIGN KEY (allergy_id) + REFERENCES allergies(allergy_id) + ON DELETE RESTRICT +); + +CREATE TABLE patient_symptoms ( + patient_id BIGINT NOT NULL, + symptom_id BIGINT NOT NULL, + + CONSTRAINT patient_symptoms_PK + PRIMARY KEY (patient_id, symptom_id), + + CONSTRAINT patient_symptoms_patient_FK + FOREIGN KEY (patient_id) + REFERENCES patients(patient_id) + ON DELETE RESTRICT, + + CONSTRAINT patient_symptoms_symptom_FK + FOREIGN KEY (symptom_id) + REFERENCES symptoms(symptom_id) + ON DELETE RESTRICT +); + + +-- MEDICAL RECORDS ------------------------------------------------------- CREATE TABLE medical_records ( - record_id BIGINT PRIMARY KEY, - - patient_id BIGINT NOT NULL, - - - FOREIGN KEY (patient_id) - REFERENCES patients(patient_id) - ON DELETE RESTRICT -); - - - - -CREATE TABLE allergies ( - allergy_id BIGINT, - - name TEXT NOT NULL, - allergy_severity TEXT NOT NULL, - - CONSTRAINT allergies_PK - PRIMARY KEY (allergy_id), - - CONSTRAINT allergies_name_UQ - UNIQUE (name), - - CONSTRAINT allergies_name_chk - CHECK (length(trim(name)) > 0), - - CONSTRAINT allergies_severity_chk - CHECK (allergy_severity IN ('LOW', 'MEDIUM', 'HIGH', 'CRITICAL')) -); - - -CREATE TABLE symptoms ( - symptom_id BIGINT, - - name TEXT NOT NULL, - description TEXT, - - CONSTRAINT symptoms_PK - PRIMARY KEY (symptom_id), - - CONSTRAINT symptoms_name_UQ - UNIQUE (name), - - CONSTRAINT symptoms_name_chk - CHECK (length(trim(name)) > 0), - - CONSTRAINT symptoms_description_chk - CHECK ( - description IS NULL - OR length(trim(description)) > 0 - ) -); - + record_id BIGINT PRIMARY KEY, + + patient_id BIGINT NOT NULL, + + FOREIGN KEY (patient_id) + REFERENCES patients(patient_id) + ON DELETE RESTRICT +); + +CREATE TABLE diagnosis_medical_records ( + diagnosis_id BIGINT NOT NULL, + record_id BIGINT NOT NULL, + + CONSTRAINT diagnosis_medical_records_PK + PRIMARY KEY (diagnosis_id, record_id), + + CONSTRAINT diagnosis_medical_records_diagnosis_FK + FOREIGN KEY (diagnosis_id) + REFERENCES diagnosis(diagnosis_id) + ON DELETE RESTRICT, + + CONSTRAINT diagnosis_medical_records_record_FK + FOREIGN KEY (record_id) + REFERENCES medical_records(record_id) + ON DELETE RESTRICT +); + +CREATE TABLE prescription_medical_records ( + prescription_id BIGINT NOT NULL, + record_id BIGINT NOT NULL, + + dosage TEXT NOT NULL, + frequency TEXT NOT NULL, + duration TEXT NOT NULL, + + notes TEXT, + + CONSTRAINT prescription_medical_records_PK + PRIMARY KEY (prescription_id, record_id), + + CONSTRAINT prescription_medical_records_prescription_FK + FOREIGN KEY (prescription_id) + REFERENCES prescriptions(prescription_id) + ON DELETE RESTRICT, + + CONSTRAINT prescription_medical_records_record_FK + FOREIGN KEY (record_id) + REFERENCES medical_records(record_id) + ON DELETE RESTRICT +); + +CREATE TABLE medical_record_lab_results ( + record_id BIGINT NOT NULL, + result_id BIGINT NOT NULL, + + PRIMARY KEY (record_id, result_id), + + FOREIGN KEY (record_id) + REFERENCES medical_records(record_id) + ON DELETE RESTRICT, + + FOREIGN KEY (result_id) + REFERENCES lab_results(result_id) + ON DELETE RESTRICT +); + +CREATE TABLE medical_record_procedures ( + record_id BIGINT NOT NULL, + procedure_id BIGINT NOT NULL, + + PRIMARY KEY (record_id, procedure_id), + + FOREIGN KEY (record_id) + REFERENCES medical_records(record_id) + ON DELETE RESTRICT, + + FOREIGN KEY (procedure_id) + REFERENCES procedures(procedure_id) + ON DELETE RESTRICT +); + +CREATE TABLE medical_record_symptoms ( + record_id BIGINT NOT NULL, + symptom_id BIGINT NOT NULL, + severity TEXT, + + PRIMARY KEY (record_id, symptom_id), + + FOREIGN KEY (record_id) + REFERENCES medical_records(record_id) + ON DELETE RESTRICT, + + FOREIGN KEY (symptom_id) + REFERENCES symptoms(symptom_id) + ON DELETE RESTRICT +); + +CREATE TABLE medical_record_allergies ( + record_id BIGINT NOT NULL, + allergy_id BIGINT NOT NULL, + reaction TEXT, + severity TEXT, + + PRIMARY KEY (record_id, allergy_id), + + FOREIGN KEY (record_id) + REFERENCES medical_records(record_id) + ON DELETE RESTRICT, + + FOREIGN KEY (allergy_id) + REFERENCES allergies(allergy_id) + ON DELETE RESTRICT +); + + +-- REPORTS & REFERRALS ----------------------------------------------------- CREATE TABLE referrals ( - - referral_id BIGINT PRIMARY KEY, - - reason TEXT NOT NULL, - referral_date DATE NOT NULL, - - record_id BIGINT NOT NULL, - from_doctor_id BIGINT NOT NULL, - to_doctor_id BIGINT not null, - - CONSTRAINT referrals_reason_chk - CHECK (length(trim(reason)) > 0), - - FOREIGN KEY (record_id) - REFERENCES medical_records(record_id) - ON DELETE RESTRICT, - - FOREIGN KEY (from_doctor_id) - REFERENCES doctors(doctor_id) - ON DELETE RESTRICT, - - FOREIGN KEY (to_doctor_id) - REFERENCES doctors(doctor_id) - ON DELETE RESTRICT + referral_id BIGINT PRIMARY KEY, + + reason TEXT NOT NULL, + referral_date DATE NOT NULL, + + record_id BIGINT NOT NULL, + from_doctor_id BIGINT NOT NULL, + to_doctor_id BIGINT NOT NULL, + + CONSTRAINT referrals_reason_chk + CHECK (length(trim(reason)) > 0), + + FOREIGN KEY (record_id) + REFERENCES medical_records(record_id) + ON DELETE RESTRICT, + + FOREIGN KEY (from_doctor_id) + REFERENCES doctors(doctor_id) + ON DELETE RESTRICT, + + FOREIGN KEY (to_doctor_id) + REFERENCES doctors(doctor_id) + ON DELETE RESTRICT ); CREATE TABLE medical_report ( - report_id BIGINT PRIMARY KEY, - - description TEXT NOT NULL, - report_date DATE NOT NULL, - - record_id BIGINT NOT NULL, - doctor_id BIGINT NOT NULL, - - FOREIGN KEY (record_id) - REFERENCES medical_records(record_id) - ON DELETE RESTRICT, - - FOREIGN KEY (doctor_id) - REFERENCES doctors(doctor_id) - ON DELETE RESTRICT -); - + report_id BIGINT PRIMARY KEY, + + description TEXT NOT NULL, + report_date DATE NOT NULL, + + record_id BIGINT NOT NULL, + doctor_id BIGINT NOT NULL, + + FOREIGN KEY (record_id) + REFERENCES medical_records(record_id) + ON DELETE RESTRICT, + + FOREIGN KEY (doctor_id) + REFERENCES doctors(doctor_id) + ON DELETE RESTRICT +); + +CREATE TABLE medical_report_lab_results ( + report_id BIGINT NOT NULL, + result_id BIGINT NOT NULL, + + CONSTRAINT medical_report_lab_results_PK + PRIMARY KEY (report_id, result_id), + + CONSTRAINT medical_report_lab_results_report_FK + FOREIGN KEY (report_id) + REFERENCES medical_report(report_id) + ON DELETE RESTRICT, + + CONSTRAINT medical_report_lab_results_result_FK + FOREIGN KEY (result_id) + REFERENCES lab_results(result_id) + ON DELETE RESTRICT +); + + +-- PERFORMED PROCEDURES / LAB TESTS ----------------------------------------- + +CREATE TABLE performed_procedures ( + performed_id BIGINT, + + procedure_id BIGINT NOT NULL, + doctor_id BIGINT NOT NULL, + patient_id BIGINT NOT NULL, + diagnosis_id BIGINT, + + procedure_date DATE NOT NULL, + notes TEXT, + + CONSTRAINT performed_procedures_PK + PRIMARY KEY (performed_id), + + CONSTRAINT performed_procedures_procedure_FK + FOREIGN KEY (procedure_id) + REFERENCES procedures(procedure_id) + ON DELETE RESTRICT, + + CONSTRAINT performed_procedures_doctor_FK + FOREIGN KEY (doctor_id) + REFERENCES doctors(doctor_id) + ON DELETE RESTRICT, + + CONSTRAINT performed_procedures_patient_FK + FOREIGN KEY (patient_id) + REFERENCES patients(patient_id) + ON DELETE RESTRICT, + + CONSTRAINT performed_procedures_diagnosis_FK + FOREIGN KEY (diagnosis_id) + REFERENCES diagnosis(diagnosis_id) + ON DELETE RESTRICT +); + +CREATE TABLE performed_lab_tests ( + performed_test_id BIGINT, + + test_id BIGINT NOT NULL, + patient_id BIGINT NOT NULL, + doctor_id BIGINT NOT NULL, + technician_id BIGINT NOT NULL, + + test_date DATE NOT NULL, + notes TEXT, + created_at TIMESTAMP DEFAULT now(), + + CONSTRAINT performed_lab_tests_PK + PRIMARY KEY (performed_test_id), + + CONSTRAINT performed_lab_tests_test_FK + FOREIGN KEY (test_id) + REFERENCES lab_tests(test_id) + ON DELETE RESTRICT, + + CONSTRAINT performed_lab_tests_patient_FK + FOREIGN KEY (patient_id) + REFERENCES patients(patient_id) + ON DELETE RESTRICT, + + CONSTRAINT performed_lab_tests_doctor_FK + FOREIGN KEY (doctor_id) + REFERENCES doctors(doctor_id) + ON DELETE RESTRICT, + + CONSTRAINT performed_lab_tests_technician_FK + FOREIGN KEY (technician_id) + REFERENCES lab_technician(technician_id) + ON DELETE RESTRICT +); + + +-- BILLING ------------------------------------------------------------------- CREATE TABLE billing ( - bill_id BIGINT PRIMARY KEY, - - total_cost DECIMAL NOT NULL, - - payment_status TEXT NOT NULL, - payment_date DATE, - - record_id BIGINT NOT NULL, - admin_id BIGINT NOT NULL, - - CONSTRAINT billing_cost_chk CHECK (total_cost >= 0), - CONSTRAINT billing_status_chk - CHECK (payment_status IN ('PENDING', 'PAID', 'CANCELLED')), - - FOREIGN KEY (record_id) - REFERENCES medical_records(record_id) - ON DELETE RESTRICT, - - FOREIGN KEY (admin_id) - REFERENCES admin(admin_id) - ON DELETE RESTRICT -); - - - - -CREATE TABLE patient_allergies ( - patient_id BIGINT NOT NULL, - allergy_id BIGINT NOT NULL, - - CONSTRAINT patient_allergies_PK - PRIMARY KEY (patient_id, allergy_id), - - CONSTRAINT patient_allergies_patient_FK - FOREIGN KEY (patient_id) - REFERENCES patients(patient_id) - ON DELETE RESTRICT, - - CONSTRAINT patient_allergies_allergy_FK - FOREIGN KEY (allergy_id) - REFERENCES allergies(allergy_id) - ON DELETE RESTRICT -); - - - -CREATE TABLE allergy_prescription_restrictions ( - allergy_id BIGINT NOT NULL, - restriction_id BIGINT NOT NULL, - - CONSTRAINT allergy_prescription_restrictions_PK - PRIMARY KEY (allergy_id, restriction_id), - - CONSTRAINT allergy_prescription_restrictions_allergy_FK - FOREIGN KEY (allergy_id) - REFERENCES allergies(allergy_id) - ON DELETE RESTRICT, - - CONSTRAINT allergy_prescription_restrictions_restriction_FK - FOREIGN KEY (restriction_id) - REFERENCES prescription_restriction(restriction_id) - ON DELETE RESTRICT -); - - - -CREATE TABLE patient_symptoms ( - patient_id BIGINT NOT NULL, - symptom_id BIGINT NOT NULL, - - CONSTRAINT patient_symptoms_PK - PRIMARY KEY (patient_id, symptom_id), - - CONSTRAINT patient_symptoms_patient_FK - FOREIGN KEY (patient_id) - REFERENCES patients(patient_id) - ON DELETE RESTRICT, - - CONSTRAINT patient_symptoms_symptom_FK - FOREIGN KEY (symptom_id) - REFERENCES symptoms(symptom_id) - ON DELETE RESTRICT -); - - - - -CREATE TABLE diagnosis_symptoms ( - diagnosis_id BIGINT NOT NULL, - symptom_id BIGINT NOT NULL, - - CONSTRAINT diagnosis_symptoms_PK - PRIMARY KEY (diagnosis_id, symptom_id), - - CONSTRAINT diagnosis_symptoms_diagnosis_FK - FOREIGN KEY (diagnosis_id) - REFERENCES diagnosis(diagnosis_id) - ON DELETE RESTRICT, - - CONSTRAINT diagnosis_symptoms_symptom_FK - FOREIGN KEY (symptom_id) - REFERENCES symptoms(symptom_id) - ON DELETE RESTRICT -); - -CREATE TABLE specialization_procedures ( - specialization_id BIGINT NOT NULL, - procedure_id BIGINT NOT NULL, - - CONSTRAINT specialization_procedures_PK - PRIMARY KEY (specialization_id, procedure_id), - - CONSTRAINT specialization_procedures_specialization_FK - FOREIGN KEY (specialization_id) - REFERENCES doctor_specialization(specialization_id) - ON DELETE RESTRICT, - - CONSTRAINT specialization_procedures_procedure_FK - FOREIGN KEY (procedure_id) - REFERENCES procedures(procedure_id) - ON DELETE RESTRICT -); - - - -CREATE TABLE diagnosis_procedures ( - diagnosis_id BIGINT NOT NULL, - procedure_id BIGINT NOT NULL, - - CONSTRAINT diagnosis_procedures_PK - PRIMARY KEY (diagnosis_id, procedure_id), - - CONSTRAINT diagnosis_procedures_diagnosis_FK - FOREIGN KEY (diagnosis_id) - REFERENCES diagnosis(diagnosis_id) - ON DELETE RESTRICT, - - CONSTRAINT diagnosis_procedures_procedure_FK - FOREIGN KEY (procedure_id) - REFERENCES procedures(procedure_id) - ON DELETE RESTRICT -); - -CREATE TABLE department_procedures ( - department_id BIGINT NOT NULL , - procedure_id BIGINT NOT NULL, - - CONSTRAINT department_procedures_PK - PRIMARY KEY (department_id, procedure_id), - - CONSTRAINT department_procedures_department_FK - FOREIGN KEY (department_id) - REFERENCES departments(department_id) - ON DELETE RESTRICT, - - CONSTRAINT department_procedures_procedure_FK - FOREIGN KEY (procedure_id) - REFERENCES procedures(procedure_id) - ON DELETE RESTRICT -); - + bill_id BIGINT PRIMARY KEY, + + total_cost DECIMAL NOT NULL, + + payment_status TEXT NOT NULL, + payment_date DATE, + + created_at TIMESTAMP DEFAULT now(), + + record_id BIGINT NOT NULL, + admin_id BIGINT NOT NULL, + + CONSTRAINT billing_cost_chk CHECK (total_cost >= 0), + CONSTRAINT billing_status_chk + CHECK (payment_status IN ('PENDING', 'PAID', 'CANCELLED')), + + FOREIGN KEY (record_id) + REFERENCES medical_records(record_id) + ON DELETE RESTRICT, + + FOREIGN KEY (admin_id) + REFERENCES admin(admin_id) + ON DELETE RESTRICT +); CREATE TABLE billing_procedures ( - bill_id BIGINT NOT NULL, - procedure_id BIGINT NOT NULL, - - CONSTRAINT billing_procedures_PK - PRIMARY KEY (bill_id, procedure_id), - - CONSTRAINT billing_procedures_bill_FK - FOREIGN KEY (bill_id) - REFERENCES billing(bill_id) - ON DELETE RESTRICT, - - CONSTRAINT billing_procedures_procedure_FK - FOREIGN KEY (procedure_id) - REFERENCES procedures(procedure_id) - ON DELETE RESTRICT -); - - - + bill_id BIGINT NOT NULL, + procedure_id BIGINT NOT NULL, + + CONSTRAINT billing_procedures_PK + PRIMARY KEY (bill_id, procedure_id), + + CONSTRAINT billing_procedures_bill_FK + FOREIGN KEY (bill_id) + REFERENCES billing(bill_id) + ON DELETE RESTRICT, + + CONSTRAINT billing_procedures_procedure_FK + FOREIGN KEY (procedure_id) + REFERENCES procedures(procedure_id) + ON DELETE RESTRICT +); CREATE TABLE billing_lab_tests ( - bill_id BIGINT NOT NULL, - test_id BIGINT NOT NULL, - - CONSTRAINT billing_lab_tests_PK - PRIMARY KEY (bill_id, test_id), - - CONSTRAINT billing_lab_tests_bill_FK - FOREIGN KEY (bill_id) - REFERENCES billing(bill_id) - ON DELETE RESTRICT, - - CONSTRAINT billing_lab_tests_test_FK - FOREIGN KEY (test_id) - REFERENCES lab_tests(test_id) - ON DELETE RESTRICT -); - - - -CREATE TABLE doctor_medical_records ( - doctor_id BIGINT NOT NULL, - record_id BIGINT NOT NULL, - - CONSTRAINT doctor_medical_records_PK - PRIMARY KEY (doctor_id, record_id), - - CONSTRAINT doctor_medical_records_doctor_FK - FOREIGN KEY (doctor_id) - REFERENCES doctors(doctor_id) - ON DELETE RESTRICT, - - CONSTRAINT doctor_medical_records_record_FK - FOREIGN KEY (record_id) - REFERENCES medical_records(record_id) - ON DELETE RESTRICT -); - - - -CREATE TABLE diagnosis_medical_records ( - diagnosis_id BIGINT NOT NULL, - record_id BIGINT NOT NULL, - - CONSTRAINT diagnosis_medical_records_PK - PRIMARY KEY (diagnosis_id, record_id), - - CONSTRAINT diagnosis_medical_records_diagnosis_FK - FOREIGN KEY (diagnosis_id) - REFERENCES diagnosis(diagnosis_id) - ON DELETE RESTRICT, - - CONSTRAINT diagnosis_medical_records_record_FK - FOREIGN KEY (record_id) - REFERENCES medical_records(record_id) - ON DELETE RESTRICT -); - - -CREATE TABLE prescription_medical_records ( - prescription_id BIGINT NOT NULL, - record_id BIGINT NOT NULL, - - dosage TEXT NOT NULL, - frequency TEXT NOT NULL, - duration TEXT NOT NULL, - - notes TEXT, - - CONSTRAINT prescription_medical_records_PK - PRIMARY KEY (prescription_id, record_id), - - CONSTRAINT prescription_medical_records_prescription_FK - FOREIGN KEY (prescription_id) - REFERENCES prescriptions(prescription_id) - ON DELETE RESTRICT, - - CONSTRAINT prescription_medical_records_record_FK - FOREIGN KEY (record_id) - REFERENCES medical_records(record_id) - ON DELETE RESTRICT -); - - - -CREATE TABLE medical_report_lab_results ( - report_id BIGINT NOT NULL, - result_id BIGINT NOT NULL, - - CONSTRAINT medical_report_lab_results_PK - PRIMARY KEY (report_id, result_id), - - CONSTRAINT medical_report_lab_results_report_FK - FOREIGN KEY (report_id) - REFERENCES medical_report(report_id) - ON DELETE RESTRICT, - - CONSTRAINT medical_report_lab_results_result_FK - FOREIGN KEY (result_id) - REFERENCES lab_results(result_id) - ON DELETE RESTRICT -); - - - -CREATE TABLE performed_procedures ( - performed_id BIGINT, - - procedure_id BIGINT NOT NULL, - doctor_id BIGINT NOT NULL, - patient_id BIGINT NOT NULL, - diagnosis_id BIGINT, - - procedure_date DATE NOT NULL, - notes TEXT, - - CONSTRAINT performed_procedures_PK - PRIMARY KEY (performed_id), - - CONSTRAINT performed_procedures_procedure_FK - FOREIGN KEY (procedure_id) - REFERENCES procedures(procedure_id) - ON DELETE RESTRICT, - - CONSTRAINT performed_procedures_doctor_FK - FOREIGN KEY (doctor_id) - REFERENCES doctors(doctor_id) - ON DELETE RESTRICT, - - CONSTRAINT performed_procedures_patient_FK - FOREIGN KEY (patient_id) - REFERENCES patients(patient_id) - ON DELETE RESTRICT, - - CONSTRAINT performed_procedures_diagnosis_FK - FOREIGN KEY (diagnosis_id) - REFERENCES diagnosis(diagnosis_id) - ON DELETE RESTRICT -); - -CREATE TABLE performed_lab_tests ( - performed_test_id BIGINT, - - test_id BIGINT NOT NULL, - patient_id BIGINT NOT NULL, - doctor_id BIGINT NOT NULL, - technician_id BIGINT NOT NULL, - - test_date DATE NOT NULL, - notes TEXT, - - CONSTRAINT performed_lab_tests_PK - PRIMARY KEY (performed_test_id), - - CONSTRAINT performed_lab_tests_test_FK - FOREIGN KEY (test_id) - REFERENCES lab_tests(test_id) - ON DELETE RESTRICT, - - CONSTRAINT performed_lab_tests_patient_FK - FOREIGN KEY (patient_id) - REFERENCES patients(patient_id) - ON DELETE RESTRICT, - - CONSTRAINT performed_lab_tests_doctor_FK - FOREIGN KEY (doctor_id) - REFERENCES doctors(doctor_id) - ON DELETE RESTRICT, - - CONSTRAINT performed_lab_tests_technician_FK - FOREIGN KEY (technician_id) - REFERENCES lab_technician(technician_id) - ON DELETE RESTRICT -); - -CREATE TABLE medical_record_lab_results ( - record_id BIGINT NOT NULL, - result_id BIGINT NOT NULL, - - PRIMARY KEY (record_id, result_id), - - FOREIGN KEY (record_id) - REFERENCES medical_records(record_id) - ON DELETE RESTRICT, - - FOREIGN KEY (result_id) - REFERENCES lab_results(result_id) - ON DELETE RESTRICT -); - - - - -CREATE TABLE medical_record_procedures ( - record_id BIGINT NOT NULL, - procedure_id BIGINT NOT NULL, - - PRIMARY KEY (record_id, procedure_id), - - FOREIGN KEY (record_id) - REFERENCES medical_records(record_id) - ON DELETE RESTRICT, - - FOREIGN KEY (procedure_id) - REFERENCES procedures(procedure_id) - ON DELETE RESTRICT -); - - - -CREATE TABLE medical_record_symptoms ( - record_id BIGINT NOT NULL, - symptom_id BIGINT NOT NULL, - severity TEXT, - - PRIMARY KEY (record_id, symptom_id), - - FOREIGN KEY (record_id) - REFERENCES medical_records(record_id) - ON DELETE RESTRICT, - - FOREIGN KEY (symptom_id) - REFERENCES symptoms(symptom_id) - ON DELETE RESTRICT -); - -CREATE TABLE medical_record_allergies ( - record_id BIGINT NOT NULL, - allergy_id BIGINT NOT NULL, - reaction TEXT, - severity TEXT, - - PRIMARY KEY (record_id, allergy_id), - - FOREIGN KEY (record_id) - REFERENCES medical_records(record_id) - ON DELETE RESTRICT, - - FOREIGN KEY (allergy_id) - REFERENCES allergies(allergy_id) - ON DELETE RESTRICT -); - + bill_id BIGINT NOT NULL, + test_id BIGINT NOT NULL, + + CONSTRAINT billing_lab_tests_PK + PRIMARY KEY (bill_id, test_id), + + CONSTRAINT billing_lab_tests_bill_FK + FOREIGN KEY (bill_id) + REFERENCES billing(bill_id) + ON DELETE RESTRICT, + + CONSTRAINT billing_lab_tests_test_FK + FOREIGN KEY (test_id) + REFERENCES lab_tests(test_id) + ON DELETE RESTRICT +); COMMIT; -