Pharmacy Information System Master Data & Configuration
Home / Clinical Modules / Pharmacy Information System / Master Data

Pharmacy Information System Master Data & Configuration

Master Data Inventory

ID Data Set Source Approx. Records Owner Update Frequency Approver
MD-PIS-001 RxNorm Drug Database NLM RxNorm (subset licensed for UAE use) ~120,000 Pharmacy Informatics Monthly (aligned with RxNorm releases) Chief Pharmacist
MD-PIS-002 Facility Formulary P&T Committee decisions + RxNorm/EDE mapping ~3,000 Chief Pharmacist Quarterly (P&T); ad-hoc for safety alerts P&T Committee
MD-PIS-003 Drug Interaction Database Commercial CDS vendor (e.g., First Databank, Medi-Span) or open-source ~50,000 Pharmacy Informatics Monthly vendor update Medication Safety Committee
MD-PIS-004 Dose Range Check Rules Clinical guidelines + manufacturer labeling ~5,000 Clinical Pharmacy Lead Annual review; ad-hoc for safety alerts Pharmacy & Therapeutics Committee
MD-PIS-005 UAE MOH Controlled Substance Schedules UAE MOH (Federal Law No. 14 of 1995 and amendments; Decree-Law 30/2021) ~500 Chief Pharmacist / Compliance Officer As legislated UAE MOH (external) + Facility Compliance Committee
MD-PIS-006 Administration Routes SNOMED CT / HL7 standard route codes ~60 Pharmacy Informatics Rare; annual review Clinical Informatics Lead
MD-PIS-007 Dose Units UCUM (Unified Code for Units of Measure) ~50 Pharmacy Informatics Rare; annual review Clinical Informatics Lead
MD-PIS-008 Therapeutic Drug Classes ATC classification ~800 Clinical Pharmacy Annual P&T Committee
MD-PIS-009 IV Solution Base Products Facility formulary ~30 IV Pharmacy Supervisor With formulary changes Chief Pharmacist
MD-PIS-010 Medication Frequencies Facility-defined (QD, BID, TID, Q4H, PRN, etc.) ~30 Pharmacy Informatics Rare; annual review Medication Safety Committee
MD-PIS-011 Pharmacy Locations Facility layout (central, satellite, ADC locations) ~20 Pharmacy Director On facility changes Hospital Operations Committee
MD-PIS-012 Vendor / Supplier Directory Procurement contracts ~50 Pharmacy Inventory Manager On contract changes Procurement Committee
MD-PIS-013 NABIDH/Malaffi Medication & Facility Codes DHA/DOH HIE specifications <1,000 IT / Integration Team On HIE spec changes IT Director
MD-PIS-014 Antimicrobial Stewardship Rules Local guidelines + international best practice ~300 Antimicrobial Stewardship Pharmacist Quarterly Antimicrobial Stewardship Committee
MD-PIS-015 Compounding Recipes (IV & Non-Sterile) Internal protocols + manufacturer guidance ~400 IV Pharmacist Annual; ad-hoc for new products Pharmacy Supervisor
MD-PIS-016 Controlled Substance Transaction Reasons Internal policy aligned with MOH ~30 Pharmacy Supervisor Rare Compliance Officer
MD-PIS-017 Inventory Transaction Types Internal inventory policy ~20 Pharmacy Inventory Manager Rare Pharmacy Director
MD-PIS-018 Medication Administration Status Codes Internal eMAR configuration ~20 Nursing Informatics Rare Nursing Director + Pharmacy Director

Note: Patients, providers, encounters, facilities, departments, users, payers are shared entities and not redefined here; they are referenced via foreign keys as per module brief.

Setup Sequence

Dependency Diagram

flowchart TD A["MD-PIS-007: Dose Units"] --> C["MD-PIS-001: RxNorm Drug Database"] B["MD-PIS-006: Administration Routes"] --> C D["MD-PIS-005: UAE MOH Controlled Substance Schedules"] --> C E["MD-PIS-008: Therapeutic Drug Classes"] --> C F["MD-PIS-013: NABIDH/Malaffi Codes"] --> C C --> G["MD-PIS-002: Facility Formulary"] C --> H["MD-PIS-003: Drug Interaction Database"] C --> I["MD-PIS-004: Dose Range Check Rules"] C --> J["MD-PIS-009: IV Solution Base Products"] C --> K["MD-PIS-014: Antimicrobial Stewardship Rules"] C --> L["MD-PIS-015: Compounding Recipes"] M["MD-PIS-010: Medication Frequencies"] --> N["MD-PIS-018: eMAR Status Codes"] A --> N B --> N O["MD-PIS-011: Pharmacy Locations"] --> P["MD-PIS-017: Inventory Transaction Types"] Q["MD-PIS-012: Vendor / Supplier Directory"] --> R["Inventory & Purchasing Config"] G --> S["Clinical Workflows Ready"] H --> S I --> S J --> S K --> S L --> S M --> S N --> S O --> S P --> S Q --> S R --> S

  1. Foundational clinical terminology - MD-PIS-006: Administration Routes
    - MD-PIS-007: Dose Units
    - MD-PIS-008: Therapeutic Drug Classes

  2. Regulatory & external mappings - MD-PIS-005: UAE MOH Controlled Substance Schedules
    - MD-PIS-013: NABIDH/Malaffi Medication & Facility Codes

  3. Medication master - MD-PIS-001: RxNorm Drug Database (subset relevant to facility)

  4. Formulary & clinical rules - MD-PIS-002: Facility Formulary
    - MD-PIS-003: Drug Interaction Database (mapped to RxNorm)
    - MD-PIS-004: Dose Range Check Rules
    - MD-PIS-014: Antimicrobial Stewardship Rules

  5. IV & compounding - MD-PIS-009: IV Solution Base Products
    - MD-PIS-015: Compounding Recipes

  6. Workflow configuration - MD-PIS-010: Medication Frequencies
    - MD-PIS-018: Medication Administration Status Codes

  7. Inventory & logistics - MD-PIS-011: Pharmacy Locations
    - MD-PIS-017: Inventory Transaction Types
    - MD-PIS-016: Controlled Substance Transaction Reasons
    - MD-PIS-012: Vendor / Supplier Directory

  8. Go-live readiness - Validate all mappings (RxNorm ↔ formulary ↔ MOH schedules ↔ NABIDH/Malaffi codes)
    - Run test scenarios for inpatient, outpatient, IV, controlled substances, and stewardship workflows.

