title: Design Record update approval
body: <p><strong>Problem statement:</strong><br>
  To always approve record updates before the changes reflect in main record. Want to try solving this problem using SQL database. <br>
  Eg. 
  User: { "name" : "Ravi Kumar", "city" : "Bangalore" }
  Say we want to update the city to "Delhi" but it has to undergo approval before it reflects in the main record.</p>
  
  <p>Once approved. It should show:
  User: { "name" : "Ravi Kumar", "city" : "Delhi" }</p>
  
  <p><strong>Required Features:</strong></p>
  
  <ol>
  <li>Changes must get approved before they reflect in the main record</li>
  <li>audit trail of the record to be maintained</li>
  <li>how to support table joins? what if record is fetched using joining multiple tables? Basically if db is normalised.</li>
  </ol>
  
  <p><strong>Possible Solutions:</strong></p>
  
  <ol>
  <li><p>Have a additional column "approved" in the table. All approved records will have status 1 and rest will be 0. To get the current record we have to get record with the most recent timestamp with approved=1.</p></li>
  <li><p>Have 2 tables one which contains the main table and another for approvals. When someone approves then we make changes to the record in main table.</p></li>
  </ol>
  
  <p><strong>Questions:</strong></p>
  
  <ol>
  <li>How to incorporate joins when there is normalisation? Is it simple or complex when joins are must? Is it even possible?</li>
  <li>In case of joins, can we still implement using ORMs like hibernate? </li>
  <li>If there are multiple updates waiting for approval on the same record and each update modifies different set of fields. If all of the records are approved then the record in consideration might only have the last update changes(Assuming one of above solutions are used).<br><br>
  Eg. There is a record in the main table and no unapproved records for it. Now a user changes a property say "name" and submits for approval. Another user changes property "city" and submits for approval. Another user changes property "salary" and submits for approval. All unapproved records are approved. Now last update(which changed salary property), that change contains old name not the one which is in first update. How to get all the approved changes.<br>
  This can be achieved by storing only changed properties instead of the whole. But it comes with cost of more code changes.</li>
  </ol>
  
  <p>How are these problems tackled in the industry?</p>
  
  <p>Related questions:<br>
  <a href="https://stackoverflow.com/q/103766/5039001">What&#39;s the best way to store changes to database records that require approval before being visible?</a><br>
  <a href="https://stackoverflow.com/q/51302006/5039001">Structure to handle changes to records that require approval</a></p>