title: Database design with dynamic fields: single table vs many tables - many indexes
body: <p>I have to choose the structure of a database that will store content types (eg. Blog articles, Pages, Documents, Invoices, Estimates, etc..) with dynamic fields: for example, the <code>Estimate</code> content type should have the fields <code>title</code>, <code>date</code> and <code>total price</code>.</p>
  
  <p>However in the time those fields can be added ore removed, so after 1 year the <code>Estimate</code> contant type can have the <code>notes</code> field.</p>
  
  <p>This is a common task provided by famous CMS (drupal for example), but I wonder what is the best approach to have best performance and flexibility: Drupal for example use to have one table with <code>basic</code> fields (e.g. <code>title</code>), and all the secondary fields are stored in sub-tables created on-the-fly and linked to the main one with foreign keys:</p>
  
  <pre><code>table node
  | id | title         | ...
  |  1 | First example |
  table fields_node_total_price
  | id | node_id | value  |
  |  1 | 1       | 123.45 |
  table fields_node_date
  | id | node_id | value    |
  |  1 | 1       | 12345677 |
  </code></pre>
  
  <p>etc..</p>
  
  <p>My point of view is that this approach is very flexible but easly fall into performance issue: in order to get all fields for a document, you must join the tables many times, and the code itself have to iterate many times to build the query (but this shouldnt be a problem).</p>
  
  <p>Btw multi-table is the most-used approach.. so must have many cons.</p>
  
  <p>Im thinking in what kind of disvantages will using a single table have:</p>
  
  <pre><code>| id | title | total_price | date | ec...
  </code></pre>
  
  <p>I did some tests with 5 and 50 additional fields; the performance between the single table approach and the multi table approach are enourmous: single table is about 50x time faster.</p>
  
  <p>Every time a field is added, a column is added to the table.. what kind of problems will this approach rise?</p>
  
  <h2>EDIT</h2>
  
  <p>Let me provide few details:</p>
  
  <ol>
  <li>The application is still in design phase, is a complete redesign of an old application where the fields numbers were static</li>
  <li>We did few tests simulating a object to store, both with single table approach and multi table approach (using 50 fields), results are:</li>
  </ol>
  
  <p>Time in seconds:</p>
  
  <pre><code>Test                                                            1°          2°          3°          4°          5°          avg
  1000 insert single_table                                        8,5687      8,6832      8,7143      8,7977      8,6906      8,69090137389466
  1000 select single table LIKE '%key%' on char(250) field        1,5539      1,5540      1,5591      1,5602      1,5564      1,556705142
  1000 select single table LIKE '%key%' on char(25) field         0,8848      0,8923      0,8894      0,8919      0,8888      0,889427996
  1000 select single table id = $n                                0,2645      0,2620      0,2645      0,2632      0,2636      0,263564462
  1000 select single table integer field &lt; $j                     0,8627      0,8759      0,8673      0,8713      0,8767      0,870787334
  1000 insert multi_table                                         446,3830    445,2843    440,8151    436,6051    446,0302    443,023531816
  1000 select multi table LIKE '%key%' on char(250) field         1,7048      1,6822      1,6817      1,7041      1,6840      1,691367196
  1000 select multi table LIKE '%key%' on char(25) field          0,9391      0,9365      0,9382      0,9431      0,9408      0,939536426
  1000 select multi table id = $n                                 0,9336      0,9287      0,9349      0,9331      0,9428      0,93460784
  1000 select multi table integer field &lt; $j                      2,3366      2,3260      2,3134      2,3342      2,3228      2,326600456
  </code></pre>