I need to keep information changes history for people in our application.
e.g. when their address was changed I need to remember who changed it (staff or member), when and to what was changed along with what the original value was. In other words audit trail. I also need to keep track of the current value or values (in the case of languages a person speaks-may be more than one...so also current groups).
I don't know what's the standard approach to this problem. I thought of having another address table, say addresses_history, same structure as usual table, but with some extra fields: type of user and user id who made the change, and time stamp.
Every time a change is made to addresses table also insert the proper record in addresses_history with a trigger. But I need to know information about the user who changed it in database, not only the application, so where to keep it? So I should add other columns to addresses table for this (user id and type of user). Or will an active/inactive flag be sufficient and how then do I approach this in a group (language) situation?
Also do I keep a copy in the history table and only the current or active record (s) in the address table? I was hoping there was some sort of standard out there for this.
Also just for background info, the current database without history implemented is around 250-300 tables with a large part on the demographics of a client. Any suggestions for improving or confirmations that this approach will do the job would be greatly appreciated!