Good points @anna_corning
My initial thoughts on your proposal were based on a comparison of the RESPONSE table with the OBSERVATION table and the VISIT_OCCURRENCE_ID jumped out as something important that was missing. In hindsight though, you are absolutely correct - if the VISIT_OCCURRENCE_ID is in the SURVEY table and the SURVEY_OCCURRENCE_ID is a foreign key in the RESPONSE table, then we don't need VISIT_OCCURRENCE_ID in the RESPONSE table as well. I can't think of any use case where we would need it in both.
To explain what we are doing a little more...
The ICHOM standard sets include data from three different sources - Administrative, Clinical and Patient-reported. The latter can always be linked back to a survey, but the first two are not linked to surveys and we will continue to store them as name-value-pairs in the OBSERVATION table.
Regarding the SURVEY_SOURCE_VALUE, my intention was that it would be used in a similar way to PERSON_SOURCE_VALUE - i.e. as a means of connecting the converted data to the original source data. In our case, we ingest data from different suppliers (institutions and registries), load the data into the OMOP CDM, run a number of data quality (DQ) procedures on the OMOP data and report back to the data supplier on any issues. In the DQ report, we need to give them the original keys that they provided and not the OMOP-generated keys. This doesn't just affect survey ids, but affects visit (episode) ids and procedure ids too.
Point taken thought that the XXX_SOURCE_VALUE fields are used to map to domain-appropriate concept_ids (except PERSON_SOURCE_VALUE is not mapped to a concept_id).
I'd be happy to have a call to discuss. Send me your (and others) contact details to firstname.lastname@example.org, with suggested times and I'll set something up for next week. We are in Ireland, so I guess afternoon BST would be best. Thanks!