Rate histories or cleanly storing history

The HIST_NAV_IND column:

When you want a history of values (such as ratings) in the main table for some business requirement, add this column and use the following values:

S => When you have only 1 record

F => The first record when you have more than 1 record

P => The current primary record when you have more than 1

M => The previous P records that have been surpassed.

The EFF_BEGIN_DT and EFF_END_DT columns:

In case you might need to do reprocessing of old records you will want an easy way to figure out which rate history that you would want to use; EFF_BEGIN_DT and EFF_END_DT make that simple.

EFF_BEGIN_DT is always set in every record (generally it should match the create date but there are business reasons why you want it separate)

EFF_END_DT should be NULL for the current primary record (unless you are organized enough to always know the future rate change date in advance [unlikely]) and should always be set for the M and F records to the day [or hour, minute or second] prior to the EFF_BEGIN_DT of the new P record. The EFF_END_DT of one record should never overlap with the EFF_BEGIN_DT of the next and you can use TRUNC("TimeStamp", DATE) to ensure that your select driver will always either get 1 [normally] or zero [They shouldn't have been included] records.