Master Data Specifications

Below, each data set includes purpose, schema, sample data, governance, and validation rules. All tables should support bilingual display where user-facing (English/Arabic) and be UTF-8 encoded to comply with UAE bilingual requirements.

MD-PIS-001: RxNorm Drug Database

Purpose

Canonical medication vocabulary used across PIS for:

  • Linking CPOE orders to dispensing and administration
  • Driving drug interaction checks, dose range rules, and stewardship
  • Mapping to UAE MOH controlled schedules and HIE (NABIDH/Malaffi) codes

Typically stored in a central terminology schema; PIS references it via drug_code_rxnorm.

Logical Schema (PIS-facing subset)

Field Type Required Description
rxnorm_code VARCHAR(20) YES RxNorm Concept Unique Identifier (RxCUI); primary key in PIS context
generic_name_en VARCHAR(255) YES Generic drug name (English)
generic_name_ar VARCHAR(255) NO Generic drug name (Arabic)
brand_name_en VARCHAR(255) NO Common brand name (English)
brand_name_ar VARCHAR(255) NO Common brand name (Arabic)
dosage_form_en VARCHAR(100) YES Dosage form (tablet, capsule, injection, etc.)
dosage_form_ar VARCHAR(100) NO Dosage form (Arabic)
strength VARCHAR(100) YES Human-readable strength (e.g., 500 mg)
atc_code VARCHAR(10) NO ATC therapeutic class code (link to MD-PIS-008)
is_active BOOLEAN YES Whether the RxNorm concept is active/usable
effective_date DATE YES Date this record became effective in the facility
retired_date DATE NO Date this record was retired (if applicable)

Sample Data

rxnorm_code generic_name_en generic_name_ar brand_name_en dosage_form_en strength atc_code is_active
617310 Atorvastatin أتورفاستاتين Lipitor Tablet 20 mg C10AA05 TRUE
314076 Lisinopril ليسينوبريل Zestril Tablet 10 mg C09AA03 TRUE
261106 Tramadol ترامادول Tramal Capsule 50 mg N02AX02 TRUE
3322 Diazepam ديازيبام Valium Tablet 5 mg N05BA01 TRUE
7646 Omeprazole أوميبرازول Losec Capsule 20 mg A02BC01 TRUE
283742 Esomeprazole إيسوميبرازول Nexium Tablet 40 mg A02BC05 TRUE
198211 Amoxicillin أموكسيسيلين Amoxil Capsule 500 mg J01CA04 TRUE
723 Insulin Regular إنسولين عادي Actrapid Injection 100 IU/mL A10AB01 TRUE

Governance

  • Owner: Pharmacy Informatics
  • Approval: Chief Pharmacist for inclusion/exclusion of concepts
  • Update Frequency: Monthly, aligned with RxNorm release schedule
  • Change Process: 1. Download vendor/NLM RxNorm update (subset for UAE formulary).
    2. Load into staging; run automated diff against production.
    3. Present changes impacting formulary (new/retired codes) to P&T Committee if clinically relevant.
    4. Apply approved changes to production; maintain mapping logs for audit.
    5. Notify CPOE, LIS, and billing teams of any code changes affecting interfaces.

Validation Rules

  • rxnorm_code must be unique and non-null.
  • generic_name_en required; generic_name_ar recommended for patient-facing displays.
  • is_active = TRUE implies retired_date IS NULL.
  • atc_code, if populated, must exist in MD-PIS-008.
  • No orphaned references: any RxNorm used in formulary, orders, or inventory must exist and be active.

MD-PIS-002: Facility Formulary

Purpose

Defines which medications are orderable/dispensable at each facility, including formulary status, restrictions, and controlled substance flags. Drives:

  • Order verification (WF-PIS-001)
  • Therapeutic interchange suggestions
  • Insurance/prior authorization checks

Schema

This master data maps into operational tables formulary and formulary_items.

Table: formulary (header)

Field Type Required Description
formulary_id BIGINT YES Primary key
formulary_name_en VARCHAR(200) YES Formulary name (English)
formulary_name_ar VARCHAR(200) NO Formulary name (Arabic)
facility_id BIGINT YES FK → facilities.facility_id
effective_date DATE YES Date formulary becomes active
status VARCHAR(20) YES active, inactive, draft
approved_by_user_id BIGINT YES FK → users.user_id (P&T chair or Chief Pharmacist)
approval_datetime TIMESTAMP YES Approval timestamp

Table: formulary_items

Field Type Required Description
item_id BIGINT YES Primary key
formulary_id BIGINT YES FK → formulary.formulary_id
rxnorm_code VARCHAR(20) YES FK → MD-PIS-001 (rxnorm_code)
drug_name_en VARCHAR(255) YES Display name (English)
drug_name_ar VARCHAR(255) NO Display name (Arabic)
generic_name_en VARCHAR(255) YES Generic name (English)
formulary_status VARCHAR(20) YES on_formulary, non_formulary, restricted, do_not_use
restriction_type VARCHAR(100) NO e.g., ID-approval, ICU-only, step-therapy
therapeutic_class_code VARCHAR(10) NO FK → MD-PIS-008 (ATC code)
therapeutic_alternative_rxnorm VARCHAR(20) NO FK → rxnorm_code (preferred alternative)
requires_prior_auth BOOLEAN YES Insurance prior authorization required
is_controlled BOOLEAN YES Controlled substance flag
controlled_schedule_code VARCHAR(20) NO FK → MD-PIS-005
max_daily_dose DECIMAL(12,4) NO Maximum recommended daily dose (for CDS)
max_daily_dose_unit VARCHAR(20) NO FK → MD-PIS-007
is_high_alert BOOLEAN YES High-alert medication (ISMP-style)
is_look_alike_sound_alike BOOLEAN YES LASA flag
active BOOLEAN YES Item active in formulary

