CPIA Module 2, Section 3: Mapping and Crosswalks for Drug Databases
MODULE 2: DATA STRUCTURES AND HEALTHCARE TERMINOLOGIES

Section 2.3: Mapping and Crosswalks for Drug Databases

Learn the art and science of “translation.” This section covers the critical process of mapping a hospital’s local, proprietary drug formulary to the national standard of RxNorm to enable interoperability.

SECTION 2.3

Mapping and Crosswalks for Drug Databases

The Rosetta Stone of Pharmacy Informatics.

2.3.1 The “Why”: Solving the Tower of Babel Problem

In the previous section, we established the critical need for standardized terminologies like RxNorm. We learned that RxNorm provides a universal, unambiguous language for medications. Now, we confront the messy reality: almost no hospital in the world actually uses RxNorm as its primary, internal drug database. Every hospital, for historical, operational, and financial reasons, has its own unique, proprietary drug list. This list is known by many names—the formulary master, the chargemaster (CDM), the item master—but its nature is the same: it is a private dialect, a local language understood only within the walls of that institution.

This creates the “Tower of Babel” problem in healthcare data. Hospital A’s computer system knows lisinopril by the internal ID DRUG_ID: 10452 and the name LISINOPRIL 10MG TAB (ACME). Hospital B, just across the street, knows the exact same clinical drug as ITEM_ID: 7781-A and the name LISINOPRIL, ORAL, 10MG, TABLET. When these two systems try to exchange data, they are speaking different languages. Without a translator, the information is useless. This is why a patient’s medication list often fails to transfer electronically between hospitals, and why performing research across multiple health systems is a monumental challenge.

Drug mapping is the act of creating that translator. It is the meticulous, pharmacist-led process of building a “Rosetta Stone”—known in informatics as a crosswalk—that creates a definitive link between a hospital’s internal, proprietary drug ID and the corresponding universal, standard RxNorm ID (the RxCUI). This is arguably one of the most foundational and highest-impact tasks in all of pharmacy informatics. A successful mapping project is the key that unlocks true interoperability. It enables everything from safe electronic prescribing and medication reconciliation to powerful clinical decision support and large-scale data analytics. An incorrect map, however, is a patient safety catastrophe waiting to happen. This section will teach you the art and science of building that bridge.

Retail Pharmacist Analogy: Your Internal “Drug Mapping” Brain

You perform complex drug mapping hundreds of times a day without even thinking about it. It’s a core, ingrained function of being a pharmacist. Let’s break down a common scenario to reveal the sophisticated “crosswalk” that already exists in your head.

A new electronic prescription arrives for “Zestril 10mg tablets, #30.”

  1. Initial Input (The Local Term): Your brain receives the brand name “Zestril 10mg tablet.” This is the proprietary name from the “prescriber system.”
  2. Normalization (Mapping to the Clinical Concept): You instantly and automatically normalize this. You don’t think, “I need to find a bottle that says Zestril.” You think, “The patient needs lisinopril 10mg tablets.” You have just mapped the branded drug concept (SBD in RxNorm terms) to its core clinical concept (SCD in RxNorm terms). This allows you to apply your clinical knowledge (e.g., checking for ACE inhibitor allergies).
  3. Inventory Lookup (Mapping to the Dispensable Product): You turn to the shelf. You don’t just look for “lisinopril 10mg.” Your pharmacy stocks the generic from Lupin Pharmaceuticals. You are now looking for a specific bottle with a specific National Drug Code (NDC). You have just mapped the clinical concept (SCD) to a specific, physical, billable product.
  4. Output (The Dispensed Drug): You pick that bottle, affix a label that reads “Lisinopril 10mg Tablet (generic for Zestril),” and dispense it. The claim sent to the insurance company uses the specific NDC of the Lupin product.

In that single, seamless workflow, your brain acted as a sophisticated terminology server, executing a multi-step mapping process: Zestril (Brand Name) Lisinopril 10mg Tablet (Clinical Drug) Lupin-manufactured Lisinopril 10mg Tablet (Dispensable Product/NDC). The goal of a hospital drug mapping project is to codify this exact expert logic into a permanent, reliable digital crosswalk that the entire health system can use automatically.

