Below is a simplified version of my current tables. Each test type is a type of test (eg: one table is for different appearance tests, ie substance is yellow and sticky, or substance is green and dry, where as another table might be for microbial counts in food products).
A result set is just a group of results for each test. So a test can be tested 5 times in one batch, so that would be a result set for a test that has 5 results.
What I really want is just one result set table and one results table as right now it's overly complicated and there's duplication. How do I do that when there's three different types of tests? Ultimate the results can be boiled down to a numeric value OR a pass/fail value.
I could have three columns in the result set table (Type1TestId, Type2TestId, Type3TestId) and populate whichever the result set belongs to but that seems ugly. I could have a new parent table called Tests and from there have a link to the TestType tables and each Test in TblTest would link to one of the three test type tables?
All my solutions seem ugly, so I'm asking for help.
I could do something like this?