Sample Data

formulary

formulary_id formulary_name_en facility_id effective_date status approved_by_user_id
1 Dubai General Hospital Adult Formulary 101 2026-01-01 active 5001
2 Dubai General Hospital Paediatric Formulary 101 2026-01-01 active 5001

formulary_items

item_id formulary_id rxnorm_code drug_name_en formulary_status restriction_type therapeutic_class_code requires_prior_auth is_controlled controlled_schedule_code is_high_alert
1001 1 617310 Atorvastatin 20 mg tablet on_formulary NULL C10AA05 FALSE FALSE NULL FALSE
1002 1 261106 Tramadol 50 mg capsule on_formulary max 3 days N02AX02 FALSE TRUE S2 TRUE
1003 1 3322 Diazepam 5 mg tablet restricted psychiatry-only N05BA01 TRUE TRUE S6 TRUE
1004 1 7646 Omeprazole 20 mg capsule on_formulary step-therapy (after H2 blocker) A02BC01 FALSE FALSE NULL FALSE
1005 1 283742 Esomeprazole 40 mg tablet non_formulary use omeprazole unless justified A02BC05 TRUE FALSE NULL FALSE
1006 1 198211 Amoxicillin 500 mg capsule on_formulary NULL J01CA04 FALSE FALSE NULL FALSE

Governance

  • Owner: Chief Pharmacist
  • Approval: Pharmacy & Therapeutics (P&T) Committee
  • Update Frequency: Quarterly P&T meetings; ad-hoc for safety alerts or MOH directives
  • Change Process: 1. Clinician or department submits formulary change request.
    2. Clinical and financial impact assessment by pharmacy.
    3. P&T Committee review and decision.
    4. Pharmacy Informatics updates formulary and formulary_items in test, then production.
    5. Notify CPOE, billing, and inventory teams; update training materials.

Validation Rules

  • rxnorm_code must exist and be active in MD-PIS-001.
  • If is_controlled = TRUE, controlled_schedule_code must be populated and valid (MD-PIS-005).
  • therapeutic_class_code, if populated, must exist in MD-PIS-008.
  • formulary_status limited to controlled list; do_not_use items must not be orderable.
  • Only one active formulary record per facility and formulary type (adult/paediatric) at a time.

MD-PIS-003: Drug Interaction Database

Purpose

Knowledge base of drug–drug interactions used by CDS in order verification (WF-PIS-001, WF-PIS-003). Typically sourced from a commercial vendor; stored in drug_interactions table.

Schema: drug_interactions

Field Type Required Description
interaction_id BIGINT YES Primary key
drug_a_rxnorm VARCHAR(20) YES FK → MD-PIS-001
drug_b_rxnorm VARCHAR(20) YES FK → MD-PIS-001
severity VARCHAR(20) YES contraindicated, major, moderate, minor
description_en VARCHAR(1000) YES Interaction description (English)
description_ar VARCHAR(1000) NO Interaction description (Arabic)
clinical_significance_en VARCHAR(1000) YES Clinical impact summary
recommendation_en VARCHAR(1000) YES Recommended action (avoid, monitor, adjust dose)
evidence_level VARCHAR(10) YES Vendor-specific evidence grade (e.g., A, B, C)
source VARCHAR(100) YES Vendor name/version
last_reviewed_date DATE YES Last clinical review date
active BOOLEAN YES Whether rule is active

Sample Data

interaction_id drug_a_rxnorm drug_b_rxnorm severity description_en recommendation_en evidence_level source
1 617310 3322 major Atorvastatin + Diazepam may increase CNS depression. Avoid combination or monitor for excessive sedation; consider lower doses. B FDB 2026.01
2 261106 3322 major Tramadol + Diazepam increases risk of respiratory depression. Avoid co-prescribing where possible; if necessary, use lowest effective doses and monitor closely. A FDB 2026.01
3 617310 198211 minor Atorvastatin + Amoxicillin: no significant interaction expected. No action needed. C FDB 2026.01
4 7646 198211 moderate Omeprazole may reduce absorption of some antibiotics. Monitor clinical response; adjust antibiotic if inadequate response. B FDB 2026.01
5 723 617310 major Insulin + Atorvastatin may increase risk of hypoglycaemia in some patients. Monitor blood glucose closely when starting or adjusting statin therapy. C FDB 2026.01

Governance

  • Owner: Pharmacy Informatics
  • Approval: Medication Safety Committee
  • Update Frequency: Monthly vendor updates; quarterly alert fatigue review
  • Change Process: 1. Import vendor update into staging.
    2. Run analytics on override rates; propose suppression/tuning of low-value alerts.
    3. Medication Safety Committee approves tuning changes.
    4. Deploy to production; document changes for audit.

Validation Rules

  • drug_a_rxnorm and drug_b_rxnorm must exist in MD-PIS-001.
  • Enforce canonical ordering (e.g., lowest RxNorm code as drug_a) to avoid duplicates.
  • severity must be from controlled list; active = FALSE used for suppressed rules (do not delete).
  • No self-interactions (drug_a_rxnorm != drug_b_rxnorm).

MD-PIS-004: Dose Range Check Rules

Purpose

Defines age/weight/organ-function–based dosing limits used in CDS to prevent overdosing or underdosing during verification.

Schema: dose_range_rules

