Whether to store data in SQL as key value pairs

It can be tempting to store unstructured data as key value pairs to avoid lots of schema changes and capture dynamic data.  But it can lead to problems.

  • Overly complex queries/reporting
  • No type support

This article explains some of the issues.

My conclusion is that it is fine if you will only be accessing the data rather than querying it, or as a temporary capture means until you’ve identified where the data really belongs.

If you have another way to query, like a Lucene index it may also be perfectly fine.

Update:

A key value store NoSQL solution may be appropriate, but that brings with it other baggage.  Depending on the NoSQL solution you choose, you may have to do some extra work to get the type of search you need or use a Lucene style indexing solution anyway.

If you are using PostgreSQL, you can get much of the best of both worlds using hstore.

And you can easily use it with rails!

This will have the drawback of not being portable to other SQL backends, but that may be a tradeoff worth making.

With the PostgreSQL solution, you can retain one datastore and still allow some ability to store unstructured key values.  Then as needed, you can integrate these back into the schema to permanent normalized columns.