implementation_international_drug_vocabulary
Differences
This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
implementation_international_drug_vocabulary [2019/07/24 14:09] ekorchmar |
implementation_international_drug_vocabulary [2021/06/09 10:15] adavydov |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| - | ===== International drugs vocabulary implementation process ===== | + | ===== International drug vocabulary implementation process ===== |
| - | The most straightforward way to implement a local drug [[documentation:vocabulary|Vocabulary]] is to utilize [[documentation:international_drugs|RxNorm Extension]] logic to extend standard Drug concept pool and map source drug products to concepts in RxNorm extended hierarchy. | + | The most straightforward way to standardize a local drug [[documentation:vocabulary|Vocabulary]] is to utilize [[documentation:international_drugs|RxNorm Extension]] logic to extend standard Drug concept pool and map source drug products to concepts in RxNorm and RxNorm Exterension. |
| ==== Combined target structure ==== | ==== Combined target structure ==== | ||
| - | Standard concepts in [[documentation:vocabulary:drug|Drug domain]] are placed in a single comprehensive hierarchy based on their attributes. To correctly implement a vocabulary in CDM and find or build an counterpart for each source drug concept, the following attributes must be extracted: | + | Standard concepts in [[documentation:vocabulary:drug|Drug domain]] are placed in a single comprehensive hierarchy based on their attributes. To correctly implement a vocabulary in CDM and find or build a counterpart for each source drug concept the following attributes must be extracted: |
| - **Ingredients**: active substance(-s) in pharmacological preparation. Examples: Aspirin, Trastuzumab, Ibuprofen etc.; | - **Ingredients**: active substance(-s) in pharmacological preparation. Examples: Aspirin, Trastuzumab, Ibuprofen etc.; | ||
| Line 26: | Line 26: | ||
| === Creation of input tables === | === Creation of input tables === | ||
| - | The new vocabulary should be prepared in the following tables: | + | The new vocabulary should be prepared in the following input tables. |
| + | Hereon, the DDL is given in PostgreSQL dialect. For other dialects, an appropriate data type has to be found. For example, for PostgreSQL we specifically set numbers to be NUMERIC and not FLOAT. | ||
| ==DRUG_CONCEPT_STAGE== | ==DRUG_CONCEPT_STAGE== | ||
| ^ Field ^ Required ^ Type ^ Description ^ | ^ Field ^ Required ^ Type ^ Description ^ | ||
| - | | concept_name | Yes | string(255) | An unambiguous, meaningful and descriptive name for the Concept in English language | | + | | concept_name | Yes | VARCHAR(255) | An unambiguous, meaningful and descriptive name for the Concept in English language | |
| - | | domain_id | Yes | string(20) | A foreign key to the DOMAIN table. The standard content is 'Drug', but for non-drugs it could be 'Device' or 'Observation' | | + | | domain_id | Yes | VARCHAR(20) | A foreign key to the DOMAIN table. The standard content is 'Drug', but for non-drugs it could be 'Device' or 'Observation' | |
| - | | vocabulary_id | Yes | string(20) | A foreign key to the VOCABULARY table. The value of this field should be identical for all records, indicating the new vocabulary being added. | | + | | vocabulary_id | Yes | VARCHAR(20) | A foreign key to the VOCABULARY table. The value of this field should be identical for all records, indicating the new vocabulary being added. | |
| - | | concept_class_id | Yes | string(20) | One of the above listed RxNorm Concept Classes | | + | | concept_class_id | Yes | VARCHAR(20) | One of the above listed RxNorm Concept Classes | |
| - | | concept_code | Yes | string(50) | The code in the source vocabulary. If the source vocabulary does not contain a code, e.g. for ingredients or dose forms, they will be created automatically (see below OMOP created codes) | | + | | concept_code | Yes | VARCHAR(50) | The code in the source vocabulary. If the source vocabulary does not contain a code, e.g. for ingredients or dose forms, they will be created automatically (see below OMOP created codes) | |
| - | | source_concept_class_id | No | string(20) | Concept class that is given by the source vocabulary | | + | | source_concept_class_id | No | VARCHAR(20) | Concept class that is given by the source vocabulary | |
| - | | possible_excipient | No | string(1) | A flag only relevant to ingredients, indicating whether or not they are not active ingredients and could be omitted from an ingredient list. Currently ignored. | | + | | possible_excipient | No | VARCHAR(1) | A flag only relevant to ingredients, indicating whether or not they are not active ingredients and could be omitted from an ingredient list. Currently ignored. | |
| - | | valid_start_date | No | date | Date when the Concept became valid. This may or may not coincide with the date the product went to market. Default value is 01.01.1970, unless source gives explicit date.| | + | | valid_start_date | No | DATE | Date when the Concept became valid. This may or may not coincide with the date the product went to market. Default value is 01.01.1970, unless source gives explicit date.| |
| - | | valid_end_date | No | date | Date when the Concept became invalid. Market withdrawal does not mean a Concept is invalid. Deprecated concepts have VALID_END_DATE of a day before update, unless source gives explicit date. VALID_END_DATE for all valid source concepts must be 31.12.2099 | | + | | valid_end_date | No | DATE | Date when the Concept became invalid. Market withdrawal does not mean a Concept is invalid. Deprecated concepts have VALID_END_DATE of a day before update, unless source gives explicit date. VALID_END_DATE for all valid source concepts must be 31.12.2099 | |
| - | | invalid_reason | No | string(1) | Flag indicating wether the Concept is active (today's date between valid_start and valid_end_date), or upgraded ('U') or deprecated ('D'). | | + | | invalid_reason | No | VARCHAR(1) | Flag indicating wether the Concept is active (today's date between valid_start and valid_end_date), or upgraded ('U') or deprecated ('D'). | |
| This table is expected to contain concepts having following Concept Classes: | This table is expected to contain concepts having following Concept Classes: | ||
| Line 49: | Line 50: | ||
| * Ingredient | * Ingredient | ||
| * Supplier | * Supplier | ||
| - | * Device (for source conccepts falling outside of Drug cathegory) | + | * Device (for source concepts falling outside of Drug cathegory) |
| It may contain Branded or Clinical Drug Forms or Components, but if not they will be derived (see below). Note that units should not necessarily have an entry in the DRUG_CONCEPT_STAGE table. Instead, they should be used as verbatim. If the precise Concept Class of a Drug Product is relevant, it can be preserved in source_concept_class_id field. | It may contain Branded or Clinical Drug Forms or Components, but if not they will be derived (see below). Note that units should not necessarily have an entry in the DRUG_CONCEPT_STAGE table. Instead, they should be used as verbatim. If the precise Concept Class of a Drug Product is relevant, it can be preserved in source_concept_class_id field. | ||
| Line 55: | Line 56: | ||
| Brand Names that are simple combinations of generic international name of active substance and manufacturer name (e.g. "Aspirin Bayer") should not appear as attributes for Drug Products. Manufacturer information should be stored as a concept with Supplier class. | Brand Names that are simple combinations of generic international name of active substance and manufacturer name (e.g. "Aspirin Bayer") should not appear as attributes for Drug Products. Manufacturer information should be stored as a concept with Supplier class. | ||
| - | Concepts that belong to the source vocabulary, but do not belong to Drug domain by OMOP CDM conventions, should be classified as 'Device'. Typically, these belong to different substance groups: | + | Concepts that belong to the source vocabulary, but do not belong to Drug domain by OMOP CDM conventions, should be classified as 'Device'. Typically, these belong to different groups - check the list [[documentation:vocabulary:drug|here]]. |
| - | * Radiographic contrasts and dyes (barium sulfate, radiolabeled iodides) | + | |
| - | * Nutritional supplements without stated mechanism of pharmaceutical action (e.g. herbal complexes, homeopathic preparations) | + | |
| - | * Formulas for infant feeding | + | |
| - | * Parenteral nutrition (aminoacids and/or lipid mixes) | + | |
| - | * Solution for dialysis, catheter maintenance etc. | + | |
| - | * Blood products for transfusion, blood plasma, autologous and non-autologous transplants of any kind | + | |
| - | * Cosmetics, sunscreens, non-medicated shampoos and soaps, etc. | + | |
| - | * Surgical materials like bone cements | + | |
| - | * Topical/external disinfectants | + | |
| Animal drugs can be handled as Drugs or Devices, depending on what their role in patient data can be expected to be. | Animal drugs can be handled as Drugs or Devices, depending on what their role in patient data can be expected to be. | ||
| Line 72: | Line 64: | ||
| This table should contain the mapping between source codes and Standard Concepts for Ingredients, Brand Names, Dose Forms, Suppliers and Units. All other relationships will be ignored. | This table should contain the mapping between source codes and Standard Concepts for Ingredients, Brand Names, Dose Forms, Suppliers and Units. All other relationships will be ignored. | ||
| - | ^Field^Required^Type^Description^ | + | ^ Field ^ Required ^ Type ^ Description ^ |
| - | |concept_code_1|Yes|string(255)|The source code| | + | | concept_code_1 | Yes | VARCHAR(50) | The source code | |
| - | |concept_id_2|Yes|integer|The existing target Concept| | + | | vocabulary_id_1 | Yes | VARCHAR(20) | The source vocabulary | |
| - | |precedence|No|integer|For multiple concept_code_1/concept_id_2 combination the order of precedence in which they should be considered for equivalence testing. The mapping with the highest prevalence among the drugs will be used for writing a record to the CONCEPT_RELATIONSHIP table. A missing precedence will be interpreted as precedence 1. Every precedence value should be unique per concept_code_1| | + | | concept_id_2 | Yes | INTEGER | The existing target Concept | |
| - | |conversion_factor|No|float|The factor used to convert the source code to the target Concept. This is usually defined for units| | + | | precedence | No | SMALLINT | For multiple concept_code_1/concept_id_2 combination the order of precedence in which they should be considered for equivalence testing. The mapping with the highest prevalence among the drugs will be used for writing a record to the CONCEPT_RELATIONSHIP table. A missing precedence will be interpreted as precedence 1. Every precedence value should be unique per concept_code_1 | |
| + | | conversion_factor | No | NUMERIC | The factor used to convert the source code to the target Concept. This is usually defined for units | | ||
| This table should contain all mappings from the new to existing Concepts and their precedence. | This table should contain all mappings from the new to existing Concepts and their precedence. | ||
| Line 94: | Line 87: | ||
| ^Field^Required^Type^Description^ | ^Field^Required^Type^Description^ | ||
| - | |concept_code_1|Yes|string(255)|One source code of the pair| | + | |concept_code_1|Yes|VARCHAR(50)|One source code of the pair| |
| - | |concept_code_2|Yes|string(255)|The other source code of the pair| | + | |concept_code_2|Yes|VARCHAR(50)|The other source code of the pair| |
| This table should contain relationships for each Drug Concept: To the Ingredients (always), the Dose Form (if appropriate),the Supplier (if appropriate) and the Brand Name (if appropriate). All other relationships will be derived and ignored if they exist in the table. The relationships don't need to be symmetrical, only the one initiating from the Drug Concept is required. | This table should contain relationships for each Drug Concept: To the Ingredients (always), the Dose Form (if appropriate),the Supplier (if appropriate) and the Brand Name (if appropriate). All other relationships will be derived and ignored if they exist in the table. The relationships don't need to be symmetrical, only the one initiating from the Drug Concept is required. | ||
| Line 104: | Line 97: | ||
| ^Field^Required^Type^Description^ | ^Field^Required^Type^Description^ | ||
| - | |drug_concept_code|Yes|string(255)|The source code of the Drug or Drug Component, either Branded or Clinical| | + | |drug_concept_code|Yes| VARCHAR(50)|The source code of the Drug or Drug Component, either Branded or Clinical| |
| - | |ingredient_concept_code|Yes|string(255)|The source code for one of the Ingredients| | + | |ingredient_concept_code|Yes| VARCHAR(50)|The source code for one of the Ingredients| |
| - | |amount_value|No|float|The numeric value for absolute content (usually solid formulations)| | + | |amount_value|No|NUMERIC|The numeric value for absolute content (usually solid formulations)| |
| - | |amount_unit|No|string(255)|The verbatim unit of the absolute content (solids)| | + | |amount_unit|No| VARCHAR(255)|The verbatim unit of the absolute content (solids)| |
| - | |numerator_value|No|float|The numerator value for a concentration (usually liquid formulations)| | + | |numerator_value|No|NUMERIC|The numerator value for a concentration (usually liquid formulations)| |
| - | |numerator_unit|No|string(255)|The verbatim numerator unit of a concentration (usually liquid formulations)| | + | |numerator_unit|No| VARCHAR(255)|The verbatim numerator unit of a concentration (usually liquid formulations)| |
| - | |denominator_value|No|float|The denominator value for a concentration (usually liquid formulations). It should contain a number for Quantified products, and null for everything else.| | + | |denominator_value|No|NUMERIC|The denominator value for a concentration (usually liquid formulations). It should contain a number for Quantified products, and null for everything else.| |
| - | |denominator_unit|No|string(255)|The verbatim denominator unit of a concentration (usually liquid formulations)| | + | |denominator_unit|No|VARCHAR(255)|The verbatim denominator unit of a concentration (usually liquid formulations)| |
| - | |box_size|No|integer|The number of units per box| | + | |box_size|No|SMALLINT|The number of units per box| |
| This table contains the dose of each ingredient in each drug, as well as the box_size. For drugs which have no strength information or have only for some of the containing ingredients, the ds_stage record must be omitted. '0' values in ds_stage are only allowed for inert drugs. | This table contains the dose of each ingredient in each drug, as well as the box_size. For drugs which have no strength information or have only for some of the containing ingredients, the ds_stage record must be omitted. '0' values in ds_stage are only allowed for inert drugs. | ||
| Line 129: | Line 122: | ||
| * Drugs can't have differing information for denominators among different ingredients, skip dosage for some ingredients or have same ingredient with different dosages | * Drugs can't have differing information for denominators among different ingredients, skip dosage for some ingredients or have same ingredient with different dosages | ||
| - | ==PС_STAGE== | + | ==PC_STAGE== |
| ^Field^Required^Type^Description^ | ^Field^Required^Type^Description^ | ||
| - | |pack_concept_code|Yes|string(255)|The source code of the Pack, either Branded or Clinical| | + | |pack_concept_code|Yes|VARCHAR(50)|The source code of the Pack, either Branded or Clinical| |
| - | |drug_concept_code|Yes|string(255)|The component drug product in the Pack| | + | |drug_concept_code|Yes|VARCHAR(50)|The component drug product in the Pack| |
| - | |amount|No|integer|The number of units of the drug product in a pack| | + | |amount|No| SMALLINT |The number of units of the drug product in a pack| |
| - | |box_size|No|integer|The number of packs if the pack is boxed (several packs in a larger container| | + | |box_size|No| SMALLINT |The number of packs if the pack is boxed (several packs in a larger container| |
| This table contains the composition of a Clinical or Branded Pack: The Clinical or Branded Drug and, number of doses in each box and number of boxes in each pack. If it is a boxed Pack, it will also contain the box size, since Packs have no records in DS_STAGE like the other drug products. Packs are allowed to have branded drugs as components, although usually Brand Name is only attributed to packs as a whole. Supplier may only be attributed to the pack as a whole. | This table contains the composition of a Clinical or Branded Pack: The Clinical or Branded Drug and, number of doses in each box and number of boxes in each pack. If it is a boxed Pack, it will also contain the box size, since Packs have no records in DS_STAGE like the other drug products. Packs are allowed to have branded drugs as components, although usually Brand Name is only attributed to packs as a whole. Supplier may only be attributed to the pack as a whole. | ||
| - | Box size equal to 1 can also be omitted. | + | Box size = 1 should be omitted. |
| ==CONCEPT_SYNONYM_STAGE== | ==CONCEPT_SYNONYM_STAGE== | ||
| Line 146: | Line 139: | ||
| ^Field^Type^Description^ | ^Field^Type^Description^ | ||
| - | |synonym_concept_id|integer|Always and empty field in this table| | + | |synonym_concept_id|INTEGER|Always and empty field in this table| |
| - | |synonym_name|string(255)|Alternative name of the concept. There is no need to copy the entry from DRUG_CONCEPT_STAGE| | + | |synonym_name|VARCHAR(255)|Alternative name of the concept. There is no need to copy the entry from DRUG_CONCEPT_STAGE| |
| - | |synonym_concept_code|string(50)|Concept code in source vocabulary| | + | |synonym_concept_code| VARCHAR(50)|Concept code in source vocabulary| |
| - | |synonym_vocabulary_id|string(20)|VOCABULARY_ID of source vocabulary| | + | |synonym_vocabulary_id| VARCHAR(20)|VOCABULARY_ID of source vocabulary| |
| - | |language_concept_id|integer|CONCEPT_ID for Standard concept representing language| | + | |language_concept_id| INTEGER |CONCEPT_ID for Standard concept representing language| |
| ==CONCEPT_RELATIONSHIP_MANUAL== | ==CONCEPT_RELATIONSHIP_MANUAL== | ||
| Line 157: | Line 150: | ||
| ^Field^Required^Type^Description^ | ^Field^Required^Type^Description^ | ||
| - | |concept_code_1|Yes|string(255)|CONCEPT_CODE of source concept in either [[documentation:cdm:concept|CONCEPT]] or DRUG_CONCEPT_STAGE tables| | + | |concept_code_1|Yes| VARCHAR(255)|CONCEPT_CODE of source concept in either [[documentation:cdm:concept|CONCEPT]] or DRUG_CONCEPT_STAGE tables| |
| - | |concept_code_2|Yes|string(255)|CONCEPT_CODE of target concept in either [[documentation:cdm:concept|CONCEPT]] or DRUG_CONCEPT_STAGE tables| | + | |concept_code_2|Yes| VARCHAR(255)|CONCEPT_CODE of target concept in either [[documentation:cdm:concept|CONCEPT]] or DRUG_CONCEPT_STAGE tables| |
| - | |vocabulary_id_1|Yes|string(20)|VOCABULARY_ID value of source concept| | + | |vocabulary_id_1|Yes| VARCHAR(20)|VOCABULARY_ID value of source concept| |
| - | |vocabulary_id_2|Yes|string(20)|VOCABULARY_ID value of target concept| | + | |vocabulary_id_2|Yes| VARCHAR(20)|VOCABULARY_ID value of target concept| |
| - | |relationship_id|Yes|sting(20)|Indicates the type of relation from source to target; most usually will indicate equivalence mapping ('Maps to'). Must be one of the values from [[documentation:cdm:relationship|RELATIONSHIP table]]| | + | |relationship_id|Yes| VARCHAR(20)|Indicates the type of relation from source to target; most usually will indicate equivalence mapping ('Maps to'). Must be one of the values from [[documentation:cdm:relationship|RELATIONSHIP table]]| |
| - | |valid_start_date|No|date|Date when the relation became valid| | + | |valid_start_date|No|DATE|Date when the relation became valid| |
| - | |valid_end_date|No|date|Date when the relation became invalid| | + | |valid_end_date|No|DATE|Date when the relation became invalid| |
| - | |invalid_reason|No|string(1)|Non-null entry allows for manual deprecation of existing relationship. Deprecated relationships that are absent from [[documentation:cdm:concept_relationship|CONCEPT_RELATIONSHIP table]] will not be added to Standardized Vocabularies| | + | |invalid_reason|No| VARCHAR(1)|Non-null entry allows for manual deprecation of existing relationship. Deprecated relationships that are absent from [[documentation:cdm:concept_relationship|CONCEPT_RELATIONSHIP table]] will not be added to Standardized Vocabularies| |
| This table needs not to be symmetrical like CONCEPT_RELATIONSHIP; complementary relationships will be built automatically. | This table needs not to be symmetrical like CONCEPT_RELATIONSHIP; complementary relationships will be built automatically. | ||
implementation_international_drug_vocabulary.txt · Last modified: 2021/06/09 10:15 by adavydov
Page Tools
