The only reason I'd go with approach #2 would be if there could be multiple rows per ID. Otherwise I'd have no heartburn with adding a few more columns to the main table.
Karen In a message dated 7/12/2012 1:50:53 PM Central Daylight Time, [email protected] writes: > 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? > > >
