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.
