Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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 Intermezzo, skip if you suffer from TL;DR:

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 most sensible solution in my opinion would be to create a TBL_TEST1_METADATA which contains an entry IFF test1 was not performed which specifies why not, but the people (clinicians with rudimentary MS-Access knowledge) aren't educated well enough to & are not willing to invest a serious amount of time to program the logic required to write values to two tables from one Access form.

My dilemma is that I see patient-decades of data going lost because of insufficient data quality, while I can't find a pragmatic solutions "DB-amateurs" can implement in an acceptable timeframe because I'm (too?) obsessed with doing it the right way [tm]. Am I missing the obvious? Or is the only way to improve the situation to send physicians to programming classes or provide them with a "programming minion" that does it for them?

PS: I'd love to ask this question on healthcare IT.SE.com, but the proposal hasn't received enough attention yet to go beta.

share|improve this question
add comment

2 Answers

up vote 4 down vote accepted

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.

share|improve this answer
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 '11 at 15:21
add comment

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".

share|improve this answer
add comment

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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