History table

[effigy]

I'm trying to figure out the best way to implement a history tracking table in MySQL. Right now the history table tracks various actions, linking directly to the record. For instance, "Bob put id 1 in table A into an alteration phase." A problem arises when I want to allow a delete action. It's easy enough to log that "Bob deleted id 1 in table A," but the record I'm linking to is gone now; I've lost touch with all of the associated information.

Based on this example, I'm assuming that history tables are where one strays from normalization and begins redundancy. I was frowning on this, especially since the history is often accessed and displayed, but even though the information is redundant, I don't need to use it as long as the link is alive; thus, avoiding performance and integrity issues, while only adding more burden to the storage space.

Am I on the right track here? I hope I was clear.

Thanks.

[fenway]

I've struggled with this counteless times... in my world, even "deleting" doesn't really exist, because clients can change their mind, and I have to be able to bring it back without going to a backup.

To my knowledge, there are 3 schools of thought:

1) Create a "string"-ified version of the record in question (e.g. id1) and store it in a TEXT field in your history table in a parsable fashion.  Ugly, but it works.

2) On delete, copy the record into a "backup" table, and have your tracking script check the "live" table first, then the "backup" table.  This doesn't scale really well (2N tables right off the bat), and requires some juggling, but can be abstracted away from most of the app.

3) Do the copying in the original table, and somehow mark the records "stale".  Less ugly, scales better, but makes the primary table a mish-mash of crap after a while.

They're all equally bad -- but depending on what you're trying to do, I've used all three sucessfully for different applications.

[effigy]

It's comforting to know that this isn't as easy as it looks. I think the 1st approach is best for my situation because the amount of information that I need to keep is minimal--so minimal that I'll probably add a few new columns instead of stringifying. I'm going to go over this a few more times.

Many thanks fenway.

[artacus]

You have to be real  careful about what you delete in a database. Most apps I write just give the appearance of deleting and mark a field inactive instead. For example an employee leaves, if you actually deleted him from the database, your historical records for payroll, timesheets, tax info etc would all be off.

[fenway]

And the worst part about that is you can no longer use a UNIQUE index ever again.