I'll ask the dumb question.
Why not create individual history tables corresponding to your 'main'
tables? So, if you have an 'address' table, then the original record could
be written to an 'address_his' table via an update or delete trigger
(depending on whether you allow deletions or not) when a change is
made...and the updated address record would be in the 'address' table. The
address_his table would really only need two additional fields to track your
data - a user field and a journal date/time.
Not sure how you're planning on writing to the changes to your audit table,
but this would allow the database to do the work instead of having to write
application code to do it.
Note: This is based on how I see things for the current application where I
work. Doesn't mean that it's right or wrong...it just works for us.
From: Neil Tompkins [mailto:neil.tompkins@stripped]
Sent: Friday, May 31, 2013 3:43 AM
Subject: Fwd: Audit Table storage for Primary Key(s)
Any advice anyone ?
---------- Forwarded message ----------
From: Neil Tompkins <neil.tompkins@stripped>
Date: Thu, May 30, 2013 at 8:27 AM
Subject: Audit Table storage for Primary Key(s)
To: "[MySQL]" <mysql@stripped>
I've created a Audit table which tracks any changed fields for multiple
tables. In my Audit table I'm using a UUID for the primary key. However I
need to have a reference back to the primary key(s) of the table audited.
At the moment I've a VARCHAR field which stores primary keys like
Is this the best approach, or should I have a individual field in the audit
table for all primary keys. At the moment I think the max number of primary
keys on any given table is 3