ERD

erDiagram
  mart_dispensing {
    INT prescription_item_dispensed_id PK
    VARCHAR organisation PK
    INT prescription_item_id FK
    VARCHAR unique_patient_id FK
  }

  dispensing {
    INT prescription_item_dispensed_id PK
    VARCHAR organisation PK
  }

  stg_prescription_item_dispensed {
    INT prescription_item_dispensed_id PK
    VARCHAR organisation PK
  }

  dim_product_pack {
    INT pack_code_id PK
  }

  dim_preparation {
    INT preparation_code_id PK
  }

  stg_pharmacy_patient {
    INT patient_id PK
    VARCHAR organisation PK
  }

  stg_address {
    INT address_id PK
    VARCHAR organisation PK
  }

  mart_prescribing {
    INT prescription_item_id PK
    VARCHAR organisation PK
  }

  mart_patient {
    VARCHAR unique_patient_id PK
    VARCHAR organisation PK
  }

  stg_prescription_item_dispensed ||--o{ dispensing : source
  dim_product_pack ||--o{ dispensing : product_context
  dim_preparation ||--o{ dispensing : prep_context
  stg_pharmacy_patient ||--o{ dispensing : patient_context
  stg_address ||--o{ dispensing : address_context
  mart_prescribing ||--o{ dispensing : prescribing_context
  dispensing ||--|| mart_dispensing : builds
  mart_dispensing }o--|| mart_prescribing : prescription_item_id
  mart_dispensing }o--|| mart_patient : unique_patient_id