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
321, Mary Smith, 123-232-3121, 3/1/2019 123
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
123, 1/31/2019,5000 123
123, 2/28/2019,2000 123
123, 3/31/2019, 500 321
321, 3/31/2019,2008 123
123, 4/30/2019, 5009 321
321, 4/30/2019,2002 123
123, 5/31/2019, 5095 321
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.