I get that. Obviously with an employee table, you can know pretty well when the "enddate" is, it's gonna be pretty close to the last paycheck written to that guy. I think if you need to follow a status more carefully you might want to monitor (record) all access to that table in a separate log table that shows each and every access to the table by employee, time and date stamped, maybe even with "before" and "after" values to catch the bad guys.
Anyway, it's a good exercise and I think Cobb would actually be pleased. On Thu, Jul 12, 2012 at 4:05 PM, Bruce Chitiea <[email protected]>wrote: > Bill: > > 1) The 'begdate' and 'enddate' values are what you would expect. > > 2) The 'xActive' flag is a deliberate, binary [1|0] affirmation of status, > offering protection against the possibility of date-math coding errors and > omissions. A statement that "I attest that THIS value is active." > > 3) The 'flagdate' value allows one to 'obsolete' a record without knowing > the actual 'enddate' value. I'm working in an area where 'enddate' values > may not be knowable - only 'guessable' - and that the difference in meaning > may make all the difference in a subsequent investigation of fact. > > Bruce > > -------- Original Message -------- > Subject: [RBASE-L] - RE: Design Opinions Requested > From: William Stacy <[email protected]> > Date: Thu, July 12, 2012 2:09 pm > To: [email protected] (RBASE-L Mailing List) > > 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]>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]> >> Date: Thu, July 12, 2012 12:57 pm >> To: [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] <[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 > > > > -- William Stacy, O.D. Please visit my website by clicking on : http://www.folsomeye.net
