Re: Include history in logical model or physical design

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

Audit_Trail

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" <olegjay_at_yahoo.com> wrote in message news:47d21956.0303070804.baa2a59_at_posting.google.com...
> 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:
>
> CUSTOMER
> cust id
> cust name
>
> ORDER
> 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 (http://www.grisoft.com).
Version: 6.0.459 / Virus Database: 258 - Release Date: 25-Feb-2003