Re: Database design for scientific tests
Thank you very much!
So many things just went 'click' in my head.
I thought of one slight problem, but I think I may have a solution. The columns for the test and test_data tables change depending on which test is performed. 'Test1' may record just pressure before the test begins (separate from the resulting test data) and 'Test2' may record pressure, altitude, and temperature.
My solution:
Have a number of 'initial_condition_name1' 'initial_condition_name2' paired with 'initial_condition_value1' 'initial_condition_value12' within the test table. For the example above test1 would have 'initial_condition_name1'= 'pressure, kPa' and 'initial_condition_value1' = 100. I could then use or ignore as many or few of the columns as needed, depending on the test.
The same would apply to the 'test_data' table where I may need 5 different columns for the resulting data instead of just 1. But I could put in `test_date_type1`, `test_data_value1', `test_date_type2`,`test_data_value2', etc, and then use them as needed.
My other thought is to include all of the possible initial conditions within the table itself, and then only use the ones I need. Again using the above example, the test table could have pressure, altitude, and temperature columns, but for test1 only put data into pressure and leave altitude and temperature null.
Any thoughts?
Again, thank you very much for your reply, it helped immensely and I appreciate it very much!