Adding a source value column for IDs to the visit_occurence table

CDM or THEMIS convention?

THEMIS

Field

visit IDs mapped from the source table are not integers and are a bit messy,
created an auto incremented numeric id for visit_occurence_id.
User do not want to lose these source IDs as they might come useful when a use case requires a link back to the source dataset.
How to retain the original source IDs for potential future use cases requiring a link back to the source data?

Author 1

I understand what you are asking here. You have visit_ids that do not conform to the integer data type of the VISIT_OCCURRENCE_ID and want to store them somewhere.
You are correct that VISIT_SOURCE_VALUE is used for the source value of VISIT_CONCEPT_ID like IP, OP, etc.
Can I ask your use case for needing to keep the source id here? In databases where I am unable to keep the source it is still feasible to trace back using the PERSON_SOURCE_VALUE and date of service.

Response to question:

A custom local table sounds like a good solution that would preserve my source values without altering the OMOP CDM schema.
To answer your question, I wanted to keep the source ID for 2 reasons
1. If I am adding more visit_occurences in the future, I will need the source primary key to make sure I am not remapping an existent visit_occurence.
2. When mapping another table, for instance, condition_occurence, I will need to join my local condition table T1 (that is equivalent to condition_occurrence in the CDM) to visit_occurence T2 using a primary key of T2 that exists in T1. Regarding tracking back using the person_source_value and the date of service, that would definitely work! Thank you for referencing other resources and for your advice.

Author 2:

Back at Monte, we locally maintained mapping tables, containing source database, table, and column and respective OMOP table and column. This could work for multiple scenarios, including when multiple source records are mapped to one OMOP record. You may go further by adding transformation rule to the table, if any. This is a good way to track provenance of your data.
If I remember correctly, @Daniella_Meeker had proposed to introduce one Event table containing all the source record IDs mapped to various OMOP domain record IDs to preserve provenance of all the records ETLed into OMOP CDM.

Please use this section to add any other relevant notes or comments. (i.e. if links are not working, something is out of date or deprecated)