On 3/31/10 7:51 AM, Justin Giboney wrote: > Is there a design pattern for handling updates to information? > > I am designing a application that needs to know when things changed > and who changed them. Is the best way to do this to create a > transaction table for each table in the database that records the new > update every time something changes
Justin, There are a couple ways that I have solved this problem, both had very different requirements. Here are some ways to deal with it: As wade suggested you can have certain fields. For example, we have put the following: 1. added the following columns: created_date, created_by, modified_date and modified_by 2. We have also added version on the table. This would be modified_date, modified_by, and version. Version incrementing on each update on the record. Version 0 being the created by and created date. 3. I have also stored the version information in another table. So instead of leaving all the versions in one table we had a trigger that would do an insert on insert or update. This would copy the data to a history table. We would use the fields defined in #2. But all the versions previous would be stored in the history table. We then would have then history table stored differently. By this, I mean that we used postgresql and partitions and table spaces to handle the location of the history tables. This would allow us to have a history of all transactions that happened on the table. Hope that helps out! -- thebigdog _______________________________________________ UPHPU mailing list [email protected] http://uphpu.org/mailman/listinfo/uphpu IRC: #uphpu on irc.freenode.net
