How would you structure your entity model for storing arbitrary key/value data with different data t

Posted by Nathan Ridley on Stack Overflow See other posts from Stack Overflow or by Nathan Ridley
Published on 2010-06-07T08:59:20Z Indexed on 2010/06/07 9:02 UTC
Read the original article Hit count: 215

Filed under:

I keep coming across scenarios where it will be useful to store a set of arbitrary data in a table using a per-row key/value model, rather than a rigid column/field model. The problem is, I want to store the values with their correct data type rather than converting everything to a string. This means I have to choose either a single table with multiple nullable columns, one for each data type, or a set of value tables, one for each data type. I'm also unsure as to whether I should use full third normal form and separate the keys into a separate table, referencing them via a foreign key from the value table(s), or if it would be better to keep things simple and store the string keys in the value table(s) and accept the duplication of strings.

Old/bad:

This solution makes adding additional values a pain in a fluid environment because the table needs to be modified regularly.

MyTable
============================
ID    Key1    Key2    Key3
int   int     string  date
----------------------------
1     Value1  Value2  Value3
2     Value4  Value5  Value6

Single Table Solution

This solution allows simplicity via a single table. The querying code still needs to check for nulls to determine which data type the field is storing. A check constraint is probably also required to ensure only one of the value fields contains non-nulll data.

DataValues
=============================================================
ID    RecordID    Key    IntValue    StringValue    DateValue
int   int         string int         string         date
-------------------------------------------------------------
1     1           Key1   Value1      NULL           NULL
2     1           Key2   NULL        Value2         NULL
3     1           Key3   NULL        NULL           Value3
4     2           Key1   Value4      NULL           NULL
5     2           Key2   NULL        Value5         NULL
6     2           Key3   NULL        NULL           Value6

Multiple-Table Solution

This solution allows for more concise purposing of each table, though the code needs to know the data type in advance as it needs to query a different table for each data type. Indexing is probably simpler and more efficient because there are less columns that need indexing.

IntegerValues
===============================
ID    RecordID    Key    Value
int   int         string int
-------------------------------
1     1           Key1   Value1
2     2           Key1   Value4

StringValues
===============================
ID    RecordID    Key    Value
int   int         string string
-------------------------------
1     1           Key2   Value2
2     2           Key2   Value5

DateValues
===============================
ID    RecordID    Key    Value
int   int         string date
-------------------------------
1     1           Key3   Value3
2     2           Key3   Value6

How do you approach this problem? Which solution is better? Also, should the key column be separated into a separate table and referenced via a foreign key or be should it be kept in the value table and bulk updated if for some reason the key name changes?

© Stack Overflow or respective owner

Related posts about database-design