Best way to store a value which could be multiple types

Question

I'd like to re-ask a question in a more direct and general way:

How do you create a table to store values which could be multiple different types?

In my case, the values provide diagnostics about an event. Ex: Event occurred -> Store readings from multiple PLC's which contain pertinent information about the event. The PLC's could monitor any type of data.

Some examples I can think of:

• Create a column for every possible type and create another column to indicate which column to use
  • Ex: Cols: IntVal, StrVal, BoolVal, Type. Vals: null, null, True, "BOOL"
• Store the values no matter what as a varchar

Answer 1

It would appear that you have already been told that you are straying down the road to the EAV model. Take a look at the image here for why the EAV model should be avoided at virtually all costs.

Bill Karwin, the author responsible for the image above, has written a book "SQL Antipatterns: Avoiding the Pitfalls of Database Programming" and he devotes the first chapter to the EAV anti-pattern. He's also a big hitter on this group and huge on StackOverflow (for database issues).

My advice would be to have a table for each type of result and then to use VIEWs to combine them when necessary.

As an example, you could have

CREATE TABLE char_result
(
  question_id INT,
  user_id INT,
  cresult CHAR,
  result_correct BOOLEAN (or equivalent in your RDBMS)
  ..
  <other stuff>
  ..
);

Do the same for num_result, except substitute nresult INT (FLOAT... whatever) for cresult - same idea for VARCHAR &c.

Then create VIEWs over your different result tables for result_correct (and other fields - number_of_attempts... &c. - whatever your other fields are). In this case, you are comparing like with like and not peforming calculations equivalent to adding population to altitude as per the hilarious image referenced above!

Answer 2

I think if it was absolutely necessary to store a value this way in a relational database, I'd use a solution like Microsoft SQL Server's JSON columns.