We do the first option regularly. Each line has the needed info plus product ID, active, current, start date, end date columns. If a charge for packing a box changes, the line gets duplicated. The old line never gets deleted but gets updated to inactive status, not current, and gets an end date. Product ID never changes. The new line gets changed to active, marked current, gets a new start date and a blank end date. The current column simply restricts available billing codes to activities we are currently doing. At the drop downs, the user only sees displays of the current, active lines and has no need to see the real changes. Reports get printed based on date ranges needed ID #s. If a report includes a date when a current inactive line was active or a non-current active line was current, it simply prints the report with the needed info because it is based on an ID#. The old stuff is always there and can be simply turned on/off or used as needed. You also print out real easy histories using the Product ID and start/end dates.
Tom Frederick President/CEO Elm City Center 1314 W Walnut Jacksonville, IL 62650 W- 217-245-9504 F - 217-245-2350 E - [email protected] From: [email protected] [mailto:[email protected]] On Behalf Of Dennis McGrath Sent: Thursday, July 12, 2012 2:20 PM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: Design Opinions Requested #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 [email protected]<mailto:[email protected]> From: [email protected]<mailto:[email protected]> [mailto:[email protected]]<mailto:[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
