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.