What's the best practice approach to storing historical records?
I have an entity whose rows will constantly get updated. I need to keep track of each row's history. There will be about 40K "current" records at any given time and each of them will be updated on a daily basis for about 60 days, then the updates will stop.
I see two options here:
All current and historical records in one table – since each row will have an id that will allow me to identify which records are part of the same group.
Current and historical records in separate tables – each time the current record is updated, a copy of it pre-update is put into the history table. The history table would have a FK.
Is there another option I haven't considered? Which option is the more appropriate approach to what I am trying to do?