I work in healthcare IT, reviewing data management processes of various observational studies. One problem I have repeatedly faced is the poorly encoded data, especially when some values are missing.

Background, skip if you like

I am currently reviewing a study which collects data on patients suffering from a specific disease. Patients usually join the study once the disease is confirmed (a few months after the outbreak). One of the important parameters is the result of a certain blood test in the acute phase of the disease, i.e. hopefully as close to the outbreak as possible. Sometimes however, that test was not performed because there was no indication for it or the patient forgot to bring a copy of the result, etc.

An important aspect here is that all the data in the DB is generated from (tons of unsorted) paper files and often people do not have the time to skim through all of it, so they do not enter the blood test's result just because they haven't gotten to it yet.

In order to "somehow" encode reasons for a value being absent, I have seen various schemes:

  1. Use a TEXT(3) field for booleans and use n/a to express that "the data really isn't available" (e.g. because the blood test wasn't performed) and NULL to express "Maybe the data is somewhere, I just haven't looked for it yet".

  2. Use an additional field in the same table, e.g. a boolean field "bloodtest_perf" where "perf" stands for "actually performed".

I dislike the first approach because it allows you to enter "yes", "y", "ja", "YES", etc. and you end up spending most of the time cleaning instead of analyzing data. The second approach isn't much better either, because you end up with dummy data at best and inconsitent data at worst:

__ TBL_TEST1 __________________________________
| patID | test1_perf | test1_date | test1_res |
| 12345 | no         | NULL       | NULL      |
| 12345 | yes        | 2011-05-13 | 20.0      |

The best solution I could come up with is to create a TBL_TEST1_METADATA which contains an entry iff test1 was not performed, which then specifies why not, but the clinicians (with rudimentary MS-Access knowledge) are struggling with this normalization approach.

What is a pragmatic yet effective solution for this problem?

2 Answers 2


There are two common ways of going about this sort of thing:

  1. Non-normalized, with a status field and a result field. The status field can be a bit, char(1), char(3), whatever, it doesn't matter. You allow NULL values in this field but more importantly you put a constraint on it - this is how you prevent garbage like "ja" and "yyy" from going into it. Obviously, a NULL value means "don't know", and anything else is an actual status.

  2. Normalized; you create a second table for the tests and results. It would likely have a TestIndex field and a non-nullable status field (with a similar constraint as in #1). If the status of the test is unknown, then the row simply does not exist at all.

Which version is better is going to depend on the other aspects of your design, indexing and performance requirements, etc. The normalized version is more time-consuming to design against, but has the smallest chance of ever being misused, since no coupling exists. The problem with the first version is that you need to look at the value of the status field to interpret what's in the result field, likely resulting in a lot of repetitive code.

If you're comfortable with the tradeoffs, go with #1. Otherwise, normalize it as in #2.

  • Addendum: You can also have the status be a foreign key into a TestStatus table instead of relying on a constraint, but I wouldn't recommend that if you have only 2 or 3 statuses. That's another tradeoff you have to make when dealing with the logical concept of enumeration in physical databases.
    – Aaronaught
    May 13, 2011 at 15:21

Profound insight here: NULL has no useful semantics.

Use an additional field in the same table, e.g. a boolean field "bloodtest_perf" where "perf" stands for "actually performed".


That is precisely what you must do.

A single field may not be sufficient. If you have multiple, independent processes that contribute to acquiring and validating an individual datum, you may need a column for the current state of each independent process.

Raw Measurement is "missing" or "in doubt" or "not available yet"

Confirmation of the Measurement is -- independently -- "missing" or "in doubt" or "not available yet".

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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