title: What is the best way to model an entity that can have different attributes?
body: <p>Let's say I am programming a MMORPG. I modeled a Entity <code>character</code> that can have a multitude of attributes like <code>coating</code>, <code>strength</code>, <code>color</code> and so on. Because I do not know these attributes in advance (what and how many of them), I thought I create an extra table for it, like so:</p>
  
  <pre><code>CREATE TABLE character (INTEGER id, VARCHAR name, INTEGER player_id);
  </code></pre>
  
  <p>and</p>
  
  <pre><code>CREATE TABLE attributes (INTEGER character_id, VARCHAR key, VARCHAR value);
  </code></pre>
  
  <p>I would then be able to introduce a multitude of new attributes. However, how would I query  this construct?
  The query</p>
  
  <pre><code>SELECT * FROM character JOIN attributes ON character.id=attributes.character_id;
  </code></pre>
  
  <p>will obvioulsy only work for a single attribute. Do I have to JOIN the <code>attributes</code> table more than once or is there another solution?</p>
  
  <p>Is there a way to have different types for the <code>attribute.value</code> Part? Doing it the way I am doing now would limit me to a <code>VARCHAR</code> representation.</p>