title: history rows management in database
body: <p>As in many databases, i am designing a database that should keep record of previous versions of the rows changed in each table.</p>
  
  <p>The standard solution to this problem is to keep a history table for each data table,
  and whenever a row needs to be updated in the data table, a copy of the current row gets inserted to the history table and than the row in the data table gets updated.</p>
  
  <p>the disadvantages of this solution for me:</p>
  
  <ul>
  <li>maintenance of 2 tables instead of 1, (in case the structure of the table needs change)</li>
  <li>the application needs to know both of the tables instead of one</li>
  <li>names of the tables might need to be short to keep a convention of the table name and the history table name (SOME_TABLE, SOME_TABLE_HIST for example)</li>
  </ul>
  
  <p>I am considering a different solution and would like to know if it's ok.
  for each table, we add the column IS_LAST</p>
  
  <ul>
  <li>when a row gets inserted to the table, it will get inserted with IS_LAST=1.</li>
  <li>when a row gets updated, a copy of the original row will be duplicated to the same table with the change of IS_LAST=0, and the original row will get updated as needed (still keeping IS_LAST=1).</li>
  </ul>
  
  <p>assume that in my case, rows are updated at an average of 10 times.
  also assume that at least 90% of the actions performed by the application happens only on the recent version of the rows. </p>
  
  <p>my database is an Oracle 10g so to keep the "active" table slim, we can split the table to 2 partitions: the IS_LAST=1 partition, and the IS_LAST=0 partition.</p>
  
  <p>Is partitioning a good way of solving the history data keeping problem?</p>
  
  <p>Does this solution limits other partition potential to these tables?</p>
  
  <p>thanks!</p>