I need to store key-value pairs in a database, where key is either e.g. a string, and value can be one of multiple types (integer, string, float/date, GUID, BLOB). The Database is accessed through OLE DB, so I want need to stick to "normal" types.
They keys might be involved in "exists" queries, values are not involved in queries (i.e. I won't query for "all keys where value is 17"). Additional key-value-pairs will be added later.
I currently see the following options:
1. Serialized BLOB
Serializing the key-value-set (this functionality is already available) and store it as a single blob.
The only problem I have with this is that individual values cannot be updated easily when the database is shared. It's nto a problem right now (current set of values is updated only when the DB is opened exclusively) but seems like a limitation for future access.
2. Key-BLOB
A Row would consist of Key, Type, BLOB storing the raw data. makes for some ugly conversion and testing, but would allow to extend the var types later easily. I have no idea how bad the overhead is for storign BLOB's, but the number of items is low (around a dozen.
3. One column for each Value Type
A Row would consist of Key, Type, int, double, sting, blob, the type would indicate which column is used. Looks horrible to me, but also least "abusive".
4. One column per setting
(using only one row). I am not really considering this.
Ideas? Comments? Other approaches?
Another option would be to use one table per type. Use a view to make all keys visible at the same time. Here, you can add a column which tells you the type of the value so you can fetch that, too.
create view KEY_TABLES as
select key, 'INT_TABLE' from INT_TABLE
union
select key, 'STRING_TABLE' from STRING_TABLE
...