Our employee table uses a status history table since we have had many situations where an employee status changed several times. The record with the null enddate is the active one.
Dennis McGrath Software Developer QMI Security Solutions 1661 Glenlake Ave Itasca IL 60143 630-980-8461 [email protected] From: [email protected] [mailto:[email protected]] On Behalf Of William Stacy Sent: Thursday, July 12, 2012 4:10 PM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: Design Opinions Requested Hi Bruce. I agree with everyone but would ask about the need for all 4 columns. For example in my employee table I have two columns, a datehired and a dateterminated. Logically, every employee who has a non null dateterminated is inactive, else they are active. Bill On Thu, Jul 12, 2012 at 1:37 PM, Bruce Chitiea <[email protected]<mailto:[email protected]>> wrote: Thanks all. Sounds like #1 is the clear choice as I don't need to keep multiple entries per unique ID. Indebted: Bruce -------- Original Message -------- Subject: [RBASE-L] - RE: Design Opinions Requested From: "Tony IJntema" <[email protected]<mailto:[email protected]>> Date: Thu, July 12, 2012 12:57 pm To: [email protected]<mailto:[email protected]> (RBASE-L Mailing List) Bruce, I prefer the first option. It is closer to the normalized model and fits better in the relational model. My experience is that these kind of solutions are more stable, more flexible and need less maintenance. Tony From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Bruce Chitiea Sent: donderdag 12 juli 2012 20:50 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 -- William Stacy, O.D. Please visit my website by clicking on : http://www.folsomeye.net
