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.TonyFrom: [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 RequestedAll: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]begdateenddateflagdateor:2. Maintain a record status table:RECORDSTATUS-------------------RecordStatusID <pk>TableName - one of the tracked tablesRecordID <fk> - Primary-key value to be flaggedReplacedRecordID <fk> - Value inactivated by RecordIDxActiveBegDateEndDateFlagDateRecord 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
