Re: Include history in logical model or physical design

From: Henry Craven <>
Date: Sat, 08 Mar 2003 21:13:22 GMT
Message-ID: <S1taa.1422$>

Add a table and update it as part of a Transaction on each change.


ObjID              -    AutoIncrement (PK)
Timestamp        -   Date/Time
Table_Name     -   Text
Column_Name  -   Text
Record_PK      -    Text (Unique Record Identifier)
Old_Value        -    Text  (Can be Cast to Column Datatype as Req'd)
Action_Type     -    Text  (Change, Delete)
Actioned_By     -    Text  (User/Process Ident that initiated the change)

That's at it's most basic, and you can elaborate from there as needed.

Henry Craven.

"OlegJay" <> wrote in message
> I need to build a 3rd normal form logical model that has more nuances
> than I've handled in the past.
> In particular, how do I include into my logical model the saving of
> records that are updated in each evening load? Users will want to know
> what was in the database at any prior time.
> Does anyone know of any simple history maintaining logical models
> described on the internet where I can easily get to? Or is history
> records and retrieval purely a physical design consideration?
> As a simple example consider the two entity CUSTOMER - ORDER logical
> model where there is one 1 to many relationship and the attributes may
> be:
> cust id
> cust name
> order id
> bill amount
> product ordered
> date ordered
> What if at a later date some attribute changed and the database is
> supposed to keep both the original and the update; like cust named
> changed or bill amount changed. Should I add a create-modify-date
> field or a sequence-number to each entity in the logical model to
> handle this updateability feature of the entity(s) or should I not be
> concerned about this capability or history for a deliverable 3rd
> normal form logical model for industry.
> I thank you for any help in advance,
> Oleg Jay

Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (
Version: 6.0.459 / Virus Database: 258 - Release Date: 25-Feb-2003
Received on Sat Mar 08 2003 - 22:13:22 CET

Original text of this message