Changeset 48d2bed for sql/ddl.sql
- Timestamp:
- 09/24/26 12:09:29 (11 hours ago)
- Branches:
- master
- Parents:
- ae6cd79
- File:
-
- 1 edited
-
sql/ddl.sql (modified) (3 diffs)
Legend:
- Unmodified
- Added
- Removed
-
sql/ddl.sql
rae6cd79 r48d2bed 1 2 1 BEGIN; 3 2 … … 11 10 DROP TABLE IF EXISTS prescription_medical_records CASCADE; 12 11 DROP TABLE IF EXISTS diagnosis_medical_records CASCADE; 13 DROP TABLE IF EXISTS doctor_medical_records CASCADE;14 12 DROP TABLE IF EXISTS billing_lab_tests CASCADE; 15 13 DROP TABLE IF EXISTS billing_procedures CASCADE; 16 14 DROP TABLE IF EXISTS department_procedures CASCADE; 17 DROP TABLE IF EXISTS diagnosis_procedures CASCADE;18 15 DROP TABLE IF EXISTS specialization_procedures CASCADE; 19 16 DROP TABLE IF EXISTS diagnosis_symptoms CASCADE; … … 43 40 DROP TABLE IF EXISTS doctor_specialization CASCADE; 44 41 DROP TABLE IF EXISTS doctor_level CASCADE; 45 46 47 42 DROP TABLE IF EXISTS users CASCADE; 43 44 45 -- USERS & ROLES --------------------------------------------------------- 46 47 CREATE TABLE users ( 48 user_id SERIAL PRIMARY KEY, 49 username VARCHAR(255) NOT NULL UNIQUE, 50 password VARCHAR(255) NOT NULL, 51 role VARCHAR(50) NOT NULL, 52 first_name VARCHAR(100), 53 last_name VARCHAR(100), 54 is_active BOOLEAN DEFAULT true 55 ); 56 57 58 -- STAFF ------------------------------------------------------------------- 48 59 49 60 CREATE TABLE doctor_level ( 50 51 52 53 54 55 56 57 61 level_id BIGINT, 62 level TEXT NOT NULL, 63 64 CONSTRAINT doctor_level_PK 65 PRIMARY KEY (level_id), 66 67 CONSTRAINT doctor_level_level_UQ 68 UNIQUE (level) 58 69 ); 59 70 60 71 CREATE TABLE doctor_specialization ( 61 62 63 64 65 66 67 68 72 specialization_id BIGINT, 73 specialization_name TEXT NOT NULL, 74 75 CONSTRAINT doctor_specialization_PK 76 PRIMARY KEY (specialization_id), 77 78 CONSTRAINT doctor_specialization_name_UQ 79 UNIQUE (specialization_name) 69 80 ); 70 81 71 82 72 83 CREATE TABLE departments ( 73 74 75 76 77 78 79 80 84 department_id BIGINT, 85 department_name TEXT NOT NULL, 86 87 CONSTRAINT departments_PK 88 PRIMARY KEY (department_id), 89 90 CONSTRAINT departments_name_UQ 91 UNIQUE (department_name) 81 92 ); 82 93 83 94 CREATE TABLE doctors ( 84 doctor_id BIGINT, 85 86 first_name TEXT NOT NULL, 87 last_name TEXT NOT NULL, 88 89 email_address TEXT NOT NULL, 90 91 level_id BIGINT NOT NULL, 92 specialization_id BIGINT NOT NULL, 93 department_id BIGINT NOT NULL, 94 95 CONSTRAINT doctors_PK 96 PRIMARY KEY (doctor_id), 97 98 CONSTRAINT doctors_email_UQ 99 UNIQUE (email_address), 100 101 CONSTRAINT doctors_email_chk 102 CHECK (email_address LIKE '%@%'), 103 104 CONSTRAINT doctors_level_FK 105 FOREIGN KEY (level_id) 106 REFERENCES doctor_level(level_id) 107 ON DELETE RESTRICT, 108 109 CONSTRAINT doctors_specialization_FK 110 FOREIGN KEY (specialization_id) 111 REFERENCES doctor_specialization(specialization_id) 112 ON DELETE RESTRICT, 113 114 CONSTRAINT doctors_department_FK 115 FOREIGN KEY (department_id) 116 REFERENCES departments(department_id) 117 ON DELETE RESTRICT 118 ); 119 120 95 doctor_id BIGINT, 96 97 first_name TEXT NOT NULL, 98 last_name TEXT NOT NULL, 99 100 email_address TEXT NOT NULL, 101 102 level_id BIGINT NOT NULL, 103 specialization_id BIGINT NOT NULL, 104 department_id BIGINT NOT NULL, 105 106 user_id BIGINT NOT NULL, 107 108 CONSTRAINT doctors_PK 109 PRIMARY KEY (doctor_id), 110 111 CONSTRAINT doctors_email_UQ 112 UNIQUE (email_address), 113 114 CONSTRAINT doctors_email_chk 115 CHECK (email_address LIKE '%@%'), 116 117 CONSTRAINT doctors_user_UQ 118 UNIQUE (user_id), 119 120 CONSTRAINT doctors_level_FK 121 FOREIGN KEY (level_id) 122 REFERENCES doctor_level(level_id) 123 ON DELETE RESTRICT, 124 125 CONSTRAINT doctors_specialization_FK 126 FOREIGN KEY (specialization_id) 127 REFERENCES doctor_specialization(specialization_id) 128 ON DELETE RESTRICT, 129 130 CONSTRAINT doctors_department_FK 131 FOREIGN KEY (department_id) 132 REFERENCES departments(department_id) 133 ON DELETE RESTRICT, 134 135 CONSTRAINT doctors_user_FK 136 FOREIGN KEY (user_id) 137 REFERENCES users(user_id) 138 ON DELETE RESTRICT 139 ); 140 141 CREATE TABLE admin ( 142 admin_id BIGINT, 143 144 username TEXT NOT NULL, 145 name TEXT NOT NULL, 146 lastname TEXT NOT NULL, 147 148 email TEXT NOT NULL, 149 150 user_id BIGINT NOT NULL, 151 152 CONSTRAINT admin_PK 153 PRIMARY KEY (admin_id), 154 155 CONSTRAINT admin_username_UQ 156 UNIQUE (username), 157 158 CONSTRAINT admin_email_UQ 159 UNIQUE (email), 160 161 CONSTRAINT admin_email_chk 162 CHECK (email LIKE '%@adminmedora%'), 163 164 CONSTRAINT admin_user_UQ 165 UNIQUE (user_id), 166 167 CONSTRAINT admin_user_FK 168 FOREIGN KEY (user_id) 169 REFERENCES users(user_id) 170 ON DELETE RESTRICT 171 ); 172 173 CREATE TABLE lab_technician ( 174 technician_id BIGINT, 175 176 username TEXT NOT NULL, 177 name TEXT NOT NULL, 178 lastname TEXT NOT NULL, 179 180 email TEXT NOT NULL, 181 182 user_id BIGINT NOT NULL, 183 184 CONSTRAINT lab_technician_PK 185 PRIMARY KEY (technician_id), 186 187 CONSTRAINT lab_technician_username_UQ 188 UNIQUE (username), 189 190 CONSTRAINT lab_technician_email_UQ 191 UNIQUE (email), 192 193 CONSTRAINT lab_technician_email_chk 194 CHECK (email LIKE '%@labmedora%'), 195 196 CONSTRAINT lab_technician_user_UQ 197 UNIQUE (user_id), 198 199 CONSTRAINT lab_technician_user_FK 200 FOREIGN KEY (user_id) 201 REFERENCES users(user_id) 202 ON DELETE RESTRICT 203 ); 204 205 206 -- PATIENTS ------------------------------------------------------------------ 121 207 122 208 CREATE TABLE patients ( 123 patient_id BIGINT, 124 125 first_name TEXT NOT NULL, 126 last_name TEXT NOT NULL, 127 128 email_address TEXT, 129 date_of_birth DATE NOT NULL, 130 131 blood_type TEXT, 132 gender TEXT, 133 134 phone_number TEXT, 135 embg TEXT NOT NULL, 136 137 CONSTRAINT patients_PK 138 PRIMARY KEY (patient_id), 139 140 CONSTRAINT patients_email_UQ 141 UNIQUE (email_address), 142 143 CONSTRAINT patients_embg_UQ 144 UNIQUE (embg), 145 146 CONSTRAINT patients_gender_chk 147 CHECK (gender IN ('MALE', 'FEMALE')), 148 149 CONSTRAINT patients_blood_type_chk 150 CHECK ( 151 blood_type IN ( 152 'A+', 'A-', 153 'B+', 'B-', 154 'AB+', 'AB-', 155 'O+', 'O-' 156 ) 157 ) 158 ); 159 160 CREATE TABLE admin ( 161 admin_id BIGINT, 162 163 username TEXT NOT NULL, 164 name TEXT NOT NULL, 165 lastname TEXT NOT NULL, 166 167 email TEXT NOT NULL, 168 169 CONSTRAINT admin_PK 170 PRIMARY KEY (admin_id), 171 172 CONSTRAINT admin_username_UQ 173 UNIQUE (username), 174 175 CONSTRAINT admin_email_UQ 176 UNIQUE (email), 177 178 CONSTRAINT admin_email_chk 179 CHECK (email LIKE '%@adminmedora%') 180 ); 181 182 CREATE TABLE lab_technician ( 183 technician_id BIGINT, 184 185 username TEXT NOT NULL, 186 name TEXT NOT NULL, 187 lastname TEXT NOT NULL, 188 189 email TEXT NOT NULL, 190 191 CONSTRAINT lab_technician_PK 192 PRIMARY KEY (technician_id), 193 194 CONSTRAINT lab_technician_username_UQ 195 UNIQUE (username), 196 197 CONSTRAINT lab_technician_email_UQ 198 UNIQUE (email), 199 200 CONSTRAINT lab_technician_email_chk 201 CHECK (email LIKE '%@labmedora%') 202 ); 203 204 209 patient_id BIGINT, 210 211 first_name TEXT NOT NULL, 212 last_name TEXT NOT NULL, 213 214 email_address TEXT, 215 date_of_birth DATE NOT NULL, 216 217 blood_type TEXT, 218 gender TEXT, 219 220 phone_number TEXT, 221 embg TEXT NOT NULL, 222 223 user_id BIGINT NOT NULL, 224 225 CONSTRAINT patients_PK 226 PRIMARY KEY (patient_id), 227 228 CONSTRAINT patients_email_UQ 229 UNIQUE (email_address), 230 231 CONSTRAINT patients_embg_UQ 232 UNIQUE (embg), 233 234 CONSTRAINT patients_embg_format_chk 235 CHECK (embg ~ '^[0-9]{13}$'), 236 237 CONSTRAINT patients_gender_chk 238 CHECK (gender IN ('MALE', 'FEMALE')), 239 240 CONSTRAINT patients_blood_type_chk 241 CHECK ( 242 blood_type IN ( 243 'A+', 'A-', 244 'B+', 'B-', 245 'AB+', 'AB-', 246 'O+', 'O-' 247 ) 248 ), 249 250 CONSTRAINT patients_user_UQ 251 UNIQUE (user_id), 252 253 CONSTRAINT patients_user_FK 254 FOREIGN KEY (user_id) 255 REFERENCES users(user_id) 256 ON DELETE RESTRICT 257 ); 258 259 260 -- APPOINTMENTS -------------------------------------------------------------- 205 261 206 262 CREATE TABLE appointments ( 207 appointment_id BIGINT, 208 209 appointment_date DATE NOT NULL, 210 appointment_time TIME NOT NULL, 211 212 status TEXT NOT NULL, 213 214 patient_id BIGINT NOT NULL, 215 doctor_id BIGINT NOT NULL, 216 217 CONSTRAINT appointments_PK 218 PRIMARY KEY (appointment_id), 219 220 CONSTRAINT appointments_status_chk 221 CHECK ( 222 status IN ( 223 'SCHEDULED', 224 'COMPLETED', 225 'CANCELLED', 226 'IN_PROGRESS' 227 ) 228 ), 229 230 CONSTRAINT appointments_patient_FK 231 FOREIGN KEY (patient_id) 232 REFERENCES patients(patient_id) 233 ON DELETE RESTRICT, 234 235 CONSTRAINT appointments_doctor_FK 236 FOREIGN KEY (doctor_id) 237 REFERENCES doctors(doctor_id) 238 ON DELETE RESTRICT 263 appointment_id BIGINT, 264 265 appointment_date DATE NOT NULL, 266 appointment_time TIME NOT NULL, 267 268 status TEXT NOT NULL, 269 270 patient_id BIGINT NOT NULL, 271 doctor_id BIGINT NOT NULL, 272 273 CONSTRAINT appointments_PK 274 PRIMARY KEY (appointment_id), 275 276 CONSTRAINT appointments_status_chk 277 CHECK ( 278 status IN ( 279 'SCHEDULED', 280 'COMPLETED', 281 'CANCELLED', 282 'IN_PROGRESS' 283 ) 284 ), 285 286 CONSTRAINT appointments_patient_FK 287 FOREIGN KEY (patient_id) 288 REFERENCES patients(patient_id) 289 ON DELETE RESTRICT, 290 291 CONSTRAINT appointments_doctor_FK 292 FOREIGN KEY (doctor_id) 293 REFERENCES doctors(doctor_id) 294 ON DELETE RESTRICT 295 ); 296 297 298 -- CLINICAL ENTITIES ----------------------------------------------------- 299 300 CREATE TABLE allergies ( 301 allergy_id BIGINT, 302 303 name TEXT NOT NULL, 304 allergy_severity TEXT NOT NULL, 305 306 CONSTRAINT allergies_PK 307 PRIMARY KEY (allergy_id), 308 309 CONSTRAINT allergies_name_UQ 310 UNIQUE (name), 311 312 CONSTRAINT allergies_name_chk 313 CHECK (length(trim(name)) > 0), 314 315 CONSTRAINT allergies_severity_chk 316 CHECK (allergy_severity IN ('LOW', 'MEDIUM', 'HIGH', 'CRITICAL')) 317 ); 318 319 CREATE TABLE symptoms ( 320 symptom_id BIGINT, 321 322 name TEXT NOT NULL, 323 description TEXT, 324 325 CONSTRAINT symptoms_PK 326 PRIMARY KEY (symptom_id), 327 328 CONSTRAINT symptoms_name_UQ 329 UNIQUE (name), 330 331 CONSTRAINT symptoms_name_chk 332 CHECK (length(trim(name)) > 0), 333 334 CONSTRAINT symptoms_description_chk 335 CHECK ( 336 description IS NULL 337 OR length(trim(description)) > 0 338 ) 239 339 ); 240 340 241 341 CREATE TABLE diagnosis ( 242 diagnosis_id BIGINT, 243 244 name TEXT NOT NULL, 245 description TEXT, 246 247 patient_id BIGINT NOT NULL, 248 doctor_id BIGINT NOT NULL, 249 250 CONSTRAINT diagnosis_PK 251 PRIMARY KEY (diagnosis_id), 252 253 CONSTRAINT diagnosis_patient_FK 254 FOREIGN KEY (patient_id) 255 REFERENCES patients(patient_id) 256 ON DELETE RESTRICT, 257 258 CONSTRAINT diagnosis_doctor_FK 259 FOREIGN KEY (doctor_id) 260 REFERENCES doctors(doctor_id) 261 ON DELETE RESTRICT 262 ); 263 264 265 342 diagnosis_id BIGINT, 343 344 name TEXT NOT NULL, 345 description TEXT, 346 347 patient_id BIGINT NOT NULL, 348 doctor_id BIGINT NOT NULL, 349 350 CONSTRAINT diagnosis_PK 351 PRIMARY KEY (diagnosis_id), 352 353 CONSTRAINT diagnosis_patient_FK 354 FOREIGN KEY (patient_id) 355 REFERENCES patients(patient_id) 356 ON DELETE RESTRICT, 357 358 CONSTRAINT diagnosis_doctor_FK 359 FOREIGN KEY (doctor_id) 360 REFERENCES doctors(doctor_id) 361 ON DELETE RESTRICT 362 ); 363 364 CREATE TABLE diagnosis_symptoms ( 365 diagnosis_id BIGINT NOT NULL, 366 symptom_id BIGINT NOT NULL, 367 368 CONSTRAINT diagnosis_symptoms_PK 369 PRIMARY KEY (diagnosis_id, symptom_id), 370 371 CONSTRAINT diagnosis_symptoms_diagnosis_FK 372 FOREIGN KEY (diagnosis_id) 373 REFERENCES diagnosis(diagnosis_id) 374 ON DELETE RESTRICT, 375 376 CONSTRAINT diagnosis_symptoms_symptom_FK 377 FOREIGN KEY (symptom_id) 378 REFERENCES symptoms(symptom_id) 379 ON DELETE RESTRICT 380 ); 381 382 383 -- PROCEDURES -------------------------------------------------------------- 266 384 267 385 CREATE TABLE procedures ( 268 procedure_id BIGINT, 269 270 procedure_type TEXT NOT NULL, 271 procedure_date DATE NOT NULL, 272 273 description TEXT, 274 cost DECIMAL NOT NULL, 275 276 doctor_id BIGINT NOT NULL, 277 diagnosis_id BIGINT NOT NULL, 278 279 CONSTRAINT procedures_PK 280 PRIMARY KEY (procedure_id), 281 282 CONSTRAINT procedures_cost_chk 283 CHECK (cost >= 0), 284 285 CONSTRAINT procedures_type_chk 286 CHECK (length(trim(procedure_type)) > 0), 287 288 CONSTRAINT procedures_doctor_FK 289 FOREIGN KEY (doctor_id) 290 REFERENCES doctors(doctor_id) 291 ON DELETE RESTRICT, 292 293 CONSTRAINT procedures_diagnosis_FK 294 FOREIGN KEY (diagnosis_id) 295 REFERENCES diagnosis(diagnosis_id) 296 ON DELETE RESTRICT 297 ); 298 299 300 386 procedure_id BIGINT, 387 388 procedure_type TEXT NOT NULL, 389 procedure_date DATE NOT NULL, 390 391 description TEXT, 392 cost DECIMAL NOT NULL, 393 394 doctor_id BIGINT NOT NULL, 395 396 CONSTRAINT procedures_PK 397 PRIMARY KEY (procedure_id), 398 399 CONSTRAINT procedures_cost_chk 400 CHECK (cost >= 0), 401 402 CONSTRAINT procedures_type_chk 403 CHECK (length(trim(procedure_type)) > 0), 404 405 CONSTRAINT procedures_doctor_FK 406 FOREIGN KEY (doctor_id) 407 REFERENCES doctors(doctor_id) 408 ON DELETE RESTRICT 409 ); 301 410 302 411 CREATE TABLE procedure_results ( 303 result_id BIGINT, 304 305 result_description TEXT, 306 result_date DATE NOT NULL, 307 308 procedure_id BIGINT NOT NULL, 309 310 CONSTRAINT procedure_results_PK 311 PRIMARY KEY (result_id), 312 313 CONSTRAINT procedure_results_description_chk 314 CHECK ( 315 result_description IS NULL 316 OR length(trim(result_description)) > 0 317 ), 318 319 CONSTRAINT procedure_results_procedure_FK 320 FOREIGN KEY (procedure_id) 321 REFERENCES procedures(procedure_id) 322 ON DELETE RESTRICT 323 ); 412 result_id BIGINT, 413 414 result_description TEXT, 415 result_date DATE NOT NULL, 416 417 procedure_id BIGINT NOT NULL, 418 419 CONSTRAINT procedure_results_PK 420 PRIMARY KEY (result_id), 421 422 CONSTRAINT procedure_results_description_chk 423 CHECK ( 424 result_description IS NULL 425 OR length(trim(result_description)) > 0 426 ), 427 428 CONSTRAINT procedure_results_procedure_FK 429 FOREIGN KEY (procedure_id) 430 REFERENCES procedures(procedure_id) 431 ON DELETE RESTRICT 432 ); 433 434 CREATE TABLE specialization_procedures ( 435 specialization_id BIGINT NOT NULL, 436 procedure_id BIGINT NOT NULL, 437 438 CONSTRAINT specialization_procedures_PK 439 PRIMARY KEY (specialization_id, procedure_id), 440 441 CONSTRAINT specialization_procedures_specialization_FK 442 FOREIGN KEY (specialization_id) 443 REFERENCES doctor_specialization(specialization_id) 444 ON DELETE RESTRICT, 445 446 CONSTRAINT specialization_procedures_procedure_FK 447 FOREIGN KEY (procedure_id) 448 REFERENCES procedures(procedure_id) 449 ON DELETE RESTRICT 450 ); 451 452 CREATE TABLE department_procedures ( 453 department_id BIGINT NOT NULL, 454 procedure_id BIGINT NOT NULL, 455 456 CONSTRAINT department_procedures_PK 457 PRIMARY KEY (department_id, procedure_id), 458 459 CONSTRAINT department_procedures_department_FK 460 FOREIGN KEY (department_id) 461 REFERENCES departments(department_id) 462 ON DELETE RESTRICT, 463 464 CONSTRAINT department_procedures_procedure_FK 465 FOREIGN KEY (procedure_id) 466 REFERENCES procedures(procedure_id) 467 ON DELETE RESTRICT 468 ); 469 470 471 -- LAB TESTS ----------------------------------------------------------------- 472 473 CREATE TABLE lab_tests ( 474 test_id BIGINT, 475 476 test_name TEXT NOT NULL, 477 description TEXT, 478 cost DECIMAL NOT NULL, 479 480 CONSTRAINT lab_tests_PK 481 PRIMARY KEY (test_id), 482 483 CONSTRAINT lab_tests_cost_chk 484 CHECK (cost >= 0), 485 486 CONSTRAINT lab_tests_name_chk 487 CHECK (length(trim(test_name)) > 0) 488 ); 489 490 CREATE TABLE lab_results ( 491 result_id BIGINT, 492 493 results TEXT NOT NULL, 494 result_date DATE NOT NULL, 495 496 test_id BIGINT NOT NULL, 497 498 CONSTRAINT lab_results_PK 499 PRIMARY KEY (result_id), 500 501 CONSTRAINT lab_results_results_chk 502 CHECK (length(trim(results)) > 0), 503 504 CONSTRAINT lab_results_date_chk 505 CHECK (result_date <= CURRENT_DATE), 506 507 CONSTRAINT lab_results_test_FK 508 FOREIGN KEY (test_id) 509 REFERENCES lab_tests(test_id) 510 ON DELETE RESTRICT 511 ); 512 513 514 -- PRESCRIPTIONS --------------------------------------------------------- 324 515 325 516 CREATE TABLE prescriptions ( 326 327 328 329 330 331 517 prescription_id BIGINT, 518 519 medication_name TEXT NOT NULL, 520 521 CONSTRAINT prescriptions_PK 522 PRIMARY KEY (prescription_id) 332 523 ); 333 524 334 525 CREATE TABLE prescription_restriction ( 335 restriction_id BIGINT, 336 337 description TEXT NOT NULL, 338 339 prescription_id BIGINT NOT NULL, 340 341 CONSTRAINT prescription_restriction_PK 342 PRIMARY KEY (restriction_id), 343 344 CONSTRAINT prescription_restriction_desc_chk 345 CHECK (length(trim(description)) > 0), 346 347 CONSTRAINT prescription_restriction_prescription_FK 348 FOREIGN KEY (prescription_id) 349 REFERENCES prescriptions(prescription_id) 350 ON DELETE RESTRICT 351 ); 352 353 354 CREATE TABLE lab_tests ( 355 test_id BIGINT, 356 357 test_name TEXT NOT NULL, 358 description TEXT, 359 cost DECIMAL NOT NULL, 360 361 CONSTRAINT lab_tests_PK 362 PRIMARY KEY (test_id), 363 364 CONSTRAINT lab_tests_cost_chk 365 CHECK (cost >= 0), 366 367 CONSTRAINT lab_tests_name_chk 368 CHECK (length(trim(test_name)) > 0) 369 ); 370 371 372 CREATE TABLE lab_results ( 373 result_id BIGINT, 374 375 results TEXT NOT NULL, 376 result_date DATE NOT NULL, 377 378 test_id BIGINT NOT NULL, 379 380 CONSTRAINT lab_results_PK 381 PRIMARY KEY (result_id), 382 383 CONSTRAINT lab_results_results_chk 384 CHECK (length(trim(results)) > 0), 385 386 CONSTRAINT lab_results_date_chk 387 CHECK (result_date <= CURRENT_DATE), 388 389 CONSTRAINT lab_results_test_FK 390 FOREIGN KEY (test_id) 391 REFERENCES lab_tests(test_id) 392 ON DELETE RESTRICT 393 ); 526 restriction_id BIGINT, 527 528 description TEXT NOT NULL, 529 530 prescription_id BIGINT NOT NULL, 531 532 CONSTRAINT prescription_restriction_PK 533 PRIMARY KEY (restriction_id), 534 535 CONSTRAINT prescription_restriction_desc_chk 536 CHECK (length(trim(description)) > 0), 537 538 CONSTRAINT prescription_restriction_prescription_FK 539 FOREIGN KEY (prescription_id) 540 REFERENCES prescriptions(prescription_id) 541 ON DELETE RESTRICT 542 ); 543 544 CREATE TABLE allergy_prescription_restrictions ( 545 allergy_id BIGINT NOT NULL, 546 restriction_id BIGINT NOT NULL, 547 548 CONSTRAINT allergy_prescription_restrictions_PK 549 PRIMARY KEY (allergy_id, restriction_id), 550 551 CONSTRAINT allergy_prescription_restrictions_allergy_FK 552 FOREIGN KEY (allergy_id) 553 REFERENCES allergies(allergy_id) 554 ON DELETE RESTRICT, 555 556 CONSTRAINT allergy_prescription_restrictions_restriction_FK 557 FOREIGN KEY (restriction_id) 558 REFERENCES prescription_restriction(restriction_id) 559 ON DELETE RESTRICT 560 ); 561 562 563 -- PATIENTS <-> ALLERGIES / SYMPTOMS ----------------------------------------- 564 565 CREATE TABLE patient_allergies ( 566 patient_id BIGINT NOT NULL, 567 allergy_id BIGINT NOT NULL, 568 569 CONSTRAINT patient_allergies_PK 570 PRIMARY KEY (patient_id, allergy_id), 571 572 CONSTRAINT patient_allergies_patient_FK 573 FOREIGN KEY (patient_id) 574 REFERENCES patients(patient_id) 575 ON DELETE RESTRICT, 576 577 CONSTRAINT patient_allergies_allergy_FK 578 FOREIGN KEY (allergy_id) 579 REFERENCES allergies(allergy_id) 580 ON DELETE RESTRICT 581 ); 582 583 CREATE TABLE patient_symptoms ( 584 patient_id BIGINT NOT NULL, 585 symptom_id BIGINT NOT NULL, 586 587 CONSTRAINT patient_symptoms_PK 588 PRIMARY KEY (patient_id, symptom_id), 589 590 CONSTRAINT patient_symptoms_patient_FK 591 FOREIGN KEY (patient_id) 592 REFERENCES patients(patient_id) 593 ON DELETE RESTRICT, 594 595 CONSTRAINT patient_symptoms_symptom_FK 596 FOREIGN KEY (symptom_id) 597 REFERENCES symptoms(symptom_id) 598 ON DELETE RESTRICT 599 ); 600 601 602 -- MEDICAL RECORDS ------------------------------------------------------- 394 603 395 604 CREATE TABLE medical_records ( 396 record_id BIGINT PRIMARY KEY, 397 398 patient_id BIGINT NOT NULL, 399 400 401 FOREIGN KEY (patient_id) 402 REFERENCES patients(patient_id) 403 ON DELETE RESTRICT 404 ); 405 406 407 408 409 CREATE TABLE allergies ( 410 allergy_id BIGINT, 411 412 name TEXT NOT NULL, 413 allergy_severity TEXT NOT NULL, 414 415 CONSTRAINT allergies_PK 416 PRIMARY KEY (allergy_id), 417 418 CONSTRAINT allergies_name_UQ 419 UNIQUE (name), 420 421 CONSTRAINT allergies_name_chk 422 CHECK (length(trim(name)) > 0), 423 424 CONSTRAINT allergies_severity_chk 425 CHECK (allergy_severity IN ('LOW', 'MEDIUM', 'HIGH', 'CRITICAL')) 426 ); 427 428 429 CREATE TABLE symptoms ( 430 symptom_id BIGINT, 431 432 name TEXT NOT NULL, 433 description TEXT, 434 435 CONSTRAINT symptoms_PK 436 PRIMARY KEY (symptom_id), 437 438 CONSTRAINT symptoms_name_UQ 439 UNIQUE (name), 440 441 CONSTRAINT symptoms_name_chk 442 CHECK (length(trim(name)) > 0), 443 444 CONSTRAINT symptoms_description_chk 445 CHECK ( 446 description IS NULL 447 OR length(trim(description)) > 0 448 ) 449 ); 450 605 record_id BIGINT PRIMARY KEY, 606 607 patient_id BIGINT NOT NULL, 608 609 FOREIGN KEY (patient_id) 610 REFERENCES patients(patient_id) 611 ON DELETE RESTRICT 612 ); 613 614 CREATE TABLE diagnosis_medical_records ( 615 diagnosis_id BIGINT NOT NULL, 616 record_id BIGINT NOT NULL, 617 618 CONSTRAINT diagnosis_medical_records_PK 619 PRIMARY KEY (diagnosis_id, record_id), 620 621 CONSTRAINT diagnosis_medical_records_diagnosis_FK 622 FOREIGN KEY (diagnosis_id) 623 REFERENCES diagnosis(diagnosis_id) 624 ON DELETE RESTRICT, 625 626 CONSTRAINT diagnosis_medical_records_record_FK 627 FOREIGN KEY (record_id) 628 REFERENCES medical_records(record_id) 629 ON DELETE RESTRICT 630 ); 631 632 CREATE TABLE prescription_medical_records ( 633 prescription_id BIGINT NOT NULL, 634 record_id BIGINT NOT NULL, 635 636 dosage TEXT NOT NULL, 637 frequency TEXT NOT NULL, 638 duration TEXT NOT NULL, 639 640 notes TEXT, 641 642 CONSTRAINT prescription_medical_records_PK 643 PRIMARY KEY (prescription_id, record_id), 644 645 CONSTRAINT prescription_medical_records_prescription_FK 646 FOREIGN KEY (prescription_id) 647 REFERENCES prescriptions(prescription_id) 648 ON DELETE RESTRICT, 649 650 CONSTRAINT prescription_medical_records_record_FK 651 FOREIGN KEY (record_id) 652 REFERENCES medical_records(record_id) 653 ON DELETE RESTRICT 654 ); 655 656 CREATE TABLE medical_record_lab_results ( 657 record_id BIGINT NOT NULL, 658 result_id BIGINT NOT NULL, 659 660 PRIMARY KEY (record_id, result_id), 661 662 FOREIGN KEY (record_id) 663 REFERENCES medical_records(record_id) 664 ON DELETE RESTRICT, 665 666 FOREIGN KEY (result_id) 667 REFERENCES lab_results(result_id) 668 ON DELETE RESTRICT 669 ); 670 671 CREATE TABLE medical_record_procedures ( 672 record_id BIGINT NOT NULL, 673 procedure_id BIGINT NOT NULL, 674 675 PRIMARY KEY (record_id, procedure_id), 676 677 FOREIGN KEY (record_id) 678 REFERENCES medical_records(record_id) 679 ON DELETE RESTRICT, 680 681 FOREIGN KEY (procedure_id) 682 REFERENCES procedures(procedure_id) 683 ON DELETE RESTRICT 684 ); 685 686 CREATE TABLE medical_record_symptoms ( 687 record_id BIGINT NOT NULL, 688 symptom_id BIGINT NOT NULL, 689 severity TEXT, 690 691 PRIMARY KEY (record_id, symptom_id), 692 693 FOREIGN KEY (record_id) 694 REFERENCES medical_records(record_id) 695 ON DELETE RESTRICT, 696 697 FOREIGN KEY (symptom_id) 698 REFERENCES symptoms(symptom_id) 699 ON DELETE RESTRICT 700 ); 701 702 CREATE TABLE medical_record_allergies ( 703 record_id BIGINT NOT NULL, 704 allergy_id BIGINT NOT NULL, 705 reaction TEXT, 706 severity TEXT, 707 708 PRIMARY KEY (record_id, allergy_id), 709 710 FOREIGN KEY (record_id) 711 REFERENCES medical_records(record_id) 712 ON DELETE RESTRICT, 713 714 FOREIGN KEY (allergy_id) 715 REFERENCES allergies(allergy_id) 716 ON DELETE RESTRICT 717 ); 718 719 720 -- REPORTS & REFERRALS ----------------------------------------------------- 451 721 452 722 CREATE TABLE referrals ( 453 454 referral_id BIGINT PRIMARY KEY, 455 456 reason TEXT NOT NULL, 457 referral_date DATE NOT NULL, 458 459 record_id BIGINT NOT NULL, 460 from_doctor_id BIGINT NOT NULL, 461 to_doctor_id BIGINT not null, 462 463 CONSTRAINT referrals_reason_chk 464 CHECK (length(trim(reason)) > 0), 465 466 FOREIGN KEY (record_id) 467 REFERENCES medical_records(record_id) 468 ON DELETE RESTRICT, 469 470 FOREIGN KEY (from_doctor_id) 471 REFERENCES doctors(doctor_id) 472 ON DELETE RESTRICT, 473 474 FOREIGN KEY (to_doctor_id) 475 REFERENCES doctors(doctor_id) 476 ON DELETE RESTRICT 723 referral_id BIGINT PRIMARY KEY, 724 725 reason TEXT NOT NULL, 726 referral_date DATE NOT NULL, 727 728 record_id BIGINT NOT NULL, 729 from_doctor_id BIGINT NOT NULL, 730 to_doctor_id BIGINT NOT NULL, 731 732 CONSTRAINT referrals_reason_chk 733 CHECK (length(trim(reason)) > 0), 734 735 FOREIGN KEY (record_id) 736 REFERENCES medical_records(record_id) 737 ON DELETE RESTRICT, 738 739 FOREIGN KEY (from_doctor_id) 740 REFERENCES doctors(doctor_id) 741 ON DELETE RESTRICT, 742 743 FOREIGN KEY (to_doctor_id) 744 REFERENCES doctors(doctor_id) 745 ON DELETE RESTRICT 477 746 ); 478 747 479 748 CREATE TABLE medical_report ( 480 report_id BIGINT PRIMARY KEY, 481 482 description TEXT NOT NULL, 483 report_date DATE NOT NULL, 484 485 record_id BIGINT NOT NULL, 486 doctor_id BIGINT NOT NULL, 487 488 FOREIGN KEY (record_id) 489 REFERENCES medical_records(record_id) 490 ON DELETE RESTRICT, 491 492 FOREIGN KEY (doctor_id) 493 REFERENCES doctors(doctor_id) 494 ON DELETE RESTRICT 495 ); 496 749 report_id BIGINT PRIMARY KEY, 750 751 description TEXT NOT NULL, 752 report_date DATE NOT NULL, 753 754 record_id BIGINT NOT NULL, 755 doctor_id BIGINT NOT NULL, 756 757 FOREIGN KEY (record_id) 758 REFERENCES medical_records(record_id) 759 ON DELETE RESTRICT, 760 761 FOREIGN KEY (doctor_id) 762 REFERENCES doctors(doctor_id) 763 ON DELETE RESTRICT 764 ); 765 766 CREATE TABLE medical_report_lab_results ( 767 report_id BIGINT NOT NULL, 768 result_id BIGINT NOT NULL, 769 770 CONSTRAINT medical_report_lab_results_PK 771 PRIMARY KEY (report_id, result_id), 772 773 CONSTRAINT medical_report_lab_results_report_FK 774 FOREIGN KEY (report_id) 775 REFERENCES medical_report(report_id) 776 ON DELETE RESTRICT, 777 778 CONSTRAINT medical_report_lab_results_result_FK 779 FOREIGN KEY (result_id) 780 REFERENCES lab_results(result_id) 781 ON DELETE RESTRICT 782 ); 783 784 785 -- PERFORMED PROCEDURES / LAB TESTS ----------------------------------------- 786 787 CREATE TABLE performed_procedures ( 788 performed_id BIGINT, 789 790 procedure_id BIGINT NOT NULL, 791 doctor_id BIGINT NOT NULL, 792 patient_id BIGINT NOT NULL, 793 diagnosis_id BIGINT, 794 795 procedure_date DATE NOT NULL, 796 notes TEXT, 797 798 CONSTRAINT performed_procedures_PK 799 PRIMARY KEY (performed_id), 800 801 CONSTRAINT performed_procedures_procedure_FK 802 FOREIGN KEY (procedure_id) 803 REFERENCES procedures(procedure_id) 804 ON DELETE RESTRICT, 805 806 CONSTRAINT performed_procedures_doctor_FK 807 FOREIGN KEY (doctor_id) 808 REFERENCES doctors(doctor_id) 809 ON DELETE RESTRICT, 810 811 CONSTRAINT performed_procedures_patient_FK 812 FOREIGN KEY (patient_id) 813 REFERENCES patients(patient_id) 814 ON DELETE RESTRICT, 815 816 CONSTRAINT performed_procedures_diagnosis_FK 817 FOREIGN KEY (diagnosis_id) 818 REFERENCES diagnosis(diagnosis_id) 819 ON DELETE RESTRICT 820 ); 821 822 CREATE TABLE performed_lab_tests ( 823 performed_test_id BIGINT, 824 825 test_id BIGINT NOT NULL, 826 patient_id BIGINT NOT NULL, 827 doctor_id BIGINT NOT NULL, 828 technician_id BIGINT NOT NULL, 829 830 test_date DATE NOT NULL, 831 notes TEXT, 832 created_at TIMESTAMP DEFAULT now(), 833 834 CONSTRAINT performed_lab_tests_PK 835 PRIMARY KEY (performed_test_id), 836 837 CONSTRAINT performed_lab_tests_test_FK 838 FOREIGN KEY (test_id) 839 REFERENCES lab_tests(test_id) 840 ON DELETE RESTRICT, 841 842 CONSTRAINT performed_lab_tests_patient_FK 843 FOREIGN KEY (patient_id) 844 REFERENCES patients(patient_id) 845 ON DELETE RESTRICT, 846 847 CONSTRAINT performed_lab_tests_doctor_FK 848 FOREIGN KEY (doctor_id) 849 REFERENCES doctors(doctor_id) 850 ON DELETE RESTRICT, 851 852 CONSTRAINT performed_lab_tests_technician_FK 853 FOREIGN KEY (technician_id) 854 REFERENCES lab_technician(technician_id) 855 ON DELETE RESTRICT 856 ); 857 858 859 -- BILLING ------------------------------------------------------------------- 497 860 498 861 CREATE TABLE billing ( 499 bill_id BIGINT PRIMARY KEY, 500 501 total_cost DECIMAL NOT NULL, 502 503 payment_status TEXT NOT NULL, 504 payment_date DATE, 505 506 record_id BIGINT NOT NULL, 507 admin_id BIGINT NOT NULL, 508 509 CONSTRAINT billing_cost_chk CHECK (total_cost >= 0), 510 CONSTRAINT billing_status_chk 511 CHECK (payment_status IN ('PENDING', 'PAID', 'CANCELLED')), 512 513 FOREIGN KEY (record_id) 514 REFERENCES medical_records(record_id) 515 ON DELETE RESTRICT, 516 517 FOREIGN KEY (admin_id) 518 REFERENCES admin(admin_id) 519 ON DELETE RESTRICT 520 ); 521 522 523 524 525 CREATE TABLE patient_allergies ( 526 patient_id BIGINT NOT NULL, 527 allergy_id BIGINT NOT NULL, 528 529 CONSTRAINT patient_allergies_PK 530 PRIMARY KEY (patient_id, allergy_id), 531 532 CONSTRAINT patient_allergies_patient_FK 533 FOREIGN KEY (patient_id) 534 REFERENCES patients(patient_id) 535 ON DELETE RESTRICT, 536 537 CONSTRAINT patient_allergies_allergy_FK 538 FOREIGN KEY (allergy_id) 539 REFERENCES allergies(allergy_id) 540 ON DELETE RESTRICT 541 ); 542 543 544 545 CREATE TABLE allergy_prescription_restrictions ( 546 allergy_id BIGINT NOT NULL, 547 restriction_id BIGINT NOT NULL, 548 549 CONSTRAINT allergy_prescription_restrictions_PK 550 PRIMARY KEY (allergy_id, restriction_id), 551 552 CONSTRAINT allergy_prescription_restrictions_allergy_FK 553 FOREIGN KEY (allergy_id) 554 REFERENCES allergies(allergy_id) 555 ON DELETE RESTRICT, 556 557 CONSTRAINT allergy_prescription_restrictions_restriction_FK 558 FOREIGN KEY (restriction_id) 559 REFERENCES prescription_restriction(restriction_id) 560 ON DELETE RESTRICT 561 ); 562 563 564 565 CREATE TABLE patient_symptoms ( 566 patient_id BIGINT NOT NULL, 567 symptom_id BIGINT NOT NULL, 568 569 CONSTRAINT patient_symptoms_PK 570 PRIMARY KEY (patient_id, symptom_id), 571 572 CONSTRAINT patient_symptoms_patient_FK 573 FOREIGN KEY (patient_id) 574 REFERENCES patients(patient_id) 575 ON DELETE RESTRICT, 576 577 CONSTRAINT patient_symptoms_symptom_FK 578 FOREIGN KEY (symptom_id) 579 REFERENCES symptoms(symptom_id) 580 ON DELETE RESTRICT 581 ); 582 583 584 585 586 CREATE TABLE diagnosis_symptoms ( 587 diagnosis_id BIGINT NOT NULL, 588 symptom_id BIGINT NOT NULL, 589 590 CONSTRAINT diagnosis_symptoms_PK 591 PRIMARY KEY (diagnosis_id, symptom_id), 592 593 CONSTRAINT diagnosis_symptoms_diagnosis_FK 594 FOREIGN KEY (diagnosis_id) 595 REFERENCES diagnosis(diagnosis_id) 596 ON DELETE RESTRICT, 597 598 CONSTRAINT diagnosis_symptoms_symptom_FK 599 FOREIGN KEY (symptom_id) 600 REFERENCES symptoms(symptom_id) 601 ON DELETE RESTRICT 602 ); 603 604 CREATE TABLE specialization_procedures ( 605 specialization_id BIGINT NOT NULL, 606 procedure_id BIGINT NOT NULL, 607 608 CONSTRAINT specialization_procedures_PK 609 PRIMARY KEY (specialization_id, procedure_id), 610 611 CONSTRAINT specialization_procedures_specialization_FK 612 FOREIGN KEY (specialization_id) 613 REFERENCES doctor_specialization(specialization_id) 614 ON DELETE RESTRICT, 615 616 CONSTRAINT specialization_procedures_procedure_FK 617 FOREIGN KEY (procedure_id) 618 REFERENCES procedures(procedure_id) 619 ON DELETE RESTRICT 620 ); 621 622 623 624 CREATE TABLE diagnosis_procedures ( 625 diagnosis_id BIGINT NOT NULL, 626 procedure_id BIGINT NOT NULL, 627 628 CONSTRAINT diagnosis_procedures_PK 629 PRIMARY KEY (diagnosis_id, procedure_id), 630 631 CONSTRAINT diagnosis_procedures_diagnosis_FK 632 FOREIGN KEY (diagnosis_id) 633 REFERENCES diagnosis(diagnosis_id) 634 ON DELETE RESTRICT, 635 636 CONSTRAINT diagnosis_procedures_procedure_FK 637 FOREIGN KEY (procedure_id) 638 REFERENCES procedures(procedure_id) 639 ON DELETE RESTRICT 640 ); 641 642 CREATE TABLE department_procedures ( 643 department_id BIGINT NOT NULL , 644 procedure_id BIGINT NOT NULL, 645 646 CONSTRAINT department_procedures_PK 647 PRIMARY KEY (department_id, procedure_id), 648 649 CONSTRAINT department_procedures_department_FK 650 FOREIGN KEY (department_id) 651 REFERENCES departments(department_id) 652 ON DELETE RESTRICT, 653 654 CONSTRAINT department_procedures_procedure_FK 655 FOREIGN KEY (procedure_id) 656 REFERENCES procedures(procedure_id) 657 ON DELETE RESTRICT 658 ); 659 862 bill_id BIGINT PRIMARY KEY, 863 864 total_cost DECIMAL NOT NULL, 865 866 payment_status TEXT NOT NULL, 867 payment_date DATE, 868 869 created_at TIMESTAMP DEFAULT now(), 870 871 record_id BIGINT NOT NULL, 872 admin_id BIGINT NOT NULL, 873 874 CONSTRAINT billing_cost_chk CHECK (total_cost >= 0), 875 CONSTRAINT billing_status_chk 876 CHECK (payment_status IN ('PENDING', 'PAID', 'CANCELLED')), 877 878 FOREIGN KEY (record_id) 879 REFERENCES medical_records(record_id) 880 ON DELETE RESTRICT, 881 882 FOREIGN KEY (admin_id) 883 REFERENCES admin(admin_id) 884 ON DELETE RESTRICT 885 ); 660 886 661 887 CREATE TABLE billing_procedures ( 662 bill_id BIGINT NOT NULL, 663 procedure_id BIGINT NOT NULL, 664 665 CONSTRAINT billing_procedures_PK 666 PRIMARY KEY (bill_id, procedure_id), 667 668 CONSTRAINT billing_procedures_bill_FK 669 FOREIGN KEY (bill_id) 670 REFERENCES billing(bill_id) 671 ON DELETE RESTRICT, 672 673 CONSTRAINT billing_procedures_procedure_FK 674 FOREIGN KEY (procedure_id) 675 REFERENCES procedures(procedure_id) 676 ON DELETE RESTRICT 677 ); 678 679 680 888 bill_id BIGINT NOT NULL, 889 procedure_id BIGINT NOT NULL, 890 891 CONSTRAINT billing_procedures_PK 892 PRIMARY KEY (bill_id, procedure_id), 893 894 CONSTRAINT billing_procedures_bill_FK 895 FOREIGN KEY (bill_id) 896 REFERENCES billing(bill_id) 897 ON DELETE RESTRICT, 898 899 CONSTRAINT billing_procedures_procedure_FK 900 FOREIGN KEY (procedure_id) 901 REFERENCES procedures(procedure_id) 902 ON DELETE RESTRICT 903 ); 681 904 682 905 CREATE TABLE billing_lab_tests ( 683 bill_id BIGINT NOT NULL, 684 test_id BIGINT NOT NULL, 685 686 CONSTRAINT billing_lab_tests_PK 687 PRIMARY KEY (bill_id, test_id), 688 689 CONSTRAINT billing_lab_tests_bill_FK 690 FOREIGN KEY (bill_id) 691 REFERENCES billing(bill_id) 692 ON DELETE RESTRICT, 693 694 CONSTRAINT billing_lab_tests_test_FK 695 FOREIGN KEY (test_id) 696 REFERENCES lab_tests(test_id) 697 ON DELETE RESTRICT 698 ); 699 700 701 702 CREATE TABLE doctor_medical_records ( 703 doctor_id BIGINT NOT NULL, 704 record_id BIGINT NOT NULL, 705 706 CONSTRAINT doctor_medical_records_PK 707 PRIMARY KEY (doctor_id, record_id), 708 709 CONSTRAINT doctor_medical_records_doctor_FK 710 FOREIGN KEY (doctor_id) 711 REFERENCES doctors(doctor_id) 712 ON DELETE RESTRICT, 713 714 CONSTRAINT doctor_medical_records_record_FK 715 FOREIGN KEY (record_id) 716 REFERENCES medical_records(record_id) 717 ON DELETE RESTRICT 718 ); 719 720 721 722 CREATE TABLE diagnosis_medical_records ( 723 diagnosis_id BIGINT NOT NULL, 724 record_id BIGINT NOT NULL, 725 726 CONSTRAINT diagnosis_medical_records_PK 727 PRIMARY KEY (diagnosis_id, record_id), 728 729 CONSTRAINT diagnosis_medical_records_diagnosis_FK 730 FOREIGN KEY (diagnosis_id) 731 REFERENCES diagnosis(diagnosis_id) 732 ON DELETE RESTRICT, 733 734 CONSTRAINT diagnosis_medical_records_record_FK 735 FOREIGN KEY (record_id) 736 REFERENCES medical_records(record_id) 737 ON DELETE RESTRICT 738 ); 739 740 741 CREATE TABLE prescription_medical_records ( 742 prescription_id BIGINT NOT NULL, 743 record_id BIGINT NOT NULL, 744 745 dosage TEXT NOT NULL, 746 frequency TEXT NOT NULL, 747 duration TEXT NOT NULL, 748 749 notes TEXT, 750 751 CONSTRAINT prescription_medical_records_PK 752 PRIMARY KEY (prescription_id, record_id), 753 754 CONSTRAINT prescription_medical_records_prescription_FK 755 FOREIGN KEY (prescription_id) 756 REFERENCES prescriptions(prescription_id) 757 ON DELETE RESTRICT, 758 759 CONSTRAINT prescription_medical_records_record_FK 760 FOREIGN KEY (record_id) 761 REFERENCES medical_records(record_id) 762 ON DELETE RESTRICT 763 ); 764 765 766 767 CREATE TABLE medical_report_lab_results ( 768 report_id BIGINT NOT NULL, 769 result_id BIGINT NOT NULL, 770 771 CONSTRAINT medical_report_lab_results_PK 772 PRIMARY KEY (report_id, result_id), 773 774 CONSTRAINT medical_report_lab_results_report_FK 775 FOREIGN KEY (report_id) 776 REFERENCES medical_report(report_id) 777 ON DELETE RESTRICT, 778 779 CONSTRAINT medical_report_lab_results_result_FK 780 FOREIGN KEY (result_id) 781 REFERENCES lab_results(result_id) 782 ON DELETE RESTRICT 783 ); 784 785 786 787 CREATE TABLE performed_procedures ( 788 performed_id BIGINT, 789 790 procedure_id BIGINT NOT NULL, 791 doctor_id BIGINT NOT NULL, 792 patient_id BIGINT NOT NULL, 793 diagnosis_id BIGINT, 794 795 procedure_date DATE NOT NULL, 796 notes TEXT, 797 798 CONSTRAINT performed_procedures_PK 799 PRIMARY KEY (performed_id), 800 801 CONSTRAINT performed_procedures_procedure_FK 802 FOREIGN KEY (procedure_id) 803 REFERENCES procedures(procedure_id) 804 ON DELETE RESTRICT, 805 806 CONSTRAINT performed_procedures_doctor_FK 807 FOREIGN KEY (doctor_id) 808 REFERENCES doctors(doctor_id) 809 ON DELETE RESTRICT, 810 811 CONSTRAINT performed_procedures_patient_FK 812 FOREIGN KEY (patient_id) 813 REFERENCES patients(patient_id) 814 ON DELETE RESTRICT, 815 816 CONSTRAINT performed_procedures_diagnosis_FK 817 FOREIGN KEY (diagnosis_id) 818 REFERENCES diagnosis(diagnosis_id) 819 ON DELETE RESTRICT 820 ); 821 822 CREATE TABLE performed_lab_tests ( 823 performed_test_id BIGINT, 824 825 test_id BIGINT NOT NULL, 826 patient_id BIGINT NOT NULL, 827 doctor_id BIGINT NOT NULL, 828 technician_id BIGINT NOT NULL, 829 830 test_date DATE NOT NULL, 831 notes TEXT, 832 833 CONSTRAINT performed_lab_tests_PK 834 PRIMARY KEY (performed_test_id), 835 836 CONSTRAINT performed_lab_tests_test_FK 837 FOREIGN KEY (test_id) 838 REFERENCES lab_tests(test_id) 839 ON DELETE RESTRICT, 840 841 CONSTRAINT performed_lab_tests_patient_FK 842 FOREIGN KEY (patient_id) 843 REFERENCES patients(patient_id) 844 ON DELETE RESTRICT, 845 846 CONSTRAINT performed_lab_tests_doctor_FK 847 FOREIGN KEY (doctor_id) 848 REFERENCES doctors(doctor_id) 849 ON DELETE RESTRICT, 850 851 CONSTRAINT performed_lab_tests_technician_FK 852 FOREIGN KEY (technician_id) 853 REFERENCES lab_technician(technician_id) 854 ON DELETE RESTRICT 855 ); 856 857 CREATE TABLE medical_record_lab_results ( 858 record_id BIGINT NOT NULL, 859 result_id BIGINT NOT NULL, 860 861 PRIMARY KEY (record_id, result_id), 862 863 FOREIGN KEY (record_id) 864 REFERENCES medical_records(record_id) 865 ON DELETE RESTRICT, 866 867 FOREIGN KEY (result_id) 868 REFERENCES lab_results(result_id) 869 ON DELETE RESTRICT 870 ); 871 872 873 874 875 CREATE TABLE medical_record_procedures ( 876 record_id BIGINT NOT NULL, 877 procedure_id BIGINT NOT NULL, 878 879 PRIMARY KEY (record_id, procedure_id), 880 881 FOREIGN KEY (record_id) 882 REFERENCES medical_records(record_id) 883 ON DELETE RESTRICT, 884 885 FOREIGN KEY (procedure_id) 886 REFERENCES procedures(procedure_id) 887 ON DELETE RESTRICT 888 ); 889 890 891 892 CREATE TABLE medical_record_symptoms ( 893 record_id BIGINT NOT NULL, 894 symptom_id BIGINT NOT NULL, 895 severity TEXT, 896 897 PRIMARY KEY (record_id, symptom_id), 898 899 FOREIGN KEY (record_id) 900 REFERENCES medical_records(record_id) 901 ON DELETE RESTRICT, 902 903 FOREIGN KEY (symptom_id) 904 REFERENCES symptoms(symptom_id) 905 ON DELETE RESTRICT 906 ); 907 908 CREATE TABLE medical_record_allergies ( 909 record_id BIGINT NOT NULL, 910 allergy_id BIGINT NOT NULL, 911 reaction TEXT, 912 severity TEXT, 913 914 PRIMARY KEY (record_id, allergy_id), 915 916 FOREIGN KEY (record_id) 917 REFERENCES medical_records(record_id) 918 ON DELETE RESTRICT, 919 920 FOREIGN KEY (allergy_id) 921 REFERENCES allergies(allergy_id) 922 ON DELETE RESTRICT 923 ); 924 906 bill_id BIGINT NOT NULL, 907 test_id BIGINT NOT NULL, 908 909 CONSTRAINT billing_lab_tests_PK 910 PRIMARY KEY (bill_id, test_id), 911 912 CONSTRAINT billing_lab_tests_bill_FK 913 FOREIGN KEY (bill_id) 914 REFERENCES billing(bill_id) 915 ON DELETE RESTRICT, 916 917 CONSTRAINT billing_lab_tests_test_FK 918 FOREIGN KEY (test_id) 919 REFERENCES lab_tests(test_id) 920 ON DELETE RESTRICT 921 ); 925 922 926 923 COMMIT; 927
Note:
See TracChangeset
for help on using the changeset viewer.