2.3.2 Deconstructing the Source: The Hospital Formulary Master

Before you can map anything, you must intimately understand the data you are starting with. The hospital’s internal drug database—often called the formulary master or part of the Charge Description Master (CDM)—is a complex artifact, shaped more by years of operational and billing needs than by clinical precision. It is often messy, inconsistent, and filled with legacy entries. Your first job as an informaticist is to be an archaeologist, carefully excavating and understanding this source data.

Unlike a clean, normalized terminology like RxNorm, a hospital formulary is optimized for internal hospital workflows. A single “orderable” drug in the EHR might be linked to multiple NDCs in the pharmacy purchasing system and a single billing code in the CDM. The “name” of the drug is often a long, concatenated string that contains far more than just the drug name.

Masterclass Table: Real-World Hospital Formulary Entries

Below are examples of how the same few drugs might appear in a typical hospital’s formulary file. Notice the lack of standardization.

Internal ID Drug Description (The “String”) Challenges for Mapping
PHARM_ID: 12054 LISINOPRIL 10MG TAB PO
  • Relatively clean.
  • The route “PO” is embedded in the string.
CDM_ID: 440912 TAB, LISINOPRIL, 20MG, ORAL (SUNRISE)
  • Dose form (“TAB”) is at the beginning.
  • Manufacturer name (“SUNRISE”) is included, which is irrelevant for clinical mapping.
  • Route is present.
PYXIS_ID: 8813 HEPARIN 5000U/ML VIAL 1ML IJ
  • Strength is expressed as concentration (5000U/ML).
  • Total volume (“VIAL 1ML”) is included.
  • Route “IJ” (injection) is present.
IV_ID: 7211 D5W 1000ML W/ KCL 20MEQ IVPB
  • This is a multi-ingredient IV admixture, not a single drug.
  • It contains two distinct components: Dextrose 5% in Water and Potassium Chloride.
  • This cannot be mapped to a single RxNorm ingredient. It requires complex mapping to its constituent parts.
ONC_ID: 9504 PACLITAXEL 100MG/16.7ML IV SOLN *CHEMO* *NIOSH*
  • Contains clinical and safety warnings (“*CHEMO*”, “*NIOSH*”) that are not part of the drug name.
  • Strength is expressed as total drug in total volume.

As you can see, the source data is rarely clean. The critical first step in any mapping project is data cleansing and parsing. This often involves using scripts or tools to parse these long strings into their discrete components (Drug Name, Strength, Unit, Dose Form, Route) before the actual mapping to RxNorm can even begin. This is a significant data analysis task that requires both technical skill and deep clinical knowledge.

2.3.3 The Target: Choosing the Right RxNorm Concept Level

Once you have a cleaned and parsed list of your hospital’s drugs, the next critical decision is to determine the appropriate level within the RxNorm hierarchy to map to. As we learned in the previous section, RxNorm has multiple levels of abstraction (Ingredient, Clinical Drug, Branded Drug, etc.). Choosing the wrong target level for your map can severely limit its usefulness and even introduce safety risks.

The Golden Rule of Clinical Mapping: Map to the SCD

For almost all clinical purposes—including clinical decision support, medication reconciliation, and data analytics—the primary mapping target should be the Semantic Clinical Drug (SCD). The SCD represents the brand-agnostic concept of Ingredient + Strength + Dose Form.

Why not other levels?

  • Mapping to the Ingredient (IN) is too broad. If you map “Lisinopril 10mg Tablet” to just “Lisinopril,” your system loses the critical strength and dose form information. You couldn’t perform a dose-range check.
  • Mapping to the Branded Drug (SBD) is too specific. If you map your hospital’s generic lisinopril 10mg tablet to the SBD for “Zestril 10mg Tablet,” your CDS will fail. An allergy to “ACE Inhibitors” might not fire correctly, and a therapeutic duplication check against an incoming prescription for “Prinivil” might be missed. Clinical rules need to operate on the generic, clinical concept.
  • Mapping to the NDC is a catastrophic error. An NDC is a manufacturer-specific product code, not a clinical concept. If you map your formulary to the NDC for the lisinopril made by Lupin, and next month the hospital changes its contract and starts buying lisinopril from Teva, your entire map becomes invalid. The NDCs will change, but the core clinical drug (the SCD) remains exactly the same.