Field Type Required Description
rule_id BIGINT YES Primary key
rxnorm_code VARCHAR(20) YES FK → MD-PIS-001
age_min_years DECIMAL(4,1) NO Minimum age (years) for rule applicability
age_max_years DECIMAL(4,1) NO Maximum age (years)
weight_min_kg DECIMAL(6,2) NO Minimum weight (kg)
weight_max_kg DECIMAL(6,2) NO Maximum weight (kg)
creatinine_clearance_min DECIMAL(6,2) NO Minimum CrCl (mL/min)
creatinine_clearance_max DECIMAL(6,2) NO Maximum CrCl (mL/min)
daily_dose_min DECIMAL(12,4) YES Minimum recommended daily dose
daily_dose_max DECIMAL(12,4) YES Maximum recommended daily dose
dose_unit VARCHAR(20) YES FK → MD-PIS-007 (e.g., mg, mg/kg)
route_code VARCHAR(20) NO FK → MD-PIS-006
indication_en VARCHAR(255) NO Indication (if rule is indication-specific)
severity VARCHAR(20) YES hard_stop, soft_alert
last_reviewed_date DATE YES Last clinical review date
active BOOLEAN YES Rule active flag

Sample Data

rule_id rxnorm_code age_min_years age_max_years daily_dose_max dose_unit route_code severity
10001 617310 18.0 120.0 80.0000 mg PO soft_alert
10002 261106 18.0 65.0 400.0000 mg PO hard_stop
10003 261106 65.0 120.0 300.0000 mg PO hard_stop
10004 198211 0.5 12.0 90.0000 mg/kg PO soft_alert
10005 723 0.0 120.0 1.0000 IU/kg IV hard_stop

Governance

  • Owner: Clinical Pharmacy Lead
  • Approval: P&T Committee
  • Update Frequency: Annual comprehensive review; ad-hoc for major guideline changes or safety alerts.
  • Change Process: 1. Review new guidelines (e.g., international societies, manufacturer updates).
    2. Propose rule changes; test in non-production with sample orders.
    3. P&T approval; deploy to production.
    4. Monitor alert overrides and adjust severity as needed.

Validation Rules

  • rxnorm_code must exist in MD-PIS-001 and be on formulary.
  • daily_dose_min <= daily_dose_max.
  • Age/weight/CrCl ranges must not overlap conflicting rules for same drug/route/indication without explicit design.
  • dose_unit must exist in MD-PIS-007.

MD-PIS-005: UAE MOH Controlled Substance Schedules

Purpose

Represents UAE MOH controlled substance schedules (narcotic and psychotropic) used for:

  • Controlled substance flagging in formulary
  • Verification of prescriber authorization (WF-PIS-006)
  • MOH reporting (INT-PIS-007)

Schema: moh_controlled_schedules

Field Type Required Description
schedule_code VARCHAR(20) YES Primary key; e.g., S2, S6
schedule_name_en VARCHAR(200) YES Schedule name (English)
schedule_name_ar VARCHAR(200) NO Schedule name (Arabic)
category_en VARCHAR(100) YES Narcotic, Psychotropic, etc.
legal_reference VARCHAR(255) YES e.g., Federal Law No. 14 of 1995
prescribing_restrictions_en VARCHAR(1000) YES Summary of prescriber restrictions
dispensing_restrictions_en VARCHAR(1000) YES Summary of dispensing requirements
active BOOLEAN YES Active flag
last_updated_date DATE YES Last sync with MOH guidance

Sample Data

schedule_code schedule_name_en category_en legal_reference prescribing_restrictions_en
S1 Schedule 1 Narcotic Narcotic Federal Law No. 14 of 1995 Only MOH-authorised specialists; special prescription forms; limited duration.
S2 Schedule 2 Narcotic Narcotic Federal Law No. 14 of 1995 Consultants and specialists with MOH/DOH/DHA license; max 30 days supply.
S4 Schedule 4 Psychotropic Psychotropic Federal Law No. 14 of 1995 Licensed physicians; repeat prescriptions limited as per MOH circulars.
S6 Schedule 6 Psychotropic Psychotropic Federal Law No. 14 of 1995 Restricted to psychiatrists/neurologists; additional documentation required.
S7 Schedule 7 Controlled Controlled Federal Law No. 14 of 1995 Special reporting to MOH; storage in controlled vault.

Governance

  • Owner: Chief Pharmacist / Compliance Officer
  • Approval: UAE MOH (external); internal Compliance Committee for implementation details.
  • Update Frequency: As legislated (MOH circulars, decrees).
  • Change Process: 1. Compliance Officer monitors MOH circulars and federal law updates.
    2. Update moh_controlled_schedules and map affected formulary items.
    3. Communicate changes to prescribers and pharmacists; update policies and training.
    4. Validate MOH reporting extracts.

Validation Rules

  • schedule_code unique and non-null.
  • Any formulary item with is_controlled = TRUE must reference a valid schedule_code.
  • Cannot deactivate a schedule if any active formulary item still references it (enforce via checks or workflow).

MD-PIS-006: Administration Routes

Purpose

Standardised medication administration routes used in orders, dispensing, and eMAR. Based on SNOMED CT / HL7 route codes.

Schema: administration_routes

Field Type Required Description
route_code VARCHAR(20) YES Primary key (e.g., PO, IV)
display_name_en VARCHAR(200) YES English display name
display_name_ar VARCHAR(200) NO Arabic display name
snomed_code VARCHAR(50) YES SNOMED CT route concept ID
abbreviation VARCHAR(20) NO Common abbreviation
active BOOLEAN YES Active flag
sort_order INT NO For UI ordering

Sample Data

route_code display_name_en display_name_ar snomed_code abbreviation active
PO Oral فموي 26643006 PO TRUE
IV Intravenous وريدي 47625008 IV TRUE
IM Intramuscular عضلي 78421000 IM TRUE
SC Subcutaneous تحت الجلد 34206005 SC TRUE
SL Sublingual تحت اللسان 37839007 SL TRUE
TOP Topical موضعي 6064005 TOP TRUE
INH Inhalation استنشاق 18679011000001101 INH TRUE
PR Rectal شرجي 37161004 PR TRUE

Governance

  • Owner: Pharmacy Informatics
  • Approval: Clinical Informatics Lead
  • Update Frequency: Rare; annual review or when SNOMED subset changes.
  • Change Process: 1. Propose additions/changes based on new therapies.
    2. Validate against SNOMED CT.
    3. Update in test and validate with CPOE/eMAR teams.
    4. Deploy to production.

