The structure of the table is very simple, for every source code you just list where you think it should map to the standard terminology. Here are our examples that we can share (i.e. the non-proprietary containing lists).
Additionally there are tools to help you map source codes to standard terminology and then ultimately produce a SOURCE_TO_CONCEPT_MAP table (see Usagi).
One of our databases coding system is considered proprietary. We have a method of mapping that is a combination of using information provided by the vendor to link up to other source codes in the OMOP Vocabulary codes and mapping by USAGI to get the proprietary codes mapped to standard terminology.
To use it in an ETL, if you open one of our ETL documents, in the “Source to Standard Terminology” section you’ll see we have a standard query. This query either pulls from the Vocabulary or the SOURCE_TO_CONCEPT_MAP table. If I need a map we generated, I filter my query using one of our defined VOCABUALRY_IDs found in the SOURCE_TO_CONCEPT_MAP like this instead:
WHERE SOURCE_VOCABULARY_ID IN ('JNJ_TRU_P_SPCLTY') AND TARGET_STANDARD_CONCEPT IS NOT NULL AND TARGET_INVALID_REASON IS NULL
But if I need a standard map found in the OMOP Vocabulary I would use the same query referenced above and call it with filters like this:
WHERE SOURCE_VOCABULARY_ID IN ('LOINC') AND TARGET_STANDARD_CONCEPT IS NOT NULL
Once I have built all our SOURCE_TO_CONCEPT_MAP files, I just load them into the table. What I like about this is if I do something wrong in the load, I can just truncate this table because the OMOP Vocabulary doesn’t use this table for anything. I’m not touching any of the core OMOP Vocabulary tables and can’t accidentally screw them up.
No, when you use the SOURCE_TO_CONCEPT_MAP you don’t need to worry about giving them CONCEPT_IDs and managing any of that.