Visualizing the Mapping Goal

The goal is to connect the messy, proprietary local drug to the clean, universal clinical concept. The mapping process bridges this gap, creating a reliable link that all other systems can trust.

Source: Hospital Formulary

ID: 440912

TAB, LISINOPRIL, 20MG, ORAL (SUNRISE)

(Proprietary, Inconsistent, Ambiguous)

MAPS TO

Target: RxNorm SCD

RxCUI: 311046

Lisinopril 20 MG Oral Tablet

(Standard, Universal, Unambiguous)

2.3.4 The “How”: The Art and Science of the Mapping Process

Drug database mapping is a specialized, project-based activity that combines automated tools with expert human oversight. It is never a fully automated process. The clinical nuance and potential for error are too great. The process is always led by pharmacy, with support from IT and data analysts.

The Four Phases of a Mapping Project
1

Phase 1: Data Extraction and Preparation

The project begins by getting a complete extract of the hospital’s entire drug formulary master file. The informatics pharmacist and a data analyst work together to understand all the fields. The most difficult and time-consuming part of this phase is often parsing the main drug description string. This may involve writing scripts (e.g., in Python or using SQL functions) to intelligently split the string into its component parts: name, strength, unit, and dose form. The cleaner the data is at this stage, the more successful the automated matching will be.

2

Phase 2: Automated Matching

Once the source data is prepared, it is loaded into a specialized mapping tool. These tools (from vendors like First Databank, Wolters Kluwer, or open-source projects) contain a full copy of the RxNorm database. They use sophisticated algorithms—based on natural language processing, text similarity scores (like the Jaro-Winkler distance), and parsing rules—to compare each hospital drug to the entire RxNorm dictionary and propose a “best fit” match. The output of this phase is a list of the hospital’s drugs with a suggested RxCUI match and a confidence score (e.g., “95% confident this is a match”). This automated pass can often match 70-80% of a typical formulary with high confidence.

3

Phase 3: Manual Pharmacist Validation

This is the most critical phase and it cannot be skipped. A team of pharmacists, led by an informaticist, must manually review every single match proposed by the automated tool. They review the source string, the parsed components, and the suggested RxNorm SCD match to confirm it is clinically correct. They pay special attention to the low-confidence matches and the 20-30% of drugs that the tool could not match at all. This is where clinical expertise is indispensable for resolving ambiguity.

4

Phase 4: Loading and Quality Assurance

After the pharmacists have validated and completed the entire map, the final file—the crosswalk—is generated. This file is then loaded into the hospital’s terminology server or a dedicated table in the EHR database. Before it “goes live,” a final QA step is performed. This involves taking a sample of high-risk drugs (e.g., insulin, heparin, chemotherapy) and running test scenarios in a non-production environment to ensure that CDS alerts and other functions that rely on the map are working as expected.

2.3.5 The Pharmacist’s Role: Navigating Mapping’s “Gray Zones”

The automated tools are good at matching simple concepts like “Lisinopril 10mg Tablet.” Where they fail, and where a pharmacist’s expertise is essential, is in the clinical “gray zones.” The manual validation phase is a process of clinical investigation and decision-making.

