2

We're using MySQL 5.5 and attempting to design tables to store the results from tests run in a lab. The results of some of the tests can be either a plain number, e.g. "5", or a range such as "< 5" or "> 25", and this is where we're getting stuck. The < and > values are due to the limits of the sensitivity of the tests; above or below a certain threshold for each test the exact amount can't be detected. Values right at the threshold are possible, so for the example above, both < 5 and 5 would be valid values for that particular test, and we need to be able to distinguish between these values.

There's a set list of tests, so needing to add new ones easily isn't a concern. Our preliminary design has a TestResults table and each test is a column in that. We were thinking of maybe adding a column for the tests that require < and > just to hold those values or possibly having "flag" values in the application code that signals a value is below/above the threshold and having the thresholds in a reference table. Neither of these solutions seems that great though. We don't want to just store the results as strings because we'll need to be able to get statistics, averages, etc. on the results.

Anyway we were hoping someone out there had done something like this and had some ideas on the best way to do it. We've been Googling (e.g. on LIMS) and haven't been able to find anything on database designs, but it seems like it wouldn't be that uncommon of a problem... Any ideas on the most appropriate way to model this type of data would be greatly appreciated.

2

At first glance, it seems like the most appropriate thing to do would be to make the column storing the test result value nullable, and declare an additional column, perhaps an ENUM, for the out-of-range condition. For example

CREATE TABLE ...
   ...
   result_value DECIMAL(6,3) DEFAULT NULL,
   out_of_range ENUM('low','high') DEFAULT NULL,
   ... 

When a test comes back with an out-of-range result, it would seem like you wouldn't want to store anything in the `result_value` column, because when it comes to averages or other statistics the low or high threshold value would completely skew your average... but when you take the AVG() of a data set that includes NULL the denominator used to calculate the average is the total number of not-NULL values... so AVG(5,NULL,NULL,10,NULL) would be 7.5.

Setting a flag that indicates that your result was, instead, out of range, would allow you to easily tally those values separately, and an ENUM column with a small number of possible values requires only 1 byte of storage per row... the "low" or "high" would actually be stored as the byte 0x01 or 0x02, with the corresponding labels stored in the table definition only.

When the minimum threshold is "5" the number "5" is not a meaningful number when it comes to analysis, so I would think that you wouldn't likely want to store that in the `result_value`. If you did, your analytic queries would have to go to extra steps to exclude those from calculations.

I would suggest that you do need a table that specifies the high and low boundaries for each test, because such a table could be used to constrain the data going into the results table via triggers, blocking the insertion of out-of-range data, in addition to being valuable information when doing analysis.

"But we already know those values." The database also needs to "know" those values, because subsequent analysis will be more straightforward if those values can be joined and understood by a query.

Also, I would suggest that if there is any possibility that the sensitivity, selectivity, granularity, precision, range, or whatever the proper term might be, of any test could change -- due to improved technology or whatever the reason, that you consider each test to actually be a 1 of N possible subtypes of a type of test (where initially, N is 1). If the possible range (or other property) of a result is different, then that's technically, arguably, a different test, even though it's measuring the same thing... and these are the kinds of things that are a small hassle now, and a much bigger hassle later.

needing to add new ones easily isn't a concern.

Famous last words. Speaking of bigger hassles later, that's generally not a good position to hold, even if it seems unshakable now.

A final word about a common design error I sometimes encounter: If you are using the FLOAT or DOUBLE data types, reconsider that decision, because they are imprecise. A DECIMAL column has a fixed precision and stores exactly the value you insert. Data stored in FLOAT and DOUBLE columns are stored as approximate values which can lead to problems with comparisons. This limitation is one of floating point arithmetic in general, and not of MySQL in particular; it is something with which you may already be familiar but I thought it worth mentioning.

  • Thanks so much for your answer. You've raised some great points. We'll have to find out if the sensitivity of the tests can ever change; that hasn't been addressed at all. We've also been going back and forth on storing thresholds, so your advice on that is helpful, too. – Maltiriel Oct 8 '13 at 17:03
  • I'm accepting this since it does seem like the best way to do it, after thinking about it some more. Thanks again for the help! – Maltiriel Oct 9 '13 at 17:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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