On Wed, Mar 31, 2010 at 10:05 AM, thebigdog <[email protected]> wrote:
> 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 > > ________________________ > > On this note, I'll add a hybrid called #42: Add validstartdate, validenddate, createdby and modifiedby were your validstartdate and validenddate on an active, single record are the creation date and the end of time. Any time you modify the column, a new record is created and the prior is updated,IE ID | NAME | EMAIL | VALIDSTARTDATE | VALIDENDDATE | CREATEDBY | MODIFIEDBY 01 | WILLI | [email protected] | 03-31-2010 00:00:00 | 03-31-2999 00:00:00 | 1 | 1 When a record is modified, it becomes: ID | NAME | EMAIL | VALIDSTARTDATE | VALIDENDDATE | CREATEDBY | MODIFIEDBY 01 | WILLI | [email protected] | 03-31-2010 00:00:00 | 03-31-2010 10:12:21 | 1 | 2 01 | WILLI | [email protected]| 03-31-2010 10:12:22 | 03-31-2999 00:00:00 | 2 | 2 * Notice the PK stays at 1, you can keep it at 1, or change it and use a different key to maintain a relationship between the records. This is to store all historical information on changes, and to have a single valid record at any one time. This concept is called Valid Time Relation, something from the Tenfold days. You can enhance it even more, which Beau Scott did a few years back, so the only data stored per row is the data that changed, IE: ID | NAME | EMAIL | VALIDSTARTDATE | VALIDENDDATE | CREATEDBY | MODIFIEDBY 01 | WILLI | [email protected] | 03-31-2010 00:00:00 | 03-31-2010 10:12:21 | 1 | 2 01 | | [email protected]| 03-31-2010 10:12:22 | 03-31-2999 00:00:00 | 2 | 2 this saves on DB storage, but requires your DAL (Data Access Layer) to handle congregating all current data to a single record. -- Take care, William Attwood Idea Extraordinaire [email protected] _______________________________________________ UPHPU mailing list [email protected] http://uphpu.org/mailman/listinfo/uphpu IRC: #uphpu on irc.freenode.net
