[RBASE-L] - RE: Design Opinions Requested

[Bruce Chitiea]

Dennis:

I might have added that the app involves importing gobs of data of questionable provenance where null or non-null values in the 'enddate' column are contradicted by other values. Forensically I'm forced to preserve the imported value: the 'xActive' flag and 'flagdate' values are steps towards a solution (still working on it) where I can attest: "This is what the import said, but this is what we know, or at least can well-guess."

Once we have control of all inputs, we're still working in a world where the 'enddate' may not be known. Hence 'flagdate'.

Bruce

-------- Original Message --------
Subject: [RBASE-L] - RE: Design Opinions Requested
From: Dennis McGrath <dmcgr...@qmiusa.com>
Date: Thu, July 12, 2012 2:15 pm
To: rbase-l@rbase.com (RBASE-L Mailing List)

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

dmcgr...@qmiusa.com

From: rbase-l@rbase.com [mailto:rbase-l@rbase.com] 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 <rby...@safesectors.com> 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" <t.ijnt...@cio-bv.nl>
Date: Thu, July 12, 2012 12:57 pm
To: rbase-l@rbase.com (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: rbase-l@rbase.com [mailto:rbase-l@rbase.com] 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