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. -----Original Message----- From: Neil Tompkins [mailto:neil.tompk...@googlemail.com] Sent: Friday, May 31, 2013 3:43 AM To: [MySQL] Subject: Fwd: Audit Table storage for Primary Key(s) Any advice anyone ? ---------- Forwarded message ---------- From: Neil Tompkins <neil.tompk...@googlemail.com> Date: Thu, May 30, 2013 at 8:27 AM Subject: Audit Table storage for Primary Key(s) To: "[MySQL]" <mysql@lists.mysql.com> Hi, 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 1 1|2013-05-29 2|2013-05-29 2 3 1|2|2 etc 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 Thanks Neil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
