Mysql – How to map dynamic test result and reference range data in database


I'm designing a database for pathology laboratory. I'm currently using this database design to implement the laboratory. I have to map medical test results and reference data in the database.

enter image description here

  1. Medical Test result may have a result like Negative/Positive or a numeric value or a color etc. I currently decided to add a result_type item in test_data_items to add a result accordingly.

  2. Medical Test result also has the indicator high value or low value according to the test value, gender, age etc. I added a high, low values in the test_data_items initially. But it'll not going to work with correctly because high value for a woman will not be high value for a man.(I need to identify whether a test result is high or low for a given test result value)

For the above problems my initial solutions is not going to work. How can I resolve the problems with the correct database design to store such those.

Thank you.

Best Answer

One possibility would be to add a table for test item baselines (perhaps there would be a better name) - it would have a FK to your test_data_items table on the id with the PK of this table to be the id and sex (could also contain an age/age range column if that is significant to the data). Your data would then be the high and low (possibly "normal" as well) for each combination of test, sex and optionally age.