Personally I use approach 1 for several tables. One off the top of my head would be employees.
Hire Date End Date Active -----Original Message----- From: "Bruce Chitiea" <[email protected]> To: [email protected] (RBASE-L Mailing List) Date: Thu, 12 Jul 2012 11:50:06 -0700 Subject: [RBASE-L] - Design Opinions Requested 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