Validation Rules

  • route_code unique; cannot change once in use (use new code if needed).
  • snomed_code must be valid SNOMED CT concept.
  • Only active = TRUE routes should be selectable in UI.

MD-PIS-007: Dose Units

Purpose

Standard units of measure for dosing and inventory, based on UCUM. Used in orders, dose range rules, and inventory.

Schema: dose_units

Field Type Required Description
unit_code VARCHAR(20) YES Primary key (UCUM code, e.g., mg, mg/kg)
display_name_en VARCHAR(200) YES English display name
display_name_ar VARCHAR(200) NO Arabic display name
ucum_code VARCHAR(20) YES UCUM code (may equal unit_code)
unit_type VARCHAR(50) YES mass, volume, unit, mass_per_kg, etc.
active BOOLEAN YES Active flag

Sample Data

unit_code display_name_en display_name_ar ucum_code unit_type active
mg Milligram مليغرام mg mass TRUE
g Gram غرام g mass TRUE
mcg Microgram ميكروغرام ug mass TRUE
mL Millilitre مليلتر mL volume TRUE
IU International Unit وحدة دولية IU unit TRUE
mg/kg Milligram per kilogram مليغرام/كغ mg/kg mass_per_kg TRUE
mcg/kg/min Microgram per kilogram per minute ميكروغرام/كغ/دقيقة ug/kg/min mass_per_kg TRUE

Governance

  • Owner: Pharmacy Informatics
  • Approval: Clinical Informatics Lead
  • Update Frequency: Rare; annual review.
  • Change Process: Controlled addition of new units; avoid removing units in use.

Validation Rules

  • unit_code unique; must not be changed once referenced.
  • ucum_code must be valid UCUM.
  • Only active = TRUE units available for new orders.

MD-PIS-008: Therapeutic Drug Classes

Purpose

ATC-based classification of medications used for reporting, formulary management, and stewardship.

Schema: therapeutic_classes

Field Type Required Description
atc_code VARCHAR(10) YES Primary key (e.g., C10AA05)
class_name_en VARCHAR(255) YES English class name
class_name_ar VARCHAR(255) NO Arabic class name
level INT YES ATC level (1–5)
parent_atc_code VARCHAR(10) NO FK → therapeutic_classes.atc_code (hierarchy)
active BOOLEAN YES Active flag

Sample Data

atc_code class_name_en level parent_atc_code
C Cardiovascular system 1 NULL
C10 Lipid modifying agents 2 C
C10AA HMG CoA reductase inhibitors 3 C10
C10AA05 Atorvastatin 5 C10AA
J01 Antibacterials for systemic use 2 J
J01CA Penicillins with extended spectrum 3 J01
J01CA04 Amoxicillin 5 J01CA

Governance

  • Owner: Clinical Pharmacy
  • Approval: P&T Committee
  • Update Frequency: Annual; align with WHO ATC updates.
  • Change Process: Import updated ATC list; map to RxNorm; review any reclassifications.

Validation Rules

  • atc_code unique; hierarchical integrity (parent_atc_code must exist if not null).
  • Any therapeutic_class_code in formulary must exist and be active.

MD-PIS-009: IV Solution Base Products

Purpose

Defines standard IV base solutions used in compounding (WF-PIS-005) and IV admixture orders.

Schema: iv_base_products

Field Type Required Description
base_id BIGINT YES Primary key
base_code VARCHAR(50) YES Short code (e.g., NS_1000ML)
display_name_en VARCHAR(200) YES English name (e.g., 0.9% Sodium Chloride 1000 mL)
display_name_ar VARCHAR(200) NO Arabic name
volume_ml INT YES Container volume in mL
rxnorm_code VARCHAR(20) NO FK → MD-PIS-001 (if represented as RxNorm)
sterile BOOLEAN YES Sterile product flag
active BOOLEAN YES Active flag

Sample Data

base_id base_code display_name_en volume_ml sterile active
1 NS_1000ML 0.9% Sodium Chloride 1000 mL 1000 TRUE TRUE
2 NS_500ML 0.9% Sodium Chloride 500 mL 500 TRUE TRUE
3 D5W_500ML 5% Dextrose in Water 500 mL 500 TRUE TRUE
4 RL_500ML Ringer’s Lactate 500 mL 500 TRUE TRUE
5 NS_100ML 0.9% Sodium Chloride 100 mL minibag 100 TRUE TRUE

Governance

  • Owner: IV Pharmacy Supervisor
  • Approval: Chief Pharmacist
  • Update Frequency: With formulary or supplier changes.
  • Change Process: Add/remove base products as contracts or clinical practice changes; validate compounding recipes.

Validation Rules

  • base_code unique.
  • volume_ml > 0.
  • If rxnorm_code populated, must exist in MD-PIS-001.

MD-PIS-010: Medication Frequencies

Purpose

Standard frequency codes used in orders and eMAR scheduling.

Schema: medication_frequencies

Field Type Required Description
frequency_code VARCHAR(20) YES Primary key (e.g., QD, BID)
display_name_en VARCHAR(200) YES English display name
display_name_ar VARCHAR(200) NO Arabic display name
times_per_day DECIMAL(6,2) NO Approximate administrations per day
interval_hours DECIMAL(6,2) NO Interval between doses in hours
is_prn BOOLEAN YES As-needed flag
requires_indication BOOLEAN YES PRN indication required
active BOOLEAN YES Active flag

Sample Data

frequency_code display_name_en display_name_ar times_per_day interval_hours is_prn requires_indication
QD Once daily مرة واحدة يوميًا 1.00 24.00 FALSE FALSE
BID Twice daily مرتان يوميًا 2.00 12.00 FALSE FALSE
TID Three times daily ثلاث مرات يوميًا 3.00 8.00 FALSE FALSE
Q6H Every 6 hours كل 6 ساعات 4.00 6.00 FALSE FALSE
Q8H Every 8 hours كل 8 ساعات 3.00 8.00 FALSE FALSE
QHS At bedtime عند النوم 1.00 24.00 FALSE FALSE
PRN As needed عند اللزوم NULL NULL TRUE TRUE
STAT Immediately حالاً 1.00 NULL FALSE FALSE

