Community Pharmacy Mart Relationships
This ERD shows all Community Pharmacy mart models and their full column schemas in one view, including all definitively evidenced mart-to-mart relationships.
erDiagram
mart_attendance_register {
integer attendance_register_id PK
varchar organisation PK
boolean is_deleted
varchar branch_identifier
varchar pharmacy_name
date attendance_date
varchar pharmacist_registration_number
varchar pharmacist_name
varchar psc_time_signed_in
varchar system_time_signed_in
varchar psc_time_signed_out
varchar system_time_signed_out
varchar national_practice_code
varchar row_id
date last_updated_date
}
mart_attendance_absences {
integer leave_register_id PK
varchar organisation PK
integer attendance_register_id FK
boolean is_deleted
varchar psc_sign_out_time
varchar system_sign_out_time
varchar psc_sign_in_time
varchar actual_sign_in_time
varchar destination
varchar reason_for_leaving
varchar row_id
date last_updated_date
}
mart_prescribing {
integer prescription_item_id PK
varchar organisation PK
integer prescription_id
varchar prescription_guid
varchar prescription_item_guid
boolean prescription_item_deleted
boolean prescription_deleted
varchar unique_patient_id
integer patient_id
varchar patient_full_name
integer prescription_type_id
varchar prescription_type
integer prescription_source_id
varchar prescription_exemption_code
timestamp prescription_added_date_time
integer preparation_code_id
integer virtual_product_id
numeric quantity
numeric original_quantity
varchar dosage
varchar endorsement_text
numeric prescription_cost
boolean is_non_mds
integer rds_type_id
boolean is_opd_applied
boolean is_owed
date owing_date
numeric owing_quantity
varchar pharmacy_name
varchar pharmacy_guid
integer pharmacy_address
varchar pharmacy_rx_id
varchar branch_identifier
varchar national_practice_code
integer prescriber_id
integer prescriber_type_id
integer prescriber_prescribing_organisation_id
varchar prescriber_surname
varchar prescriber_first_name
varchar prescriber_post_code
varchar prescriber_registration_number
integer region_id
integer pharmacy_id
varchar surgery_name
varchar surgery_registration_code
integer repeat_number
varchar patient_nhs_number
boolean has_controlled_drugs
boolean all_controlled_drugs
date prescription_expiry_date
date prescription_date
integer prescription_status_id
varchar prescription_status
integer prescription_item_status_id
varchar prescription_item_status
integer treatment_type_id
varchar treatment_type
boolean is_eps_england
boolean is_eps_scotland
boolean is_eps_wales
varchar digital_exemption_code
varchar digital_exemption_description
varchar prescription_reference
varchar prescription_identifier
integer number_of_claims
timestamp first_claim_date_time
integer controlled_drug_schedule
timestamp dispense_notification_completed_date_time
timestamp dispensed_date_time
date patient_registration_date
array mapping_preparations
array bnf_chapter_refs
date prescription_ingest_date
date prescription_item_ingest_date
date prescribing_surgery_ingest_date
varchar row_id
date last_updated_date
}
mart_dispensing {
integer prescription_item_dispensed_id PK
varchar organisation PK
integer prescription_item_id FK
varchar unique_patient_id FK
boolean dispensed_item_deleted
varchar prescription_item_dispensed_guid
integer prescription_id
varchar prescription_type
date prescribed_date
varchar prescribed_endorsement
varchar prescribed_dosage
numeric prescribed_quantity
boolean is_opd_applied
varchar prescribed_product
boolean is_owed
date owing_date
numeric owing_quantity
timestamp dispensed_date_time
integer dispensed_pack_code_id
numeric dispensed_quantity
numeric dispensed_cost
numeric dispensed_price
boolean dispensed_not_given
boolean is_dispensing_activity
numeric product_pack_size
numeric dispensed_packs
varchar product_name
varchar manufacturer_name
integer product_pack_type
integer product_pack_code_id
integer virtual_product_pack_id
varchar virtual_product_name
integer dispensed_virtual_product_id
varchar dispensed_drug_code
integer prescribed_virtual_product_id
varchar dispensed_pip_code
varchar pharmacy_name
varchar surgery_name
varchar surgery_registration_code
varchar patient_full_name
date patient_birth_date
varchar patient_house_number_flat_number
varchar patient_number_and_street
varchar patient_village
varchar patient_town
varchar patient_post_code
integer nursing_home_id
varchar nursing_home_name
varchar nursing_home_type
varchar nursing_home_house_name_flat_number
varchar nursing_home_number_and_street
varchar nursing_home_village
varchar nursing_home_town
varchar nursing_home_post_code
integer nursing_home_address_id
varchar nursing_home_registration
boolean off_site_fulfilment
array bnf_chapter_refs
varchar uom_description
varchar preparation_description
varchar branch_identifier
varchar national_practice_code
boolean non_MDS
timestamp audited_clinical_check_date
integer audited_clinical_check_pharmacist_id
integer audited_clinical_check_user_in_role_id
varchar external_accuracy_check_user_and_date
varchar row_id
date last_updated_date
}
mart_patient {
varchar unique_patient_id PK
varchar organisation PK
boolean is_deleted
integer patient_id
integer pharmacy_id
varchar patient_full_name
varchar sex
varchar ethnicity
timestamp last_activity_date_time
date last_dispensing_date
date birth_date
date death_date
boolean is_deceased
timestamp added_date_time
varchar exemption_code
boolean is_active
boolean is_new
integer patient_age
varchar patient_age_bucket
varchar patient_nhs_number
varchar patient_nhs_number_hashed
integer eps_registration_status_id
varchar eps_registration_status
varchar additional_service_type
boolean is_collection
boolean is_delivery
boolean is_pmr_locked
varchar pharmacy_name
varchar branch_identifier
varchar national_practice_code
integer imd_decile
varchar email
varchar home_phone_number
varchar mobile_phone_number
varchar work_phone_number
varchar fax_number
varchar row_id
date last_updated_date
}
mart_dmr {
integer dmr_id PK
integer eligibility_criterium_id PK
varchar organisation PK
varchar unique_patient_id FK
varchar dmr_guid
varchar description
boolean is_deleted
integer patient_id
varchar pharmacy_name
varchar rx_id
varchar patient_full_name
timestamp date_added
timestamp date_part_0_completed
timestamp date_part_1_completed
timestamp date_part_2_completed
integer advice_id
varchar advice_description
integer consent_status_id
varchar consent_status_description
boolean deleted
integer pharmacist_id
varchar pharmacist_full_name
integer cancel_reason_id
varchar cancel_reason_description
date date_part_2_contact_attempt_1
date date_part_2_contact_attempt_2
boolean is_claimable
varchar branch_identifier
varchar national_practice_code
varchar row_id
date last_updated_date
}
mart_fridge_log {
integer fridge_log_id PK
varchar organisation PK
boolean is_deleted
varchar pharmacy_name
timestamp date_added
varchar location_name
numeric minimum_temperature
numeric maximum_temperature
varchar notes
varchar branch_identifier
varchar national_practice_code
varchar row_id
date last_updated_date
}
mart_mds {
integer mds_item_id PK
varchar organisation PK
varchar mds_item_guid
boolean is_deleted
integer pharmacy_id
integer patient_id
varchar unique_patient_id
varchar patient_guid
integer nursing_home_id
integer preparation_code_id
integer pack_code_id
varchar pharmacy_name
boolean patient_deleted
varchar patient_full_name
boolean patient_deceased
timestamp date_added
timestamp dispensed_date
varchar nursing_home_name
varchar nursing_home_type
numeric quantity
varchar product_name
varchar branch_identifier
varchar national_practice_code
varchar row_id
date last_updated_date
}
mart_mds_emar {
integer emar_history_id PK
integer emar_history_item_id PK
varchar organisation PK
varchar emar_history_guid
integer pharmacy_id
varchar pharmacy_name
integer patient_id
varchar patient_guid
boolean patient_deleted
boolean patient_deceased
varchar nursing_home_name
integer emar_system_type_id
integer nursing_home_id
varchar nursing_home_guid
varchar nursing_home_type
integer emar_status_id
timestamp date_added
timestamp date_sent
varchar emar_history_item_guid
varchar product_name
integer preparation_code_id
integer pack_code_id
numeric quantity
varchar branch_identifier
varchar national_practice_code
timestamp MDS_inactive
boolean is_deleted
varchar row_id
date last_updated_date
}
mart_nms {
integer nms_id PK
varchar organisation PK
varchar nms_guid
timestamp date_nms_added
timestamp date_nms_modified
integer patient_id
varchar unique_patient_id
varchar patient_full_name
integer patient_age
varchar patient_nhs_number
varchar patient_home_number
varchar patient_mobile_number
integer pharmacist_id
integer prescription_id
boolean declined
varchar region_ids
varchar nms_item_name
integer decline_pharmacist_id
boolean escaped
boolean asthma_copd
boolean diabetes
boolean antiplatelet_anticoagulant_therapy
boolean hypertension
boolean engagement_non_provided
boolean engagement_diet
boolean engagement_physical_activity
boolean engagement_sexual_health
boolean engagement_weight_management
boolean engagement_smoking
boolean engagement_alcohol
integer engagement_pharmacist_id
timestamp date_engagement_completed
boolean intervention_attended
varchar intervention_not_attended_reason
boolean intervention_non_provided
boolean intervention_diet
boolean intervention_physical_activity
boolean intervention_sexual_health
boolean intervention_weight_management
boolean intervention_smoking
boolean intervention_alcohol
integer intervention_pharmacist_id
timestamp date_intervention_completed
boolean followup_attended
varchar followup_not_attended_reason
boolean followup_non_provided
boolean followup_diet
boolean followup_physical_activity
boolean followup_sexual_health
boolean followup_weight_management
boolean followup_smoking
boolean followup_alcohol
integer followup_pharmacist_id
timestamp date_followup_completed
timestamp date_exited
integer prescriber_prescribing_organisation_id
integer session_id
varchar session_parts
varchar hospital_discharge_note
varchar other_note
boolean hypercholesterolaemia
boolean osteoporosis
boolean gout
boolean glaucoma
boolean epilepsy
boolean parkinsons_disease
boolean urinary_incontinence_retention
boolean heart_failure
boolean acute_coronary_syndromes
boolean atrial_fibrillation
boolean risk_of_embolism
boolean stroke_or_transient_ischemic_attack
boolean coronary_heart_disease
boolean antidepressant
integer drug_given_previously_status_id
varchar nms_status
varchar nms_intervention_status
varchar nms_followup_status
varchar pharmacy_name
varchar branch_identifier
varchar national_practice_code
boolean is_deleted
varchar row_id
date last_updated_date
}
mart_ordering {
integer order_history_item_id PK
varchar organisation PK
boolean is_deleted
integer order_history_id
integer order_pad_id
varchar order_history_item_description
integer pack_code_id
integer order_quantity
integer received_quantity
integer book_in_quantity
numeric cost
varchar pip_code
varchar order_code
varchar order_status_reason
varchar order_item_status_reason
date date_actioned
varchar order_history_item_guid
varchar supplied_by
numeric retail_price
numeric vat_rate
integer pharmacy_id
integer wholesaler_id
varchar order_reference
integer order_history_status_id
timestamp order_history_added_date_time
timestamp order_history_modified_date_time
varchar pharmacy_name
varchar wholesaler_code
varchar supplier_codes
varchar wholesaler_account_number
varchar wholesaler_name
integer wholesaler_type_id
numeric product_pack_size
varchar product_name
varchar manufacturer_name
integer product_pack_type
integer product_pack_code_id
integer virtual_product_pack_id
varchar row_id
date last_updated_date
varchar branch_identifier
varchar national_practice_code
varchar order_status
integer order_item_status_id
varchar order_item_status
}
mart_rds {
integer ods_order_id PK
integer ods_order_item_id PK
varchar organisation PK
integer pharmacy_id
varchar prescription_guid
varchar prescription_item_guid
timestamp order_added_date
timestamp order_modified_date
timestamp order_item_added_date
timestamp order_item_modified_date
boolean local_dispense
varchar description
varchar item_status_reason
varchar accuracy_check_user
timestamp date_accuracy_checked
timestamp date_clinically_checked
varchar clinical_check_user
varchar patient_full_name
varchar action_description
numeric quantity_ordered
numeric quantity_received
varchar branch_identifier
varchar national_practice_code
varchar row_id
date last_updated_date
}
mart_stock {
integer stock_item_id PK
varchar organisation PK
boolean is_deleted
varchar stock_item_guid
timestamp stock_item_added_date_time
timestamp stock_item_modified_date_time
integer stock_level
integer minimum_stock
integer maximum_stock
integer pack_code_id
numeric product_pack_size
varchar product_name
varchar manufacturer_name
integer product_pack_type
integer product_pack_code_id
integer virtual_product_pack_id
integer pharmacy_id
varchar pharmacy_name
numeric pack_price
varchar pip_code
boolean is_generic_pack
boolean is_generic_preparation
boolean is_parallel_import
boolean is_discount_not_deducted
varchar drug_tariff_category
integer preparation_type_identifier
boolean is_acbs
integer contraceptive_identifier
integer dispensable_type_identifier
boolean is_special
timestamp dispensed_date_time
numeric dispensed_quantity
numeric dispensed_cost
numeric dispensed_price
varchar branch_identifier
varchar national_practice_code
varchar row_id
date last_updated_date
}
mart_attendance_register ||--o{ mart_attendance_absences : attendance_register_id
mart_prescribing ||--o{ mart_dispensing : prescription_item_id
mart_patient ||--o{ mart_dmr : unique_patient_id
mart_patient ||--o{ mart_dispensing : unique_patient_id