Protocol to add in-house concept_ids in v5

Hi - In our case, we’ve done what @Patrick_Ryan describes for one-off cases, but created local concepts for either 1) situations in which a particular value is mixed with standard values in the data (e.g. a custom lab result), and we want to be able to phrase queries against foo_concept_id for all cases, or 2) situations in which we want to record relationships among network-local concepts in concept_relationship so they’re explicit in the database.

Here’s our checklist for new terms, in case it’s helpful:

To add a term to the vocabulary:

  1. Insure that appropriate concepts for vocabulary_id, domain_id, and concept_class_id exist in the concept table and in the appropriate metadata table. If not, recurse.
  2. Insert a row with appropriate values into the concept table. Use the next available value in the concept ID sequence (2000000000,3000000000].
  3. Iff the new concept is a standard concept, insert identity mappings into concept_relationship:
  • insert into concept_relationship (concept_id_1, concept_id_2, relationship_id, invalid_reason, valid_start_date, valid_end_date) select concept_id, concept_id, 'Maps to', NULL, valid_start_date, valid_end_date from concept where concept_id =n;
  • insert into concept_relationship (concept_id_1, concept_id_2, relationship_id, invalid_reason, valid_start_date, valid_end_date) select concept_id, concept_id, 'Mapped from', NULL, valid_start_date, valid_end_date from concept where concept_id =n;
  1. Iff the new concept is not a standard concept or a metadata concept (and you’re just not using source_to_concept_map so everything is in one place), insert the appropriate ‘Maps to’/‘Mapped from’ rows into concept_relationship.
  2. Insert other optional mappings into concept_relationship as appropriate.
  3. Iff the new concept is a standard concept, insert at least an identity mapping into concept_ancestor: insert into concept_ancestor (ancestor_concept_id, descendant_concept_id, min_levels_of_separation, max_levels_of_separation) select concept_id, concept_id, 0, 0 from concept where concept_id =n;. You may also want to include other mappings if the new concept is part of a hierarchy.
1 Like