Masterclass Table: Common Mapping Challenges and Pharmacist Interventions
The Challenge Example Formulary String The Clinical Question / Ambiguity Pharmacist’s Investigative Action
Vague Strength ASPIRIN EC TAB Is this the 81mg low-dose tablet or the 325mg adult tablet? Mapping to the wrong concept could lead to major dosing errors or failed CDS alerts. The pharmacist must investigate the hospital’s standard of care. They check the automated dispensing cabinet (Pyxis/Omnicell) records, consult with clinical specialists (e.g., cardiology), and review treatment protocols to determine which strength is the default “Aspirin EC.” The map must reflect the most common clinical use.
IV Admixtures MAGNESIUM SULFATE 2GM in 50ML D5W IVPB This is not a single drug. It is a product containing Magnesium Sulfate, Dextrose, and Water. It cannot map to a single RxNorm SCD. This requires a policy decision. Most EHRs handle this by linking the orderable to its constituent parts. The mapping pharmacist ensures that the orderable is linked to the RxCUI for Magnesium Sulfate (Ingredient) AND the RxCUI for Dextrose (Ingredient). This allows ingredient-based allergy checks to function correctly.
Therapeutic Interchanges FAMOTIDINE 20MG IV SOLN The hospital has a P&T-approved protocol to automatically substitute IV famotidine with IV ranitidine when there is a shortage. The name in the formulary is famotidine, but the drug being dispensed could be ranitidine. The map for the orderable should be to the preferred/default agent (famotidine). However, the informaticist must work with the EHR build team to ensure that the therapeutic interchange logic is correctly configured at a different layer of the system, so that CDS rules (like checking for H2-blocker duplication) function regardless of which product is dispensed.
Unclear Dose Forms DILTIAZEM 120MG CAP Is this an immediate-release capsule or an extended-release capsule (e.g., Cardizem CD)? The clinical implications are enormous. The pharmacist must become a detective. They look at the purchasing history for that specific formulary ID to find the NDCs that have been dispensed under it. They look up those NDCs to confirm the exact dose form (IR, SR, CD, etc.) and then map the hospital drug to the correct, specific RxNorm SCD.

2.3.6 The Informatics Angle: Governance and Maintenance

A drug mapping crosswalk is not a static document that is created once and then forgotten. It is a living, breathing part of the hospital’s data infrastructure that requires constant care and feeding. Establishing a robust governance process for maintaining the map is just as important as creating it in the first place. This is a primary responsibility of the pharmacy informatics team.

Key Governance Processes
  • New Formulary Request Workflow: There must be a formal process for adding any new drug to the hospital’s systems. A critical, mandatory step in this workflow is “Map to RxNorm.” A new drug should not be allowed to become orderable in the EHR until a pharmacy informaticist has reviewed it and assigned it to the correct RxCUI in the master crosswalk. This prevents unmapped “orphan” drugs from entering the system and failing to trigger safety alerts.
  • Regular Terminology Updates: The NLM updates RxNorm on a regular basis (typically monthly). The pharmacy informatics team must have a process to download these updates, review changes that might affect existing maps (e.g., a concept being retired or a new one being added), and update the hospital’s crosswalk accordingly. This ensures the hospital’s terminology stays in sync with the national standard.
  • Annual Map Audit: At least once a year, the informatics team should perform an audit of the drug map. This involves re-examining high-risk or frequently used medications to ensure the maps are still accurate. It also involves searching for any drugs that have been ordered or dispensed but do not have a map (orphan drugs) and remediating them.
  • P&T Committee Integration: The lead pharmacy informaticist should be an active member of the hospital’s Pharmacy & Therapeutics (P&T) Committee. When the committee discusses adding a new drug, changing a therapeutic interchange policy, or responding to a shortage, the informaticist is there to provide crucial input on the downstream technical implications. They can answer questions like, “How will this decision affect our CDS alerts? How will we map this new combination kit? Does this change require us to update our standard order sets?” This proactive involvement ensures that clinical decisions and technical reality are always aligned.

In conclusion, drug database mapping is the essential, pharmacist-driven work that transforms a hospital’s isolated, proprietary drug list into a powerful, interoperable asset. It is a meticulous process that requires deep clinical knowledge, analytical skill, and a commitment to ongoing governance. By mastering this process, the pharmacy informaticist builds the fundamental bridge that allows raw data to be safely and effectively transformed into meaningful clinical information.