Governance

  • Owner: Pharmacy Informatics
  • Approval: Medication Safety Committee
  • Update Frequency: Rare; annual review.
  • Change Process: Add new frequencies only after clinical review; avoid changing semantics of existing codes.

Validation Rules

  • frequency_code unique.
  • If is_prn = TRUE, requires_indication = TRUE.
  • Non-PRN frequencies should have times_per_day or interval_hours populated.

MD-PIS-011: Pharmacy Locations

Purpose

Defines physical and logical pharmacy locations (central pharmacy, satellites, ADCs) used in inventory and dispensing workflows.

Schema: pharmacy_locations

Field Type Required Description
location_id BIGINT YES Primary key
location_code VARCHAR(50) YES Short code (e.g., PHARM_MAIN, ADC_ICU1)
display_name_en VARCHAR(200) YES English name
display_name_ar VARCHAR(200) NO Arabic name
facility_id BIGINT YES FK → facilities.facility_id
department_id BIGINT NO FK → departments.department_id
location_type VARCHAR(50) YES central_pharmacy, satellite, adc, ward_store
is_controlled_vault BOOLEAN YES Controlled substance vault flag
active BOOLEAN YES Active flag

Sample Data

location_id location_code display_name_en facility_id location_type is_controlled_vault
1 PHARM_MAIN Main Pharmacy – Dubai General Hospital 101 central_pharmacy TRUE
2 PHARM_OPD Outpatient Pharmacy – Dubai General Hospital 101 satellite FALSE
3 ADC_ICU1 ICU ADC Cabinet 1 101 adc TRUE
4 ADC_ER Emergency Department ADC 101 adc TRUE
5 WARD_STORE_3A Ward 3A Medication Store 101 ward_store FALSE

Governance

  • Owner: Pharmacy Director
  • Approval: Hospital Operations Committee
  • Update Frequency: On facility layout changes or new ADC installations.
  • Change Process: Coordinate with Facilities, Nursing, and IT; update inventory mappings and controlled substance workflows.

Validation Rules

  • location_code unique.
  • If is_controlled_vault = TRUE, location must meet MOH storage requirements (enforced via policy, not system).
  • Only active = TRUE locations used for new inventory transactions.

MD-PIS-012: Vendor / Supplier Directory

Purpose

List of pharmaceutical vendors/suppliers used for purchasing and inventory reconciliation.

Schema: pharmacy_vendors

Field Type Required Description
vendor_id BIGINT YES Primary key
vendor_code VARCHAR(50) YES Short code
vendor_name_en VARCHAR(255) YES English name
vendor_name_ar VARCHAR(255) NO Arabic name
contact_phone VARCHAR(30) NO Contact number (e.g., +971 4 XXX XXXX)
contact_email VARCHAR(255) NO Contact email
address_en VARCHAR(500) NO Address (English)
address_ar VARCHAR(500) NO Address (Arabic)
active BOOLEAN YES Active flag

Sample Data

vendor_id vendor_code vendor_name_en contact_phone address_en active
1 GULF_PHARMA Gulf Pharmaceutical Industries +971 4 123 4567 PO Box 12345, Dubai, UAE TRUE
2 JULPHAR Julphar Gulf Pharmaceutical +971 7 222 3333 PO Box 997, Ras Al Khaimah, UAE TRUE
3 MNC_MED MNC Medical Supplies LLC +971 2 555 6677 PO Box 54321, Abu Dhabi, UAE TRUE
4 LOCAL_DIST Dubai Local Distributor +971 4 777 8899 PO Box 67890, Dubai, UAE TRUE

Governance

  • Owner: Pharmacy Inventory Manager
  • Approval: Procurement Committee
  • Update Frequency: On contract award/termination.
  • Change Process: Align with procurement system; ensure vendor codes match finance/ERP.

Validation Rules

  • vendor_code unique.
  • Only active = TRUE vendors selectable for new purchase orders.

MD-PIS-013: NABIDH/Malaffi Medication & Facility Codes

Purpose

Mappings required to submit dispensing events and medication lists to Dubai’s NABIDH and Abu Dhabi’s Malaffi HIEs (INT-PIS-005/006).

Schema: hie_medication_mappings

Field Type Required Description
mapping_id BIGINT YES Primary key
rxnorm_code VARCHAR(20) YES FK → MD-PIS-001
nabidh_med_code VARCHAR(50) NO DHA NABIDH medication code
malaffi_med_code VARCHAR(50) NO DOH Malaffi medication code
active BOOLEAN YES Active flag

Sample Data

mapping_id rxnorm_code nabidh_med_code malaffi_med_code active
1 617310 DHA-MED-000123 DOH-MED-ATV20 TRUE
2 261106 DHA-MED-000456 DOH-MED-TRM50 TRUE
3 198211 DHA-MED-000789 DOH-MED-AMX500 TRUE

Governance

  • Owner: IT / Integration Team
  • Approval: IT Director + Pharmacy Informatics
  • Update Frequency: On HIE specification or formulary changes.
  • Change Process: Coordinate with DHA/DOH mapping tables; test in pre-production HIE environment.

Validation Rules

  • rxnorm_code must exist and be on formulary.
  • At least one of nabidh_med_code or malaffi_med_code must be populated for facilities in Dubai/Abu Dhabi respectively.

MD-PIS-014: Antimicrobial Stewardship Rules

Purpose

Rules used to identify antimicrobial orders requiring stewardship review (WF-PIS-008) and to drive restrictions.

Schema: stewardship_rules

Field Type Required Description
rule_id BIGINT YES Primary key
rxnorm_code VARCHAR(20) YES FK → MD-PIS-001
rule_type VARCHAR(50) YES restricted_agent, duration_limit, iv_to_po, broad_spectrum
indication_en VARCHAR(255) NO Indication (if specific)
max_duration_days INT NO Maximum recommended duration
requires_id_approval BOOLEAN YES Infectious Disease approval required
auto_review_after_hours INT NO Hours after which order is queued for review
active BOOLEAN YES Active flag

