🚀 go-pugleaf

Foo Say How (sayhow@solomon.technet.sg) wrote:
: I am maintain the database of an equipment, including details of its 
: various parts, and colour  ....
: Through the life span of the equipment, the parts are changed, colour 
: replaced.

: What is the best way to keep track of the changes?
: This is required so that questions such as these can be answered ..

: 1. Which equipments have undergone a colour change from RED TO BLUE?
: 2. How many equipments have had part A replaced within the last 5 years?
:    etc etc ..
: 3. What is amount spend for replacing part for a particular equipment?

: In mosty situation, there are two ways to handle the equipment details.

: If history of changes are of no consequence,
: One way is to handle keep all the information in one single file and 
: changes are made directly on to it.  (A parallel case would be Customer 
: details ...and details are keyed directly into it) But then how to track 
: the changes in this case?

I think you will have to make up your mind: do you want to track the 
history of changes or don't you?  If you read the paragraph above, you 
are saying both.

: The otherway is to have an assembly type of database in which the 
: compnent are kept .. (how then to keep information on non-component such 
: as Colour).  In this case History can be created vai a transaction file 
: to report the changes.

: Have any one here face similar problem and how you resolve this?

: Please note that I am not talking about audit trail here, but history file.

What is the difference between an audit trail and a history file?  To me, 
they are synonymous (maybe someone else can enlighten me).

Generally, the two methods of tracking changes that I'm familiar with are 
(1) maintain _all_ records in the same or a different table; when a 
change is made, _keep_ the old record and add the changed record with a 
transaction date; this is often referred to as a "version" file; it may 
be useful if the rate of changes is not overly high and the record size 
is not extremely large; with this method, you can perform queries that 
yield the state of records "as of" any desired date, as well as 
reconstruct changes that occurred during a stated period, although that 
is somewhat awkward, involving field comparisons to determine what did 
and did not change when a record was replaced.
(2) handle _all_ changes as transactions, recording the record, field, 
previous and new contents, and date of transaction; use the transaction 
record to perform the update, then save the transaction, itself; this 
makes it easier to see what _changes_ occurred, but a little more awkward 
to derive the state of the entire table "as of" any date.

Which approach you use depends on many factors.  You should not expect a 
complete analysis of your individual circumstances remotely.  You may 
want to consider engaging a professional database consultant.
-- 

+-----------------------------------------------------------------------------+
| Don Ravey           dravey@netcom.com               OBJECTS IN MIRROR       | 
| W6DBZ             San Mateo, California        ARE CLOSER THAN THEY APPEAR  |
+-----------------------------------------------------------------------------+