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.

Advertisements

One thought on “Whether to store data in SQL as key value pairs

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s