title: Dynamic Database/Key - Value/Entity - Key Value Dillemma
body: <p>I have been programming relational database for many years, but now have come across an unusual and tricky problem:</p>
  
  <p>I am building an application that needs to have very quick and easily defined entities (by the user).  Instances of these entities could then be created, updated, deleted etc.</p>
  
  <p>There are two options I can think of.</p>
  
  <p><strong>Option 1 - Dynamically created tables</strong></p>
  
  <p>The first option is to write an engine to dynamically generate the tables, and insert the data into these.  However, this would become very tricky, as every query would also need to be dynamic, or at least dynamically created stored procedures etc.</p>
  
  <p><strong>Option 2 - Entity - Key - Value Pattern</strong></p>
  
  <p>This is the only realistic option I can think of, where I have 5 table structure:</p>
  
  <p><strong>EntityTypes</strong> </p>
  
  <p>EntityTypeID    int</p>
  
  <p>EntityTypeName  nvarchar(50)</p>
  
  <p><strong>Entities</strong>    </p>
  
  <p>EntityID    int</p>
  
  <p>EntityTypeID    int</p>
  
  <p><strong>FieldTypes</strong>  </p>
  
  <p>FieldTypeID int</p>
  
  <p>FieldTypeName   nvarchar(50)</p>
  
  <p>SQLtype int</p>
  
  <p><strong>FieldValues</strong> </p>
  
  <p>EntityID    int</p>
  
  <p>FIeldID int</p>
  
  <p>Value   nvarchar(MAX)</p>
  
  <p><strong>Fields</strong></p>
  
  <p>FieldID int</p>
  
  <p>FieldName   nvarchar(50)</p>
  
  <p>FieldTypeID int</p>
  
  <p>The "FieldValues" table would work a little like a datawarehouse fact table, and all my inserts/updates would work by filling a "Key/Value" table valued parameter and passing this to a SPROC (to avoid multiple inserts/updates).</p>
  
  <p>All the tables would be heavily indexed, and I would end up doing many self joins to obtain the data.</p>
  
  <p>I have read a lot about how bad Key/Value databases are, but for this problem it still seems to be the best.  </p>
  
  <p><strong>Now my questions!</strong></p>
  
  <ul>
  <li>Can anyone suggest another approach or pattern other than these two options?</li>
  <li>Would option two be feasible for medium sized datasets (1 million rows max)?</li>
  <li>Are there further optimizations for option 2 I could use?</li>
  </ul>
  
  <p>Any direction and advice much appreciated!</p>