Using Key/Value pairs in the relational database

From Robs_Wiki
Jump to: navigation, search

Intoduction

Pros of using Key value pairs in relational database design

  • Supports simple queries very efficiently.
  • Very simple data-modeling pattern, should be understandable by anyone.

Cons of using Key value pairs in relational database design

  • The only queries that are efficient are simple, one-row-at-a-time queries.
  • Not really a data model at all, in that there's no association between attributes that form an entity.
  • Hard to use most ordinary SQL operations such as JOIN or GROUP BY.
  • You can't choose an appropriate SQL data type for the value.
  • You can't use many SQL constraints such as FOREIGN KEY or NOT NULL.
  • Pivoting the result to resemble a conventional result (one column per attribute) is a complex and expensive query.
  • You have to write a lot more application code to reassemble collections of key/value pairs into objects.