List:General Discussion« Previous MessageNext Message »
From:Jason Trebilcock Date:May 31 2013 10:28pm
Subject:RE: Audit Table storage for Primary Key(s)
View as plain text  
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.tompkins@stripped] 
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.tompkins@stripped>
Date: Thu, May 30, 2013 at 8:27 AM
Subject: Audit Table storage for Primary Key(s)
To: "[MySQL]" <mysql@stripped>


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

Thread
Audit Table storage for Primary Key(s)Neil Tompkins30 May
  • Fwd: Audit Table storage for Primary Key(s)Neil Tompkins31 May
    • RE: Audit Table storage for Primary Key(s)Jason Trebilcock31 May
  • Re: Audit Table storage for Primary Key(s)Carsten Pedersen31 May
    • Re: Audit Table storage for Primary Key(s)Neil Tompkins31 May
      • Re: Audit Table storage for Primary Key(s)Carsten Pedersen31 May
        • Re: Audit Table storage for Primary Key(s)Neil Tompkins31 May
          • Re: Audit Table storage for Primary Key(s)Carsten Pedersen31 May
            • Re: Audit Table storage for Primary Key(s)Andrew Moore31 May
            • RE: Audit Table storage for Primary Key(s)Rick James31 May
              • Re: Audit Table storage for Primary Key(s)Andrew Moore31 May