If I may throw a technical point to this argument of NULL or not to NULL.
DBMS with MPP architecture apparently dont like NULLs. Apparently NOT NULL columns perform significantly better compared to NULLable columns. JOINS and WHERE work significantly better with NOT NULL columns. You ask me for reference – i heard this someplace and it was stored in my memory some place. Maybe @Chris_Knoll knows! Maybe it is how the ‘distribute on’ works - maybe MPP cant distribute NULLs. Also - having not gone to formal SQL school, i learnt the hard way that NULL is not equal to NULL
I am in favor of some flavor of integer – maybe negative integers to represent flavors of NULL, like -1, -2, -3 etc because all our concept_id’s are positive integers anyways… So we can clearly define each flavor of special information without any ambiguity and develop conventions on when to use what. This could be then in the OMOP vocabulary as an OMOP-generated concept_id with a vocabulary-id called ‘Special Values’, and domain ‘Special Values’, and concept_class_id ‘NULL flavors’. Then all negative value concept_id’s are some form of special concept’s and we could support a lot of such special situations using OMOP vocabulary.
This may make writing parameterized SQL and R packages easier - because we dont have to do stuff like
where concept_id in (33423, 32432342) or concept_id is null
we can just do where
where concept_id in (33423, 32432342, -1)