title: Database performance: Using one entity/table with the max. possible properties or split to different entities/tables?
body: <p>im need to design some database tables but im not sure about the performance impact. In my case its more about the read performance than for saving the data.</p>
  
  <p><strong>The situation</strong></p>
  
  <p>With the help of pattern recognition im finding out how many values of a certain object needs to be saved in my postgresql database.
  Amount other lets say fixed properties the only difference is if 1, 2 or 3 values of the same type needs to be saved.</p>
  
  <p>Currently im having 3 entities/tables which differ only in having having 1, 2 or 3 not nullable properties of the same type.</p>
  
  <p>For example:</p>
  
  <pre><code>EntityTestOne/TableOne {
      ... other (same) properties
      String optionOne;
  }
  
  EntityTestTwo/TableTwo {
      ... other (same) properties
      String optionOne;
      String optionTwo;
  
  }
  
  EntityTestThree/TableThree {
      ... other (same) properties
      String optionOne;
      String optionTwo;
      String optionThree;
  }
  </code></pre>
  
  <p>I expect to have several million records in production and im thinking what could be the performance impact of this variant and what could be alternatives.</p>
  
  <p><strong>Alternatives</strong></p>
  
  <p>Other options which come into my mind:</p>
  
  <ul>
  <li>Use only one entity class or table with 3 options (optionTwo and optionThree will be nullable then). If to talk of millions of expected records
  plus caching im asking myself isn't it a kind of 'waste' to save millions of null values in at least two (caching) layers (database itself and hibernate). In a another answer i read yesterday saving a null value in postgresql need only 1 bit what i think isnt that much if we talk about several millions of records which can contain some nullable properties (<a href="https://stackoverflow.com/questions/12145772/do-nullable-columns-occupy-additional-space-in-postgresql">link</a>).</li>
  <li>Create another entity/table and use a collection (list or set) relationship instead</li>
  </ul>
  
  <p>For example:</p>
  
  <pre><code>EntityOption {
      String value;
  }
  
  EntityTest {
      ... other (same) properties
      List&lt;EntityOption&gt; options;
  }
  </code></pre>
  
  <ul>
  <li>If to use this relationship: What would give a better performance in case of creating new records:
  Creating for every new EntityTest new EntityOption's or doing a
  lookup before and reference a existing EntityOption if exists? What about the read performance while fetching them later and the joins which will be needed then?
  Compared to the variant with one plain Entity with three options i can imagine it could be slightly slower...</li>
  </ul>
  
  <p>As im not that strong in database design and working with hibernate im interested of the pros and cons of these approaches and if there are even more alternatives.
  I even would like to ask the question if postgresql is the right choice for this or if should think about using another (free) database.</p>
  
  <p>Thanks!</p>