SQL Database Column with Variant Data Type

Question

I am writing an application in which users are able to add "inputs" to a form. These inputs could be of multiple different types including a textbox or checkbox.

In the case of a textbox versus a checkbox, the data types are string versus boolean.

More Information About My Application

1) A user is able to create a "form" that may have multiple different types of inputs for a customer to fill out. Here are some examples. These inputs can be dynamic and are created by the user.

An input that may ask for a users name.
An input that consists of a checkbox asking if a user wants to be enrolled in a newsletter.

2) The user saves the form, and in the database, based on the type, the newly created input is saved.

3) A new user comes into the application and fills out the new form that was just created. They fill out the inputs that the original admin created such as their name and they answer yes to enrolling in the newsletter. Then they click submit.

4) The database then saves the answers to inputs. This is where my problem lies.

My question is how do you store the inputs value in a row in SQL. I have a couple solutions that I will provide but am looking for a consensus on what is typically done in this situation.

Solution #1

Input Name

Input Boolean Value - (would be NULL when not a boolean type of input)

Input String Value - (would be NULL when not a string type of input)

Solution #2

Input Name

Input Type

Input Value (of type string, and i would cast it based on the type)

Solution #3

Input Name

Input Type

Input Value Key - (weak foreign key to a table depending on the Input Type. This would not be a real foreign key because it could point to multiple different tables)

I don't recommend making database design decision based on your front end. They should be driven by the reality of the data they represent. — Tab Alleman
– Tab Alleman, Commented Dec 22, 2014 at 18:20
I agree with you. But in this case, an input represents an input name and a value for that input. The value can be multiple different types, a bool vs a string, no matter what UI is consuming the data. Are you suggesting handling all of the values as strings and casting them to their respective types based on the input type? — Professed3376
– Professed3376, Commented Dec 22, 2014 at 18:24
I think I would need a better understanding of your application to suggest anything. It sounds like you're trying to do this in a context-agnostic way, and to me, context is everything when designing a database. What good is data about "Input1"? If a developer re-orders/re-names the inputs on the front end, the relationship of the database to the front end will be lost. — Tab Alleman
– Tab Alleman, Commented Dec 22, 2014 at 18:30
I added some more information to my post. See "More Information about my Application" — Professed3376
– Professed3376, Commented Dec 22, 2014 at 18:39
This might be worth a read: Creating a Dynamic Data-Driven User Interface — SqlZim
– SqlZim, Commented Dec 22, 2014 at 18:42

Andrew · Accepted Answer · 2014-12-22 18:56:36Z

1

What about a table for each data type, in which each row points to the containing form? For example:

FormTable
FormId      SomeOtherFields
1           Blah
2           Bleh

BooleanFieldsTable
BooleanFieldId     FormId     DisplayName     Value
1                  1          Married         True
2                  2          Finished        False

IntFieldsTable
IntFieldId         FormId     DisplayName     Value
1                  1          Age             35
2                  1          Amount          4

DateTimeFieldsTable
DateTimeFieldId    FormId     DisplayName     Value
1                  2          Purchase Date   2014-12-15 16:25:56.187

VarcharFieldsTable
VarcharFieldId     FormId     DisplayName     Value
1                  1          Name            John

answered Dec 22, 2014 at 18:56

Andrew

7,9802 gold badges38 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Professed3376 Over a year ago

This isn't a bad idea. The only thing I don't like is the same issue I have currently. For FormId 1, how do I know that I should look for the value in the BooleanFieldsTable and not in the IntFieldsTable. I guess I could say, is InputType == "Boolean", go to BooleanFieldsTable

Andrew Over a year ago

You should LEFT JOIN all the tables, of course. I don't think that should be a problem. No need to store any "InputType" anywhere, you have that info in the relationship itself.

Hogan · Accepted Answer · 2014-12-22 19:09:02Z

When I implemented a system like this years ago I picked option 2. I often regretted it and wished I'd done option 3. I expect if I had taken option 3 I might have regretted that too.

Option 2 makes some things easier... less linking and less logic in the linking but more logic in the casting.

Option 3 makes some things easier... lest casting and less logic in the casting but more logic in the linking.

At the time SQL Server had a bug that made option 2 harder to implement in many cases. I don't know if they fixed this bug.

Here is an example of the bug (note it would not always manifest, depended on table size and many other factors):

  SELECT CAST(field as int) 
  FROM table1 
  WHERE selectorField = 5

In some cases where there were rows (not of selectorField = 5) that could not be cast to type int this query would fail with a Illegal cast error.

If this issue still exists you want to go with option 3.

Collectives™ on Stack Overflow

SQL Database Column with Variant Data Type

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related