[RBASE-L] - RE: Design Opinions Requested

[Bruce Chitiea]

Javier/Bill:

Javier: You're close to the mark. I'm building an application which, in part, tracks capital items in large-scale water delivery systems, such as valves, meters, backflow preventers, pumps. Items with service lives measured in decades. Your Equip_% and service coding fits those items perfectly.

Thanks very much

Bruce

-------- Original Message --------
Subject: [RBASE-L] - RE: Design Opinions Requested
From: "Javier Valencia" <javier.valen...@vtgonline.com>
Date: Thu, July 12, 2012 10:29 pm
To: rbase-l@rbase.com (RBASE-L Mailing List)

Bruce,

 

I don’t believe you mentioned that the records were employees, so maybe the following will be of use to you.

I use something similar in an Equipment Table to track vehicle status.

In addition to the general equipment data, I also have the following column:

Equip_Status

Equip_Purchase_date

Equip_InService_date

Equip_Retire_date

The Status date has a lookup table with the following codes:

Status   Description                   

 -------- ------------------------------

 DT       Department Transfer

IU       In Use

LM       Lost/Missing

PP       Planed Purchase

RR       Rental Return

SA       Surplus Auctioned

SN       Stolen

SO       Surplus still Owned

SS       Surplus Sold

ST       Surplus Traded

 

Normally most of the queries address only equipment with code “IU” as all other codes refer to inactive equipment that needs to be kept since there are Work Order/Cost records associated with them that are needed when looking at maintenance costs for previous years.

BTW, I quit using binary codes (0 or 1) a long time ago; they were needed in the past to save space when data base capacity was restricted and memory and disk storage were small and expensive. A descriptive alpha code gives you more flexibility and it is more intuitive.

 

Javier,

 

Javier Valencia, PE

O: 913-829-0888

H: 913-397-9605

C: 913-915-3137

 

From: rbase-l@rbase.com [mailto:rbase-l@rbase.com] On Behalf Of Bruce Chitiea
Sent: Thursday, July 12, 2012 1:50 PM
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