8

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
9

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!

  • As a counter to Bill, I've written in favor of EAV here: sqlblog.com/blogs/aaron_bertrand/archive/2009/11/19/… – Aaron Bertrand Jun 6 '16 at 18:16
  • I don't think having multiple possible types for the same logical column constitutes an EAV. I think he's looking for a "variant/object" type. That's much easier to do with a few columns than one table per type. That sounds very inconvenient. And after the view is created it's really the same thing as creating the types in the same table in the first place. – usr Jun 6 '16 at 18:31
  • @usr I'd be interested in seeing your solution. – Jared Beach Jun 6 '16 at 18:41
  • 2
    Just one column per type seems alright to me. I have done it like that in the past. It's the least bad solution. Here's another argument: What if there were two variably typed columns. This answer's solution requires a quadratic number of tables. One could split it into a basic table plus N*M more tables... This does not seem right. – usr Jun 6 '16 at 18:43
  • @usr isn't one problem with your solution that none of those columns can be NOT NULL? Personally, I'm a big believer in having a NOT NULL constaint whenever possible. – Vérace Jun 6 '16 at 19:09
1

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.

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.