Include history in logical model or physical design

From: OlegJay <olegjay_at_yahoo.com>
Date: 7 Mar 2003 08:04:39 -0800
Message-ID: <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 Received on Fri Mar 07 2003 - 17:04:39 CET

Original text of this message