Representing Key/Value Pairs Where the Value Can Be of Any Type (in a Relational Database)

Question

A small but important corner of a database that I'm designing will be used to store the result of arbitrary calculations. These results can be of any type. How can I represent a Value field that can be of any type in a relational database?

The only thing I can think of is to have separate tables based on data type that all have foreign keys back to a generic table. That doesn't seem right to me because in order to get the values back, I would have to join on a bunch of different tables. There must be a better way.

Answer 1

Can you serialize the result to some sort of binary format? In that case 'image' would hold your data, and you could store the type itself as a field as well.

Answer 2

As long as your abstraction layers have the ability to serialize all the types, store them as binary data and serialize-in, deserialize-out. Maybe with a column indicating datatype. If you have nothing but CASTable datatypes, you're even more set.

Answer 3

One approach I've used before is to store the serialized value, with a separate string field identifying its type. So for example if you are in .NET, you might store "System.Decimal" and a serialized decimal value. This works with binary or XML serialization, with either a BLOB or a long string field respectively.

Answer 4

You could store all the values as a VARCHAR. When you get the value out of the database, you can try to match it with different Regular Expressions. Have a regular expression for each of your data types. Whichever one it works on, you have your data type.

Answer 5

Your best option is probably going to be to serialize the data and use either string or xml as others are suggesting.

Another option if you are using MS SQL Server is the SQL_VARIANT type, which can store anything except LOBs. I'm not recommending this option over serialization however as serialization work on any platform and is easier to understand and optimize.