Re: MySQL change-tracking

Richard Lynch wrote:

On Wed, May 2, 2007 4:32 pm, Brad Fuller wrote:

Richard Lynch wrote:

I have this simple database and I'm going to have a handful of people editing it... I'd like to track each and every edit with username, and, ideally, provide myself an easy "Undo" if I decide [bleep] is an idiot and shouldn't have done that. Now, I'm not real concerned about the relational foreign key aspect here, and I'd like to keep this as simple as possible... I've considered doing a dump and putting the output into subversion, even... I realize there may be some nifty MySQL tool that does this, so I'll be researching that shortly, but I'm wondering if there's a nifty change-management php package out there that I should check out. The users are currently slated to be logging in via HTTP Basic Authentication, but I could change that, I guess. K.I.S.S. is definitely the motto around here -- If it takes more than a day or two to figure out, install, and implement; then forget it, as I can just hack something together myself in that time-frame. -- Some people have a "gift" link here. Know what I want? I want you to buy a CD from some indie artist. http://cdbaby.com/browse/from/lynch Yeah, I get a buck. So?

I'm not aware of any existing package that offers this, and if there isn't one I wouldn't be surprised. I think that there are just too many variables for it to be universal enough... But, I thought about it for a second and here's what I came up with... I realize this is not a very efficient way to store data, but it's just an idea... What about instead of running UPDATE queries, you INSERT a new record "on top of it", and if you need to "undo" someone's change, just DELETE that record. I'm not sure how the tables would relate, but it would be something like... initial insert generates customerID -> customerID and customer data go into a separate table -> subsequent changes get stacked up in this table and the most recent record gets displayed when you click to view the customer. Voila, you have a history table and a simple way to "undo" changes. Whaddya think?

I worked on a HIPPA-compliant medicasl system like that once (HIPPA basically don't let you ever ever ever "delete" any data about a patient). It had a "version" field that got incremented on each insert and you never did an update or delete. It annoyed the bleep out of me at the time, mainly because the tables got really large really fast... I'll have to think about this and see if the tables I care about will grow ridiculously large, and I can always archive the older stuff after time...

Have a second table that has the 'history' of the record. Basically a copy of the first table. Easy to keep the history: insert into history_table select * from main_table where id='X' then update the main table's record. That way you're not making the main table really large but you'll be able to go back a version if you needed to. You could even keep the history table under check (only keep 50 records). -- Postgresql & php tutorials http://www.designmagick.com/