Most database systems can represent missing values, typically as "Null" or something similar. But what if I want to represent different categories of missing data?
For categorical data, this is not necessarily a big problem. Just create additional categories for each type of missing. But this can be a more challenging problem when representing continuous data (e.g., income). Some people will use codes like 99999, 99998, 99997 to represent missings, leaving it to the user to recognize that these values are missing and recode them as such when performing analysis. I would like to avoid this. Other workarounds include having a parallel table that includes a categorical variable for each continuous variable, where the categorical variable indicates missingness and category of missingness. I would also like to avoid this.
For comparison, Stata (which, of course, is not a database management system) stores data in a single table and encodes missings as ., .a, .b, etc. Each of these categories of missing can have a different label affixed to it indicating its category of missing.
What I would love is if a database system could accommodate missings the way that stata does. Basically, different types of Nulls, each with their own label. Does anything like this exist? Is there a convenient way to simulate this functionality if it does not exist with any database or DBMS?
For context, I am a 'data scientist' (what does that even mean). We are collecting a large amount of not-very-well-organized data and normalizing it in a database. Some data is missing for reasons we don't know (data entry error?). Other data is missing because it is not applicable to the person. Sometimes it is missing because the value was implausible and we recoded it as missing.
The appropriate way to handle these missings in subsquent statistical analysis will depend upon why these variables are missing. It is important not to throw away this information as we take disorganized flat files and convert them into our well-organized, normalized data tables.
I imagine you experts might be familiar with this problem and might have some insights into how to deal with it. Maybe I am thinking about this problem the wrong way.
