title: Database design for audit logging
body: <p>Every time I need to design a new database I spend quite some time
  thinking on how I should set up the database schema to keep an audit log of
  the changes.</p>
  
  <p>Some questions have already been asked here about this, but I don't agree that
  there is a single best approach for all scenarios:</p>
  
  <ul>
  <li><a href="https://stackoverflow.com/questions/39281/database-design-for-revisions">Database Design For Revisions</a></li>
  <li><a href="https://stackoverflow.com/questions/201527/best-design-for-a-changelog-auditing-database-table">Best design for a change log auditing database table</a></li>
  <li><a href="https://stackoverflow.com/questions/1051449/ideas-on-database-design-for-capturing-audit-trails">Ideas on database design for capturing audit trails</a></li>
  </ul>
  
  <p>I have also stumbled upon this <a href="https://web.archive.org/web/20210506085627/http://www.4guysfromrolla.com/webtech/041807-1.shtml" rel="noreferrer">interesting article on Maintaining a Log of Database Changes</a> that tries to list the pro and cons of each approach. It's very well written and has interesting information, but it has made my decisions even harder.</p>
  
  <p><strong>My question is:</strong> Is there a reference that I can use, maybe a book or something like a decision tree that I can refer to decide which way should I go based on some 
  input variables, like:</p>
  
  <ul>
  <li>The maturity of the database schema</li>
  <li>How the logs will be queried</li>
  <li>The probability that it will be need to recreate records</li>
  <li>What's more important: write or read performance</li>
  <li>Nature of the values that are being logged (string, numbers, blobs)</li>
  <li>Storage space available</li>
  </ul>
  
  <p>The approaches that I know are:</p>
  
  <p><strong>1. Add columns for created and modified date and user</strong></p>
  
  <p>Table example:</p>
  
  <ul>
  <li>id</li>
  <li>value_1</li>
  <li>value_2</li>
  <li>value_3</li>
  <li>created_date</li>
  <li>modified_date</li>
  <li>created_by</li>
  <li>modified_by</li>
  </ul>
  
  <p>Major cons: We lose the history of the modifications. Can't rollback after commit.</p>
  
  <p><strong>2. Insert only tables</strong></p>
  
  <p><a href="https://stackoverflow.com/questions/1051449/ideas-on-database-design-for-capturing-audit-trails/1051494#1051494">Table example</a>:</p>
  
  <ul>
  <li>id</li>
  <li>value_1</li>
  <li>value_2</li>
  <li>value_3</li>
  <li>from</li>
  <li>to</li>
  <li>deleted (Boolean)</li>
  <li>user</li>
  </ul>
  
  <p>Major cons: How to keep foreign keys up to date? Huge space needed</p>
  
  <p><strong>3. Create a Separate history table for each table</strong></p>
  
  <p>History table example:</p>
  
  <ul>
  <li>id</li>
  <li>value_1</li>
  <li>value_2</li>
  <li>value_3</li>
  <li>value_4</li>
  <li>user</li>
  <li>deleted (Boolean)</li>
  <li>timestamp</li>
  </ul>
  
  <p>Major cons: Needs to duplicate all audited tables. If the schema changes it will be needed to the migrate all the logs too.</p>
  
  <p><strong>4. Create a Consolidated history Table for All Tables</strong></p>
  
  <p>History table example:</p>
  
  <ul>
  <li>table_name</li>
  <li>field</li>
  <li>user</li>
  <li>new_value</li>
  <li>deleted (Boolean)</li>
  <li>timestamp</li>
  </ul>
  
  <p>Major cons: Will I be able to recreate the records (rollback) if needed easily? The new_value column needs to be a huge string so it can support all different column types.</p>