2

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?

Improve this question
2
  • 1
    You just reinvented entity-attribute-value. Commented May 27, 2015 at 21:39
  • This sounds like an inner-platform effect problem. Depending on the details, consider allowing the GUI to execute ALTER TABLE statements (with certain limits, of course) and add fields to the actual table. Commented May 28, 2015 at 4:30

1 Answer 1

Reset to default
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.

Improve this answer
2
  • Hm, it seems I am somewhere in the middle. I need those values to be only settable and gettable. So no search is needed. However, the numbers of rows might grow with time to thousands. Commented May 27, 2015 at 22:57
  • 1
    From what you are saying you are not somewhere in the middle, because you do not need search, so the number of rows that you expect to have is irrelevant. The number of rows would matter only if searching and sorting was important. Commented May 28, 2015 at 7:02

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.