1

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.

1

SQL has a single category NULL and boolean algebra is extended to the ternary case with all the boolean operators AND, OR, NOT and XOR are modified with respect to the third logical state "Unknown" or "Unavailable". All the other subcategories can be implemented as reference table of arbitrary numbers of states. That is for relational databases.

  • " All the other subcategories can be implemented as reference table of arbitrary numbers of states." I'm puzzled over how you would implement this. Suppose I have a table that describes some continuous attribute (say, income). Currently, some values are Null. Other values are impossible and I want to code them as Null. But I want to retain information on whether the value was Null when the data was received or Null because we recoded it. How would a reference table allow us to accomplish that? – user3120868 Feb 1 at 18:08
  • The table of two fields id and the_reason_to_be_null for reference and additional column nullity_reason with FK to the reference table aside the income column. nullity_reason equal to NULL means income IS NOT NULL and all the other values, listed in the reference table, describes the variety of reasons why the income can become NULL. You can add as many reasons to the reference table as you need and extend it in any desirable way. – Kondybas Feb 1 at 19:50
  • I can see how this approach would work but I was hoping that there exists a more elegant solution. This effectively requires duplicating every table (for which I want to capture different categories of missingness). In my original question, I alluded to this potential solution as "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." This solution also requires more code (and opportunities for mistakes) when reading in the data (undesirable). – user3120868 Feb 3 at 17:49
  • @user3120868 Can you explain why the duplication is required? Why you can't hold the single reference table for all the kinds and categories piled together? – Kondybas Feb 4 at 9:19

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.