We have two table structures in Oracle for keeping track of history, 1) current record and effective date 2) effective date and date ended

When we are constructing the DB logic we shall use these definitions:

1) there can only be ONE current record - either the current record flag is set to 'Y' or the date ended is NULL

2) if we are inserting a record - check if there is a current the record, if there is - update the current record flag to 'N' or set the date ended (not NULL!)

3) If we are updating the current record then we should put an end date on the current record or set the current record flag to 'N' and insert a new record with and date ended = NULL

4) Deleting a record may not apply to a subsystem. This is the generall rule though. If we are "deleting" the current record then we should put an end date on the current record or set the current record flag to 'N'. If deleting the "non current" record then do an actual delete on that record.

-- Anonymous, July 08, 1998

Answers

The first record structure stores an effective date and a "current record" flag. CURRRENT_RECORD is "Y" for the record with the latest date. DATE_EFFECTIVE does not allow NULL. There is always one record flagged as the current record. However, the stored procedure will maintain the CURRENT_RECORD flag. Therefore, unless you are part of the DB team, you will never update the CURRENT_RECORD flag.

Don't bother coding logic-error-handling code that checks for disparities between which record is flagged as the current record and which record has the latest effective date. The logic that maintains the flag will be coded and tested on the server, and from the perspective of the OCI application is guaranteed to be correct.

-- Anonymous, July 13, 1998