I have a database design problem which I need some help with.

I am developing a database to store some engineering lab test results. But the main problem I am running into is that each Test is completely different from each other in terms of inputs and results. For example, Test1 measures Measurement1 at three different Frequencies. Test2 measures Measurement2 at five different Temperatures.

If I am running multple Tests on sample, what is the best way to go about it?

  1. I can create independent tables for each Test and address this in the interface code.

  2. I can have a normalized database with Test table and Results table. Store the columns required for all the Tests in there, eventhough some will be empty for a particular Test.

Any help on this is much appreciated. Thanks.


Neither of your approaches is good.

You have Tests that produce Results for some Measurement that is done with some Method. The Method may applies to multiple Test. (see sample Data below)

So this is my approach

Test(**TestID**, Name, Description, ...)
Measurement(**MeasurementID**, TestID[FK], Description)
Result(**Measurement[FK]**, **ResultNr**, ResultValue, Time)

Sample Data:

0 | Rat in the Maze | Our rat John in the large Maze testing him find the exit
1 | Water on the oven | Boiling Water on our nuclear oven 

0 | 0 | Distance to Exit in cm
0 | 1 | Aggressiveness of the rat in Units of Aggressiveness 
1 | 1 | Temperature of the water in K
2 | 1 | Energy currently consumed by nuclear oven in W
3 | 1 | Amount of water evaporated

0 | 0 | 10 | 0s
0 | 1 | 12 | 2s
0 | 2 | 7  | 6s

Ok however this still relies on your use case. Maybe you want to keep some other table for input circumstances and map them to your results as a FK.

But note how it differs from your approach:

  • Number of tables independet from number of Tests, Results, Mesuret Values
  • No Empty rows and expandable for more Measured Values.

You can still add more information (Columns) to the Measurment table to keep track of the Device used to measure, the accuracy or whatever.

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.