Sample Data

rule_id rxnorm_code rule_type max_duration_days requires_id_approval auto_review_after_hours
2001 123456 restricted_agent 14 TRUE 24
2002 198211 duration_limit 7 FALSE 72
2003 654321 iv_to_po NULL FALSE 48

(RxNorm codes 123456, 654321 represent broad-spectrum agents in sample.)

Governance

  • Owner: Antimicrobial Stewardship Pharmacist
  • Approval: Antimicrobial Stewardship Committee
  • Update Frequency: Quarterly; ad-hoc for resistance trends.
  • Change Process: Based on antibiogram and guidelines; update rules and communicate to prescribers.

Validation Rules

  • rxnorm_code must exist and be on formulary.
  • If rule_type = 'duration_limit', max_duration_days required.
  • If requires_id_approval = TRUE, workflow must enforce approval before dispensing.

MD-PIS-015: Compounding Recipes (IV & Non-Sterile)

Purpose

Standardised recipes for compounded preparations used in IV admixture and non-sterile compounding.

Schema: compounding_recipes

Field Type Required Description
recipe_id BIGINT YES Primary key
recipe_code VARCHAR(50) YES Short code
display_name_en VARCHAR(255) YES English name
display_name_ar VARCHAR(255) NO Arabic name
base_id BIGINT NO FK → iv_base_products.base_id (for IV)
final_volume_ml INT NO Final volume
bud_hours INT YES Beyond-use date in hours
route_code VARCHAR(20) YES FK → MD-PIS-006
active BOOLEAN YES Active flag

Schema: compounding_recipe_ingredients

Field Type Required Description
ingredient_id BIGINT YES Primary key
recipe_id BIGINT YES FK → compounding_recipes.recipe_id
rxnorm_code VARCHAR(20) YES FK → MD-PIS-001
amount DECIMAL(12,4) YES Amount per final volume
dose_unit VARCHAR(20) YES FK → MD-PIS-007

Sample Data

compounding_recipes

recipe_id recipe_code display_name_en base_id final_volume_ml bud_hours route_code
3001 VANCO_IV Vancomycin IV 1 g in 250 mL NS 5 250 24 IV
3002 TPN_ADULT Adult TPN Standard 1 2000 24 IV

compounding_recipe_ingredients

ingredient_id recipe_id rxnorm_code amount dose_unit
4001 3001 789012 1000.0000 mg
4002 3001 617310 0.0000 mg
4003 3002 345678 50.0000 g

Governance

  • Owner: IV Pharmacist
  • Approval: Pharmacy Supervisor + Medication Safety Committee (for high-risk preps)
  • Update Frequency: Annual; ad-hoc for new therapies.
  • Change Process: Validate calculations, stability, and BUD; test in compounding workflow.

Validation Rules

  • recipe_code unique.
  • At least one ingredient per recipe.
  • bud_hours > 0.
  • Ingredients’ rxnorm_code must exist and be on formulary.

MD-PIS-016: Controlled Substance Transaction Reasons

Purpose

Standardised reasons for controlled substance transactions in controlled_substance_log (WF-PIS-006).

Schema: cs_transaction_reasons

Field Type Required Description
reason_code VARCHAR(20) YES Primary key
display_name_en VARCHAR(200) YES English description
display_name_ar VARCHAR(200) NO Arabic description
transaction_type VARCHAR(20) YES dispense, return, waste, adjustment, transfer
active BOOLEAN YES Active flag

Sample Data

reason_code display_name_en transaction_type
DISP_PATIENT Dispensed to patient dispense
RETURN_UNUSED Returned unused dose return
WASTE_PARTIAL Partial dose wasted waste
WASTE_EXPIRED Expired medication destroyed waste
ADJ_COUNT_ERROR Inventory count correction adjustment

Governance

  • Owner: Pharmacy Supervisor
  • Approval: Compliance Officer
  • Update Frequency: Rare; when policy changes.

Validation Rules

  • reason_code unique.
  • Only active = TRUE reasons selectable in UI.

MD-PIS-017: Inventory Transaction Types

Purpose

Standard transaction types for inventory_transactions (receipts, issues, adjustments).

Schema: inventory_transaction_types

Field Type Required Description
type_code VARCHAR(20) YES Primary key
display_name_en VARCHAR(200) YES English name
display_name_ar VARCHAR(200) NO Arabic name
direction VARCHAR(10) YES in, out, neutral
affects_cost BOOLEAN YES Whether transaction affects inventory valuation
active BOOLEAN YES Active flag

Sample Data

type_code display_name_en direction affects_cost
RECEIPT Purchase receipt in TRUE
ISSUE_PATIENT Issue to patient out TRUE
ISSUE_WARD Issue to ward stock out TRUE
ADJ_GAIN Inventory gain adjustment in TRUE
ADJ_LOSS Inventory loss adjustment out TRUE
TRANSFER Transfer between locations neutral FALSE

Governance

  • Owner: Pharmacy Inventory Manager
  • Approval: Pharmacy Director
  • Update Frequency: Rare.

Validation Rules

  • type_code unique.
  • direction must be one of in, out, neutral.

MD-PIS-018: Medication Administration Status Codes

Purpose

Defines allowed statuses for eMAR entries (WF-PIS-004).

Schema: med_admin_status_codes

Field Type Required Description
status_code VARCHAR(20) YES Primary key (e.g., GIVEN, HELD)
display_name_en VARCHAR(200) YES English label
display_name_ar VARCHAR(200) NO Arabic label
is_final BOOLEAN YES Indicates final state (no further changes)
requires_reason BOOLEAN YES Whether a reason must be documented
active BOOLEAN YES Active flag

Sample Data

status_code display_name_en is_final requires_reason
SCHEDULED Scheduled FALSE FALSE
GIVEN Given TRUE FALSE
PARTIAL Partially given TRUE TRUE
HELD Held TRUE TRUE
REFUSED Patient refused TRUE TRUE
OMITTED Omitted TRUE TRUE

