title: Key/Value pairs in a database table
body: <p>I need to design a Key/value table in my database and I'm looking for guidance on the best way to do this.  Basically, I need to be able to associate values to a dynamic set of named properties and apply them to an external key.</p>
  
  <p>The operations I need to be able to support are:</p>
  
  <ul>
  <li>Apply a key/value pair to a group of items </li>
  <li>Enumerate all of the currently-active keys</li>
  <li>Determine all of the items that have a value for a given key</li>
  <li>Determine all of the items where the value associated with a given key matches some criteria.</li>
  </ul>
  
  <p>It seems that the simplest way to do this is to define a table:</p>
  
  <pre><code>CREATE TABLE KeyValue (
    id    int,
    Key   varchar...,
    Value varchar...
  );
  </code></pre>
  
  <p>It seems that I am likely to be duplicating a lot of data in the Key column because I any given key is likely to be defined for a large number of documents.  Replacing the Key varchar with an integer lookup into another table seems to alleviate this problem (and make it significantly more efficient to enumerate all of the active keys), but sticks me with the problem of maintaining that lookup table (upserting into it whenever I want to define a property and potentially removing the entry any time a key/value is cleared).</p>
  
  <p>What's the best way to do this?</p>