1

I am working on developing an insurance app. We have designed a database table containing all user profile related information, as well as their approvals to different privacy policies.

My question is - which would be a better option to keep track of the history of all changes in the information described above?

Archive table or Audit Log table? or are both needed?

Details

  1. Archive table - a copy of the current table will be created but will contain all the past data. All present data will be in the current table.

  2. Audit Log table - generic table that contain columns that describe which tables and columns from those tables changed and to what values.

Is having one enough? or is it a standard practice to have both an archive table and an audit table to keep track of historical data of user profile related information?

Tried doing some research but all I could find was the archiving of the actual audit log data, not the archiving of the actual data. Makes it seem more confusing.

Xmus Jackson Flaxon Waxon
  • 69
  • 6
  • The point is to have 2 different permanent stores, DB & log. Via the log we can "audit" or restore the DB. The DB doesn't necessarily contain all history, the log does. The non-log is quickly accessible, the log not. There is no "better" in engineering. The 2 serve 2 complementary simultaneous roles. There is no "actual data", there is the DB & the log. This is surely all a SO faq & (the intro to the) log section of any textbook. Re choice of design, again, there is no "best". – philipxy Aug 09 '22 at 06:09
  • Please before considering posting: Pin down code issues via [mre]. Read the manual/reference & google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. Reflect research in posts. SO/SE search is poor & literal & unusual, read the help. Google re googling/searching, including Q&A at [meta] & [meta.se]. [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) [ask] [Help] – philipxy Aug 09 '22 at 06:10

0 Answers0