Skip to main content

Data modeling suggestions for table joins

User

Give suggestions for data modeling i.e. table joins up to 4 nested levels, specify one-to-many or one-to-one

Cursor

Looking at the OpenEMR database schema, I can provide suggestions for data modeling with table joins up to 4 nested levels. Let me analyze the schema and identify key relationships and join patterns.

Core Patient-Centric Data Model​

Level 1: Patient Data Foundation​

  • patient_data (1) ←→ (1) patient_history (one-to-one)
  • patient_data (1) ←→ (M) addresses (one-to-many via foreign_id)
  • patient_data (1) ←→ (M) phone_numbers (one-to-many via foreign_id)

Level 2: Clinical Encounters​

  • patient_data (1) ←→ (M) form_encounter (one-to-many via pid)
  • form_encounter (1) ←→ (M) form_soap (one-to-many via encounter)
  • form_encounter (1) ←→ (M) form_vitals (one-to-many via encounter)

Level 3: Clinical Documentation​

  • form_encounter (1) ←→ (M) form_clinical_notes (one-to-many via encounter)
  • form_encounter (1) ←→ (M) form_observation (one-to-many via encounter)
  • form_encounter (1) ←→ (M) form_care_plan (one-to-many via encounter)

Level 4: Specialized Forms​

  • form_encounter (1) ←→ (M) form_eye_base (one-to-many via encounter)
  • form_eye_base (1) ←→ (1) form_eye_acuity (one-to-one via id)
  • form_eye_base (1) ←→ (1) form_eye_refraction (one-to-one via id)

Billing and Financial Data Model​

Level 1: Patient Insurance​

  • patient_data (1) ←→ (M) insurance_data (one-to-many via pid)
  • insurance_data (M) ←→ (1) insurance_companies (many-to-one via provider)

Level 2: Billing Transactions​

  • form_encounter (1) ←→ (M) billing (one-to-many via encounter)
  • billing (M) ←→ (1) codes (many-to-one via code)

Level 3: Payment Processing​

  • billing (1) ←→ (M) payments (one-to-many via encounter)
  • payments (M) ←→ (1) payment_gateway_details (many-to-one via service)

Level 4: Audit Trail​

  • payments (1) ←→ (M) payment_processing_audit (one-to-many via transaction_id)
  • payment_processing_audit (M) ←→ (1) users (many-to-one via action_user)

Medication and Prescription Data Model​

Level 1: Patient Medications​

  • patient_data (1) ←→ (M) prescriptions (one-to-many via patient_id)
  • prescriptions (M) ←→ (1) drugs (many-to-one via drug_id)

Level 2: Drug Inventory​

  • drugs (1) ←→ (M) drug_inventory (one-to-many via drug_id)
  • drug_inventory (M) ←→ (1) pharmacies (many-to-one via warehouse_id)

Level 3: Drug Sales​

  • drug_inventory (1) ←→ (M) drug_sales (one-to-many via inventory_id)
  • drug_sales (M) ←→ (1) form_encounter (many-to-one via encounter)

Level 4: Drug Templates​

  • drugs (1) ←→ (M) drug_templates (one-to-many via drug_id)
  • drug_templates (M) ←→ (1) list_options (many-to-one via dosage codes)

Laboratory and Procedure Data Model​

Level 1: Procedure Orders​

  • patient_data (1) ←→ (M) procedure_order (one-to-many via patient_id)
  • procedure_order (M) ←→ (1) procedure_providers (many-to-one via lab_id)

Level 2: Procedure Details​

  • procedure_order (1) ←→ (M) procedure_order_code (one-to-many via procedure_order_id)
  • procedure_order_code (M) ←→ (1) procedure_type (many-to-one via procedure codes)

Level 3: Procedure Results​

  • procedure_order_code (1) ←→ (M) procedure_report (one-to-many via order sequence)
  • procedure_report (1) ←→ (M) procedure_result (one-to-many via procedure_report_id)

Level 4: Result Validation​

  • procedure_result (M) ←→ (1) users (many-to-one via review status)
  • procedure_result (M) ←→ (1) list_options (many-to-one via abnormal flags)

User and Access Control Data Model​

Level 1: User Management​

  • users (1) ←→ (1) users_secure (one-to-one via id)
  • users (M) ←→ (1) facility (many-to-one via facility_id)

Level 2: User Permissions​

  • users (1) ←→ (M) module_acl_user_settings (one-to-many via user_id)
  • module_acl_user_settings (M) ←→ (1) module_acl_sections (many-to-one via section_id)

Level 3: Module Access​

  • module_acl_sections (1) ←→ (M) modules (one-to-many via module_id)
  • modules (1) ←→ (M) module_configuration (one-to-many via module_id)

Level 4: Configuration Details​

  • module_configuration (M) ←→ (1) users (many-to-one via created_by)
  • module_configuration (M) ←→ (1) users (many-to-one via updated_by)

Key Join Recommendations​

  1. Always use indexed foreign keys for performance
  2. Implement proper constraints (CASCADE DELETE, etc.)
  3. Consider denormalization for frequently accessed data
  4. Use composite keys where appropriate (e.g., encounter + pid)
  5. Implement soft deletes for audit trails
  6. Consider partitioning for large tables like log and documents

This model provides a solid foundation for an EHR system while maintaining data integrity and performance through proper normalization and indexing strategies.