As a point of interest. A data types have a minimum storage space of 4 bytes. This holds true even if if character data is defined for less than four characters.
Jim Bentley American Celiac Society [email protected] tel: 1-504-737-3293 >________________________________ > From: Javier Valencia <[email protected]> >To: RBASE-L Mailing List <[email protected]> >Sent: Friday, July 13, 2012 12:29 AM >Subject: [RBASE-L] - RE: Design Opinions Requested > > >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:[email protected] [mailto:[email protected]] 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 > > >
