Essential and Licensed Medicine List Database

Project Overview

As part of my role, I was responsible for developing, revising, and maintaining the Essential and Licensed Medicines List (ELML) database for the Ministry of Public Health (MoPH), Afghanistan. This database serves as the centralized system for tracking, regulating, and managing medicines that can be imported, sold, and distributed across the country. It plays a crucial role in ensuring safe, effective, and high-quality pharmaceuticals, maintaining compliance with national and international pharmaceutical regulations, and streamlining the medicine registration, approval, and monitoring processes.

To align with global best practices, the database was designed in compliance with the WHO Anatomical Therapeutic Chemical (ATC) classification system. This classification ensures a standardized approach to categorizing medicines based on their active substances, therapeutic use, and pharmacological properties. Adopting the ATC system enhances data consistency, regulatory oversight, and international comparability, allowing the MoPH to benchmark its pharmaceutical system against global medicine tracking frameworks.

Additionally, the database supports decision-making for procurement, distribution, and regulatory approvals, ensuring that medicines meet efficacy, safety, and availability standards. By providing structured, well-maintained records, the system allows policymakers, healthcare professionals, and regulatory bodies to track essential medicines, prevent unauthorized imports, and facilitate efficient supply chain management in Afghanistan’s healthcare sector.

The Steps I Took

1. Database Design & Modeling

✔ Designed a relational database model to structure medicine classification, tracking, and approval workflows.
✔ Integrated the WHO ATC classification system for standardizing medicines.
✔ Developed primary and foreign key relationships to maintain data integrity.

2. Data Entry Interface Development

✔ Designed a user-friendly interface for pharmacy officers and regulatory personnel to enter, update, and retrieve medicine records.
✔ Implemented input validation rules to ensure data accuracy (e.g., dosage forms, strength, and administration routes).

3. Data Structuring & Standardization

✔ Mapped medicines to WHO ATC codes, ensuring compatibility with international classification standards.
✔ Classified medicines into categories (Essential Medicines List (EML), Licensed Medicines List (LML), Controlled Substances, Over-the-Counter Medicines, Special Program Lists).
✔ Defined standard formats for medicine names, strengths, and dosage forms to prevent inconsistencies.

4. Database Maintenance & Updates

✔ Implemented version control mechanisms to track historical changes (e.g., medicine inclusions, exclusions, and revisions).
✔ Automated reporting features for customized listings based on inclusion criteria (e.g., EML medicines, BPHS, EPHS recommendations).
✔ Optimized data retrieval and indexing to ensure fast queries and smooth user interactions.

5. Compliance with WHO ATC Listing

✔ Ensured that all medicines are classified based on WHO ATC standards to maintain consistency with international databases.
✔ Allowed for global benchmarking of medicine consumption, pricing, and regulatory tracking.
✔ Facilitated comparison of Afghanistan’s pharmaceutical system with other countries, improving transparency and accountability.
✔ Adopted WHO’s structured approach to medicine categorization, making it easier for healthcare professionals and regulatory authorities to assess medicine availability and efficacy.

Challenges Handled

Updates & Revisions

Handling Frequent Updates & Revisions – Addressed by implementing a versioning system to track medicine status changes.

Medicine Classification

Ensuring Standardized Classification – Resolved by enforcing ATC coding for all medicines and validating data at entry points.

Adoption & Training

User Adoption & Training – Conducted training sessions for regulatory teams to ensure seamless usage.

Findings & Results

✅Automate Medicine Inclusion & Exclusion Requests – Reduce manual efforts by integrating automated request workflows for new medicine approvals.
✅Faster Data Retrieval & Reports – Regulatory authorities could now generate reports in seconds instead of hours.
✅Accurate Medicine Classification – Standardized ATC coding helped in better tracking and comparison of medicines across healthcare sectors.
✅Enhanced Compliance with WHO Standards – The database now aligns with global pharmaceutical regulations, improving transparency and accountability.

Final Thoughts

This project significantly enhanced Afghanistan’s medicine registration and regulation system, ensuring compliance, accessibility, and efficient tracking of essential and licensed medicines. By leveraging a well-structured database model, automated reporting, and standardized classification, MoPH could maintain a reliable and transparent medicine approval system that benefits both public and private healthcare providers.

References

Lists:

Code: Available per request

Sample Code (SQL)

Drug Listing with ATC Hierarchy


                    
SELECT 
    LDL_List1.UniqCode, 
    LDL_List1.ATC_Code, 
    LDL_List1.ItemName, 
    LDL_List1.ItemATC, 
    LDL_List1.ItemABC, 
    Trim(ATC_1.atc_1_code) & "-  " & Trim(ATC_1_Desc) AS Level1, 
    Trim(Atc_2.ATC_2_CODE) & "-  " & Trim(ATC_2_Desc) AS Level2, 
    Trim(Atc_3.ATC_3_CODE) & "-  " & Trim(ATC_3_Desc) AS Level3, 
    Trim(Atc_4.ATC_4_CODE) & "-  " & Trim(ATC_4_Desc) AS Level4, 
    [ATC_Code - WHO Class].WHO_MLEM, 
    LkpWHOClass.TCLASSNAME, 
    Left(ItemATC,1) AS Alfa, 
    LDL_List1.EDL, 
    LDL_List1.LDL, 
    IIf(Trim(EDL) = "Yes" Or Trim(LDL) = "Yes", "Yes", "No") AS Listing, 
    LDL_List1.SpecProgr, 
    LDL_List1.SpecText, 
    LDL_List1.BPHS, 
    LDL_List1.EPHS, 
    LDL_List1.OTC, 
    LDL_List1.NarcPsy, 
    LDL_List1.Comment, 
    IIf(Trim(EDL) Like "Y*" Or Trim(EDL) Like "add*", "EDL", "") AS InEDL, 
    ATC_1.ATC_1_CODE, 
    ATC_2.ATC_2_CODE, 
    ATC_3.ATC_3_CODE, 
    ATC_4.ATC_4_CODE
FROM 
    (
        (ATC_1 
        RIGHT JOIN ATC_2 
        ON ATC_1.ATC_1_CODE = ATC_2.ATC_1_CODE) 
    RIGHT JOIN 
        (
            (ATC_3 
            RIGHT JOIN 
                (
                    (ATC_4 
                    RIGHT JOIN 
                        (
                            (
                                (LDL_List1 
                                LEFT JOIN [ATC_Code - WHO Class] 
                                ON LDL_List1.ATC_Code = [ATC_Code - WHO Class].ATC_Code) 
                            LEFT JOIN LkpWHOClass 
                                ON [ATC_Code - WHO Class].WHO_MLEM = LkpWHOClass.TCLASSCODE) 
                            LEFT JOIN ATC_5 
                                ON LDL_List1.ATC_Code = ATC_5.ATC_5_CODE
                        ) 
                    ON ATC_4.ATC_4_CODE = ATC_5.ATC_4_CODE
                    ) 
                ON ATC_3.ATC_3_CODE = ATC_4.ATC_3_CODE
                ) 
            ON ATC_2.ATC_2_CODE = ATC_3.ATC_2_CODE
        )
    );