Storing values in SQL whose types are determined in runtime

Question

I have come across a need of storing runtime determined values in a SQL database.

For example, there is a GUI where a user can add new editable fields. So the user adds a field Name, chooses the value type String and fills in the value Arthur. Then the user adds a new field Money, chooses the value type Decimal and fills in the value 10.05.

As seen above, the user is able to add any fields with any value types.

My idea is to have a SQL table schema as follow:

• FieldName (varchar) - eg. 'Money' or 'IsAlive'
• FieldType (varchar) - eg 'Decimal' or 'Boolean'
• FieldValue (varchar) - eg. '10.5' or '1'

This would mean that I can store any pre-defined data type in the table whether it's a string, boolean or long.

The problem is that I am not sure it's the best approach to cast those values to string and then cast again when reading from the table.

Another option would be to have a column of each type but that sounds even worse.

Does my approach to cast everything to string sounds sensible or should I try something else?

Answer 1

It all boils down to whether these extra fields will need to be searchable, as well as whether you are going to have few rows (hundreds) or many rows (hundreds of thousands.)

You see, the string "1000" compares below the string "900", so any search for a number will have to be be implemented using a full table scan. Plus, obtaining rows sorted by a user defined numeric field will need to be implemented by loading all the rows into memory, converting the strings to numbers, and sorting the rows in memory by comparing the numbers. Which is something you do not want to do if you have hundreds of thousands of records.

So:

If your user-defined fields are not going to need to be searchable, or if you are only going to have a few rows, (so full table scan is acceptable,) then casting to string should be fine. As a matter of fact, you do not even need to go with a name-type-value table, you can just add one long text field to one of your existing tables, and this text field can contain all your sub-fields together, encoded in XML or JSON.

However, if these user defined fields need to be searchable AND there is a possibility that you might have many rows, then this approach will not work. What you can do in this case, is have a name-value table for all strings, a name-value table for all integers, a name-value table for all floats, etc.