title: What's a good database design for hierarchical data versioning with Hibernate?
body: <p>I need to store a history of changes for hierarchical data. The data is a one-to-many relation: a setting entity has many properties. Here are my tables:</p>
  
  <pre><code>Setting
  ------------------------------
  Id            INTEGER NOT NULL
  CompanyId     INTEGER NOT NULL
  Name          TEXT NOT NULL
  
  
  SettingProperty
  ------------------------------
  SettingId            INTEGER NOT NULL
  PropertyName         TEXT NOT NULL
  </code></pre>
  
  <p>I'm using Hibernate as the ORM, the corresponding Java entity would look somewhat like this</p>
  
  <pre><code>public class Setting {
  
      private int id;
      private int companyId;
      private String name;
  
      private Set&lt;String&gt; properties;
      // ....
  }
  </code></pre>
  
  <p>Now, whenever the setting name changes or a property is added to the setting, I need to store the full history of changes. I also need to easily query the historical setting for a given point in time. I've taken into account multiple different options, i.e.:</p>
  
  <ol>
  <li>adding into the 'Setting' table a 'ChangeDate' column (tells us when the given setting version was modified) and a 'CommonId' column (tells us which entities form the history of a single setting in time - i.e. in case when the name changes the new setting version has a new name and a new id so we need to way to tell that this is just a new version of the same setting). This is simple and should work but makes querying for most current data hard (i.e. SELECT * FROM Setting WHERE ChangeDate = (SELECT MAX(ChangeDate ...))</li>
  <li>having a table with just an Id, a BLOB (for keeping the whole serialized Java Setting object) and a ChangeDate. This way I'm fine with just one table, but I'm in big trouble when I want to add/remove something into the Setting object because I need to update the already saved objects.</li>
  <li>having for each of the above tables a separate History table (with same columns and a ChangeDate) - this makes it easy to query for current data (just use the regular tables) but maintaining a few tables with almost identical schema is some minor pain.</li>
  </ol>
  
  <p>Any suggestions or better ideas? Please keep in mind that I'm using Hibernate so not all SQL query quirks are possible. My DB is PostgreSQL.</p>