Bruce,
I don’t believe you mentioned that the records were employees, so maybe the following will be of use to you. I use something similar in an Equipment Table to track vehicle status. In addition to the general equipment data, I also have the following column: Equip_Status Equip_Purchase_date Equip_InService_date Equip_Retire_date The Status date has a lookup table with the following codes: Status Description -------- ------------------------------ DT Department Transfer IU In Use LM Lost/Missing PP Planed Purchase RR Rental Return SA Surplus Auctioned SN Stolen SO Surplus still Owned SS Surplus Sold ST Surplus Traded Normally most of the queries address only equipment with code “IU” as all other codes refer to inactive equipment that needs to be kept since there are Work Order/Cost records associated with them that are needed when looking at maintenance costs for previous years. BTW, I quit using binary codes (0 or 1) a long time ago; they were needed in the past to save space when data base capacity was restricted and memory and disk storage were small and expensive. A descriptive alpha code gives you more flexibility and it is more intuitive. Javier, Javier Valencia, PE O: 913-829-0888 H: 913-397-9605 C: 913-915-3137 From: [email protected] [mailto:[email protected]] On Behalf Of Bruce Chitiea Sent: Thursday, July 12, 2012 1:50 PM To: RBASE-L Mailing List 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
