[RBASE-L] - RE: Design Opinions Requested

[Bruce Chitiea]

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