Database design for scientific tests
Hello everyone,
I have a problem I've been working on. I have some database experience but I am no expert.
I am trying to create a database for some scientific tests. The basic idea is that there are samples (think rocks taken from different locations) that have tests conducted on them (5 or more different types of tests, each that have some specific data regarding the pre conditions associated with them), and tabular data results from different tests (2 to 8 columns worth of data, depending on which test was conducted).
So, the three main entities are:
-Samples, which have some data specific to them: location, type, etc.
-Tests (performed on individual samples) many different types of tests, many tests could be performed on one sample. Each test will have some data specific to it, such as pressure or date test occurred.
-Test Data (results of different tests). The results could be 2 columns of data or it could be as many as 8 depending on the test that was performed.
My problem is that I can't wrap my head around how to model the tests and test data. Samples are quite easy because it is a usual database type entity.
My question is should each type of test (say TestA, TestB, TestC) all be different tables, connected to their own test result tables? (say TestAResults, TestBResults, TestCResults)
So that it is like:
Sample - TestA - TestAResults
\ TestB - TestBResults
etc
Then for querying purposes find the sample# (pk) in the sample table and then join to TestA, see if sample# (fk) is in there, then join TestB see if sample# (fk) is in there, then join to TestC, see if the sample# (fk) and on and on depending on the number of Tests? This seems wrong to me, but might work?
I've searched for "data model for scientific tests" or "database tabular data" but because tables and tests come up in just about every database topic I can't seem to find any specific data.
Any help is appreciated greatly! Thank you in advance!