title: How to implement temporal data in MySQL
body: <p>I currently have a non-temporal MySQL DB and need to change it to a temporal MySQL DB. In other words, I need to be able to retain a history of changes that have been made to a record over time for reporting purposes.</p>
  
  <p>My first thought for implementing this was to simply do inserts into the tables instead of updates, and when I need to select the data, simply doing a <code>GROUP BY</code> on some column and ordering by the timestamp <code>DESC</code>.</p>
  
  <p>However, after thinking about things a bit, I realized that that will really mess things up because the primary key for each insert (which would really just be simulating a number of updates on a single record) will be different and thus mess up any linkage that uses the primary key to link to other records in the DB.</p>
  
  <p>As such, my next thought was to continue updating the main tables in the DB, but also create a new insert into an "audit table" that is simply a copy of the full record after the update, and then when I needed to report on temporal data, I could use the audit table for querying purposes.</p>
  
  <p>Can someone please give me some guidance or links on how to properly do this?<br>
  Thank you.</p>