CONCEPT_ID (to NULL or not to NULL)

[ericaVoss]

TYPE	NOTES
ITEM	When should CONCEPT_ID be NULL and when should it be 0?
FORUM POST	http://forums.ohdsi.org/t/concept-id-to-null-or-not-to-null-themis-wg3/3965/46
SOLUTION	Foreign key into the Standardized Vocabularies (i.e. the standard_concept attribute for the corresponding term is true), which serves as the primary basis for all standardized analytics. For example, condition_concept_id = 31967 contains reference value for SNOMED concept of 'Nausea'. Set the value to 0 'No matching concept' when a mapping to a standard concept cannot be found (except for MEASUREMENT/OBSERVATION.VALUE_AS_CONCEPT_ID, MEASUREMENT/OBSERVATION.UNIT_CONCEPT_ID, MEASUREMENT.OPERATOR_CONCEPT_ID which can be NULL if data does not contain a value).
NEXT STEPS	1) Submit recommendation to the CDM WG to make all CONCEPT_IDs NOT NULL columns (except for MEASUREMENT/OBSERVATION.VALUE_AS_CONCEPT_ID, MEASUREMENT/OBSERVATION.UNIT_CONCEPT_ID, MEASUREMENT.OPERATOR_CONCEPT_ID which can be NULL if data does not contain a value). 2) Update language in the WIKI under CONCEPT_ID https://github.com/OHDSI/CommonDataModel/wiki/Data-Model-Conventions

[katy-sadowski]

Hi @ericaVoss , @clairblacketer , @MelaniePhilofsky - what do you think about expanding the list of fields which can be left NULL if no source value is available? There are other non-required concept ID fields to which I think this convention should also apply (e.g. ADMITTED_FROM_CONCEPT_ID, DISCHARGED_TO_CONCEPT_ID, CONDITION_STATUS_CONCEPT_ID). If you agree, I'm happy to submit a PR to CDM docs :) thanks!

[clairblacketer]

Hi @katy-sadowski I agree these are good contenders :) This would constitute a change to the model, not just the documentation so we can put it in as a proposal for v5.5

[MelaniePhilofsky]

Per the CDM v5.4 documentation, the fields @katy-sadowski identified above are not required. So, a change to the model isn't necessary. They are already nullable.

Katy - Which documents need to be changed?

[clairblacketer]

@MelaniePhilofsky that makes sense. In the documentation it does not state that the fields can be null while in other fields it does, like unit_concept_id.

[katy-sadowski]

The docs that need updating are:

• Individual column descriptions here: https://ohdsi.github.io/CommonDataModel/cdm54.html (explicitly state it should be left NULL if no source value - right now, the language is somewhat ambiguous)
• Add the fields to the list of exceptions here: https://ohdsi.github.io/CommonDataModel/dataModelConventions.html#Source_Values,_Source_Concept_Ids,_and_Standard_Concept_Ids

I think this can go in 5.4 since it's more of a clarification than change to convention?

[MelaniePhilofsky]

Yes, I agree this is a clarification and not a change. And if it is ambiguous, then let's fix it.

Should we create one general statement, "If a field is not required, then leave NULL if you do not have source data for the field"? Or should we add a sentence to every nullable field? I prefer concise documentation, we already have pages and pages of CDM documentation.