I need to setup a history feature on a project to keep track of prior changes.
Let's say I have two tables right now:
NOTES TABLE (id, userid, submissionid, message)
SUBMISSIONS TABLE (id, name, userid, filepath)
Example: I have a row in notes and the user wants to change the message. I want to keep track of it's state before the change and after the change.
What would be the best approach to setting up a column in each of these tables which will say if an item is "old." 0 if active OR 1 if deleted/invisible.
I also want to create a history (
AUDIT TRAIL) table which holds the
id of the prior state, the
id of the new state, which table these id's relate to?