Governance

  • Owner: Nursing Informatics
  • Approval: Nursing Director + Pharmacy Director
  • Update Frequency: Rare.

Validation Rules

  • status_code unique.
  • If requires_reason = TRUE, UI must enforce reason capture.

Configuration Parameters

Key system-level configuration for PIS master data and CDS behaviour.

Parameter Type Default Description Governance
max_results_per_search Integer 50 Max search results returned for drug/formulary searches. System Admin (with Pharmacy Informatics)
default_formulary_id Integer 1 Default formulary for new orders if multiple formularies exist. Pharmacy Director
enable_non_formulary_ordering Boolean false Allow prescribers to order non-formulary medications (with justification). P&T Committee
cds_interaction_severity_threshold String moderate Minimum severity for interaction alerts (minor, moderate, major, contraindicated). Medication Safety Committee
cds_dose_check_enabled Boolean true Enable dose range checking rules. Pharmacy Director
stewardship_auto_review_hours Integer 72 Default hours after which antimicrobial orders are queued for review if no specific rule. Antimicrobial Stewardship Pharmacist
controlled_substance_dual_sign_required_schedules String S1,S2,S6 Comma-separated list of MOH schedules requiring dual-signature for dispensing/waste. Compliance Officer
emar_barcode_required Boolean true Require barcode scan before recording administration (except documented downtime). Nursing Director
inventory_negative_stock_allowed Boolean false Allow negative stock balances in inventory. Pharmacy Inventory Manager
hie_submission_enabled_dubai Boolean true Enable NABIDH medication submissions for Dubai facilities. IT Director
hie_submission_enabled_auh Boolean true Enable Malaffi medication submissions for Abu Dhabi facilities. IT Director
pdpl_data_retention_years_pharmacy Integer 25 Retention period for pharmacy records per UAE PDPL and local regulations. Compliance Officer

Data Load Procedures

1. Initial Load

a. External Terminology & Regulatory Data

  1. RxNorm (MD-PIS-001)
    - Source: NLM RxNorm (or vendor-curated subset).
    - Format: Vendor-provided CSV/relational dump.
    - Procedure:

    1. Load into staging schema.
    2. Filter to molecules/products relevant to UAE formulary and EDE registrations.
    3. Populate PIS-facing tables (rxnorm_code, names, forms, strengths).

  2. ATC (MD-PIS-008)
    - Source: WHO ATC CSV.
    - Load into therapeutic_classes; build hierarchy via parent_atc_code.

  3. MOH Controlled Schedules (MD-PIS-005)
    - Source: MOH circulars / official lists (often PDF/Excel).
    - Convert to CSV; manually validate schedule codes and descriptions.
    - Load into moh_controlled_schedules.

  4. NABIDH/Malaffi Codes (MD-PIS-013)
    - Source: DHA/DOH mapping files.
    - Load into hie_medication_mappings and facility mapping tables.

b. Facility-Specific Data

  1. Formulary (MD-PIS-002)
    - Source: Existing formulary spreadsheets + P&T decisions.
    - Map existing drug identifiers to RxNorm; resolve any unmapped items.
    - Import into formulary and formulary_items.

  2. IV Base Products (MD-PIS-009) and Compounding Recipes (MD-PIS-015)
    - Source: Current IV room worksheets and SOPs.
    - Normalize into CSV templates; load into respective tables.

  3. Pharmacy Locations (MD-PIS-011) and Vendors (MD-PIS-012)
    - Source: Facility layout and procurement systems.
    - Align codes with ERP/finance where applicable.

  4. Frequencies, Routes, Units, Status Codes (MD-PIS-006, 007, 010, 018)
    - Source: Standard lists agreed with Clinical/Nursing Informatics.
    - Load from configuration CSVs.

2. Ongoing Synchronization

  • RxNorm & ATC: Monthly/annual batch jobs to import new releases; run impact analysis on formulary and CDS.
  • Drug Interaction DB & Dose Rules: Vendor-provided monthly updates; automated import with clinical review.
  • MOH Schedules: Manual update upon MOH notification; no automated feed expected.
  • HIE Mappings: Update when DHA/DOH publish new mapping tables; coordinate with integration team.
  • Formulary: Changes driven by P&T decisions; managed via internal UI with audit trail.

3. Import/Export Formats

  • Import:
  • CSV (UTF-8) for most master data (formulary, routes, units, frequencies, vendors, locations).
  • SQL scripts for terminology bulk loads (RxNorm, ATC).
  • Export:
  • CSV/Excel for reporting and P&T review.
  • JSON via internal APIs for other modules (CPOE, EHR, Billing).

4. Validation on Import

For each import job:

  1. Structural Validation
    - Check required columns present.
    - Validate data types (numeric, dates, booleans).

  2. Referential Integrity
    - RxNorm codes must exist before loading formulary, rules, recipes.
    - ATC codes must exist before linking to formulary.
    - MOH schedule codes must exist before setting is_controlled.

  3. Business Rules
    - No duplicate codes (routes, units, frequencies, vendors, locations).
    - No overlapping dose rules for same drug/route/age range without explicit design.
    - Controlled substances must have appropriate schedule and vault locations configured.

  4. UAE Regulatory Checks
    - Ensure controlled substances mapped to correct MOH schedule.
    - Ensure PDPL requirements for data minimisation and purpose limitation are met (no unnecessary personal data in master tables).

  5. Logging & Error Handling
    - All import jobs must log: file name, timestamp, user, record counts, errors.
    - Failed records written to error file with reason; must be corrected and re-imported.

This specification provides the master data and configuration foundation for the Pharmacy Information System, ensuring alignment with UAE regulatory requirements (MOH, DOH, DHA, UAE PDPL) and supporting safe, paperless pharmacy workflows across inpatient, outpatient, IV, inventory, and stewardship processes.

← Previous Pharmacy Information System Integration Specifications Next → Pharmacy Information System KPIs & Reporting
content/clinical/pis/06-master-data.md Generated 2026-02-20 22:54