How to Keep Audit Data and Effectively Query Large Tables Thanks for reading this.  I have two types of data - demographic data and transactional data.  The demographic data is only being stored when it changes. I’m trying to keep an audit trail for the demographic data at the same time. The transactional data is being aggregated.  Both tables are keyed on Account Number and Date.   A greatly simplified example might make it clearer:

Acct#, Name, Phone#, EffectiveDt

123, John Smith, 123-132-1232, 1/1/2019

321, Mary Smith, 123-232-3121, 3/1/2019

123, John Smith, 432-123-4322, 5/1/2019. — two records for John Smith, the second containing the most recent data

Acct#, MonthEnding, Sales

123, 1/31/2019,5000

123, 2/28/2019,2000

123, 3/31/2019, 500

321, 3/31/2019,2008

123, 4/30/2019, 5009

321, 4/30/2019,2002

123, 5/31/2019, 5095

321, 5/31/2019,2000

So I’m achieving the audit history by keeping two records for John Smith in the demographics file but the effective date will be different than the effective date in my transaction file.   

I am concerned about the design because I would have to select for max(effectivedt) and account number as part of getting both Demographic and transactional data.   The demographics file is really a set of five tables and with hundreds of thousands of records.    I’m concerned that my queries down the road might be too slow with this approach but there are a lot of aspects to this strategy that really appeal to me.  The primary one being that I want to avoid a separate set of audit tables to manage.   

I did see this question but I wanted to check with others about my specific situation as I’m trying to avoid a separate set of tables if possible and I had hoped that others might have ideas or experience with this problem:  Data Change Audit Plan

Any thoughts or input would be greatly appreciated.
Add a column "current" to your existing table make it true or false.  If current it is true.


Additionally


Consider adding a second date column "End effective", when John Smith has multiple old records, you are going to want to know the date span each was correct for.