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
