All:
I need to track active vs. obsoleted status for a range of primary-key data objects, while keeping them all available for historical purposes. Fortunately, these don't change very often, if at all. I've thought of two approaches:
1. Append four columns to each table:
xactive ('x' prefix indicating binary flag [1|0]
begdate
enddate
flagdate
or:
2. Maintain a record status table:
RECORDSTATUS
-------------------
RecordStatusID <pk>
TableName - one of the tracked tables
RecordID <fk> - Primary-key value to be flagged
ReplacedRecordID <fk> - Value inactivated by RecordID
xActive
BegDate
EndDate
FlagDate
Record status would update infrequently, but would be read during each view creation involving a tracked object (...where recordstatus.xactive = 1).
Approach #2 appeals. But am I trading code-simplicity for performance? Or does the idea of a 'RecordStatus' table have legs?
'Preciate your perspectives.
Bruce
