1

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.

enter image description here

I could do something like this?

enter image description here

0

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.

  • Thanks for your reply PeterH. You're spot regarding the source of my problem, as I'm a developer trying to do database design... I thought about solution number 2, but is that good design? My example tables are simplified. The real thing will have maybe twenty columns if I merge the tests. That's a lot of columns that will be null if they're of the other two types of tests. – Jack Reilly Jun 11 '18 at 1:27
  • @JackReilly Yes, it is perfectly known. There are many ORM layers doing this automatically. For example, the hibernat ein Java does this. Even the Stack Exchange does this: here Questions and Answers are in the same database table, but for example the AcceptedAnswerId field is always NULL in Answer entities (because only Questions can have accepted answers). – user259412 Jun 11 '18 at 1:45
  • @JackReilly Many NULL columns are absolutely not a problem, they won't take place on the disk. I've seen database tables with around 40 columns, majority of them were NULL. Of course if the set of the used columns are disjunct or nearly disjunct, then this solution may be sub-optimal. – user259412 Jun 11 '18 at 1:46
  • @JackReilly Btw, most likely you use the DB through an ORM layer, like hibernate (not knowing .net, but because you use MS SQL I suspect you are using also .net, I don't know what is its name). So you only have to solve that which members of your classes should map to which column. It is likely not so hard. – user259412 Jun 11 '18 at 2:22

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.