title: How to best handle the storage of historical data?
body: <p>I'm trying to determine how I should store historical transactional data.</p>
  
  <p>Should I store it in a single table where the record just gets reinserted with a new timestamp each time?</p>
  
  <p>Should I break out the historical data into a separate 'history' table and only keep current data in the 'active' table.</p>
  
  <p>If so, how do I best do that?  With a trigger that automatically copies the data to the history table?  Or with logic in my application?</p>
  
  <p><strong>Update per Welbog's comment:</strong></p>
  
  <p>There will be large amounts of historical data (hundreds of thousands of rows - eventually potentially millions) </p>
  
  <p>Primarily searches and reporting operations will be run on the historical data.</p>
  
  <p>Performance is a concern.  The searches shouldn't have to run all night to produce results.</p>