Database Design where there's three parent tables and only 1 child table

Question

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?

Answer 1

Your ResultsForType* and ResultSetFoType* tables are the same, there is no difference between them. Your problem is only about the Type?Tests tables.

Your problem is very common: how to map class inheritance and polimorphism to the ORM level. There are two most common practices to solve it:

1. What you are currently doing. You could make it redundency-free, on the cost of a much more complicated table structure. In exchange, your database will be pure and perfect, although a little bit overkill.
2. You can unify the Type?Tests tables, latter I explain, how.
3. Such a solution, where you get everything without losing anything, does not exist.

The sad truth is that SQL does not know polyporhism, and this is the ultimate cause of your current troubles. But it knows something which can help a lot to achieve some quite similar.

It can nullable values.

In your case, I would simply use instead of the Type?Tests tables this:

+----+------------+-----+-----+------------+------------+
| id | TestTypeId | min | max | daysStored | NoMoreThan |
+----+------------+-----+-----+------------+------------+

The essence is that we use a union of all possible columns in all the used classes, plus yet another column to identify the type.

Use the following constraints:

• TestTypeId should be 1, 2 or 3
• If TestTypeId=1, daysStored and NoMoreThan should be NULL
• If TestTypeId=2, NoMoreThan should be NULL
• If TestTypeId=3, daysStored should be NULL

Constraints are very useful - they make things guaranteed.