Changes between Version 2 and Version 3 of Normalization


Ignore:
Timestamp:
09/19/26 15:59:17 (4 days ago)
Author:
236021
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • Normalization

    v2 v3  
    3232  test_id, test_name, test_description, test_cost,
    3333  lab_result_id, lab_result_value, lab_result_date,
    34   performed_test_id, pt_test_id, pt_patient_id, pt_doctor_id, pt_technician_id, pt_test_date, pt_notes,
     34  performed_test_id, pt_test_id, pt_patient_id, pt_doctor_id, pt_technician_id, pt_test_date, pt_notes, pt_created_at,
    3535
    3636  prescription_id, medication_name,
    … …  
    4747  referral_id, referral_reason, referral_date, ref_from_doctor_id, ref_to_doctor_id,
    4848  report_id, report_description, report_date, report_doctor_id,
    49   bill_id, total_cost, payment_status, payment_date, billing_admin_id
     49  bill_id, total_cost, payment_status, payment_date, billing_admin_id, billing_created_at
    5050}
    5151}}}
    … …  
    131131F17 lab_result_id     -> lab_result_value, lab_result_date, test_id
    132132F18 performed_test_id -> pt_test_id, pt_patient_id, pt_doctor_id, pt_technician_id,
    133                           pt_test_date, pt_notes
     133                          pt_test_date, pt_notes, pt_created_at
    134134}}}
    135135
    … …  
    167167
    168168{{{
    169 F24 bill_id -> total_cost, payment_status, payment_date, record_id, billing_admin_id
     169F24 bill_id -> total_cost, payment_status, payment_date, record_id, billing_admin_id, billing_created_at
    170170}}}
    171171
    … …  
    203203These chains matter because whatever gets reached this way (procedure_id, test_id,
    204204prescription_id, record_id, patient_id, level_id, specialization_id, department_id)
    205 does not need to be included in the key separately — it is already recoverable once
     205does not need to be included in the key separately as it is already recoverable once
    206206its determinant is present.
    207207
    … …  
    278278
    279279From F18:
    280 performed_test_id -> pt_test_id, pt_patient_id, pt_doctor_id, pt_technician_id, pt_test_date, pt_notes
     280performed_test_id -> pt_test_id, pt_patient_id, pt_doctor_id, pt_technician_id, pt_test_date, pt_notes, pt_created_at
    281281
    282282From F20:
    … …  
    299299
    300300From F24:
    301 bill_id -> total_cost, payment_status, payment_date, record_id, billing_admin_id
     301bill_id -> total_cost, payment_status, payment_date, record_id, billing_admin_id, billing_created_at
    302302
    303303From F25 using record_id and symptom_id:
    … …  
    437437lab_result_id    -> lab_result_value, lab_result_date, test_id
    438438
    439 performed_test_id-> pt_test_id, pt_patient_id, pt_doctor_id, pt_technician_id, pt_test_date, pt_notes
     439performed_test_id-> pt_test_id, pt_patient_id, pt_doctor_id, pt_technician_id, pt_test_date, pt_notes, pt_created_at
    440440
    441441record_id        -> patient_id
    … …  
    449449report_id        -> report_description, report_date, record_id, report_doctor_id
    450450
    451 bill_id          -> total_cost, payment_status, payment_date, record_id, billing_admin_id
     451bill_id          -> total_cost, payment_status, payment_date, record_id, billing_admin_id, billing_created_at
    452452
    453453{record_id, symptom_id}      -> record_symptom_severity
    … …  
    480480LabTests(test_id, test_name, test_description, test_cost)
    481481LabResults(lab_result_id, lab_result_value, lab_result_date, test_id)
    482 PerformedLabTests(performed_test_id, pt_test_id, pt_patient_id, pt_doctor_id, pt_technician_id, pt_test_date, pt_notes)
     482PerformedLabTests(performed_test_id, pt_test_id, pt_patient_id, pt_doctor_id, pt_technician_id, pt_test_date, pt_notes, pt_created_at)
    483483MedicalRecords(record_id, patient_id)
    484484Allergies(allergy_id, allergy_name, allergy_severity)
    … …  
    486486Referrals(referral_id, referral_reason, referral_date, record_id, ref_from_doctor_id, ref_to_doctor_id)
    487487MedicalReports(report_id, report_description, report_date, record_id, report_doctor_id)
    488 Billing(bill_id, total_cost, payment_status, payment_date, record_id, billing_admin_id)
     488Billing(bill_id, total_cost, payment_status, payment_date, record_id, billing_admin_id, billing_created_at)
    489489MedicalRecordSymptoms(record_id, symptom_id, record_symptom_severity)
    490490MedicalRecordAllergies(record_id, allergy_id, record_allergy_reaction, record_allergy_severity)
    … …  
    640640Dependency preservation: F17 preserved entirely in `LabResults`.
    641641
    642 '''`PerformedLabTests`(performed_test_id, pt_test_id, pt_patient_id, pt_doctor_id, pt_technician_id, pt_test_date, pt_notes)'''
    643 
    644 R18 = R17 - {pt_test_id, pt_patient_id, pt_doctor_id, pt_technician_id, pt_test_date, pt_notes}
     642'''`PerformedLabTests`(performed_test_id, pt_test_id, pt_patient_id, pt_doctor_id, pt_technician_id, pt_test_date, pt_notes, pt_created_at)'''
     643
     644R18 = R17 - {pt_test_id, pt_patient_id, pt_doctor_id, pt_technician_id, pt_test_date, pt_notes, pt_created_at}
    645645
    646646Lossless join: Shared column `performed_test_id`. `performed_test_id -> PerformedLabTests` holds (F18).
    … …  
    688688Dependency preservation: F23 preserved entirely in `MedicalReports`.
    689689
    690 '''`Billing`(bill_id, total_cost, payment_status, payment_date, record_id, billing_admin_id)'''
    691 
    692 R24 = R23 - {total_cost, payment_status, payment_date, billing_admin_id}
     690'''`Billing`(bill_id, total_cost, payment_status, payment_date, record_id, billing_admin_id, billing_created_at)'''
     691
     692R24 = R23 - {total_cost, payment_status, payment_date, billing_admin_id, billing_created_at}
    693693
    694694Lossless join: Shared column `bill_id`. `bill_id -> Billing` holds (F24).
    … …  
    950950* {{{LabTests(test_id, test_name, test_description, test_cost)}}}
    951951* {{{LabResults(lab_result_id, lab_result_value, lab_result_date, test_id)}}}
    952 * {{{PerformedLabTests(performed_test_id, pt_test_id, pt_patient_id, pt_doctor_id, pt_technician_id, pt_test_date, pt_notes)}}}
     952* {{{PerformedLabTests(performed_test_id, pt_test_id, pt_patient_id, pt_doctor_id, pt_technician_id, pt_test_date, pt_notes, pt_created_at)}}}
    953953* {{{MedicalRecords(record_id, patient_id)}}}
    954954* {{{Referrals(referral_id, referral_reason, referral_date, record_id, ref_from_doctor_id, ref_to_doctor_id)}}}
    955955* {{{MedicalReports(report_id, report_description, report_date, record_id, report_doctor_id)}}}
    956 * {{{Billing(bill_id, total_cost, payment_status, payment_date, record_id, billing_admin_id)}}}
     956* {{{Billing(bill_id, total_cost, payment_status, payment_date, record_id, billing_admin_id, billing_created_at)}}}
    957957* {{{Prescriptions(prescription_id, medication_name)}}}
    958958* {{{PrescriptionRestrictions(restriction_id, restriction_description, prescription_id)}}}