title: Key value pairs in relational database
body: <p>Does someone have experience with storing key-value pairs in a database?</p>
  
  <p>I've been using this type of table:</p>
  
  <pre><code>CREATE TABLE key_value_pairs ( 
      itemid           varchar(32) NOT NULL,
      itemkey         varchar(32) NOT NULL,
      itemvalue       varchar(32) NOT NULL,
      CONSTRAINT ct_primarykey PRIMARY KEY(itemid,itemkey)
  )
  </code></pre>
  
  <p>Then for example the following rows could exist:</p>
  
  <pre><code> itemid            itemkey        itemvalue    
   ----------------  -------------  ------------ 
   123               Colour         Red            
   123               Size           Medium             
   123               Fabric         Cotton
  </code></pre>
  
  <p>The trouble with this scheme is the SQL syntax required to extract data is quite complex.
  Would it be better to just create a series of key/value columns?</p>
  
  <pre><code>CREATE TABLE key_value_pairs ( 
      itemid            varchar(32) NOT NULL,
      itemkey1        varchar(32) NOT NULL,
      itemvalue1      varchar(32) NOT NULL,
      itemkey2        varchar(32) NOT NULL,
      itemvalue2      varchar(32) NOT NULL,
   . . .etc . . .
  )
  </code></pre>
  
  <p>This will be easier and faster to query but lacks the extensibility of the first approach.
  Any advice?</p>