#1 is simpler and performs better (no table joins required to retrieve only active rows.) #2 would be the best option if you have to keep a history of changes to the active state, i.e. a one to many relationship in the data.
Dennis McGrath Software Developer QMI Security Solutions 1661 Glenlake Ave Itasca IL 60143 630-980-8461 dmcgr...@qmiusa.com From: rbase-l@rbase.com [mailto:rbase-l@rbase.com] 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
