Future Proof File Designs/Methods

[Michael Lee]

A company that I've done some work for in the past has an excellent way to
handle this sort of thing for lookup tables. The only drawback I found with
their system is if you wanted to do a lookup with multiple keys. As well, due to
possible locking problems, this mainly seems to be a good idea with lookup
tables. I will attempt to explain their idea below. Keep in mind I'm doing this
from memory and may not be exactly right but you should get the idea. I see
people from that company at this site and perhaps they could fill in any blanks
or correct any errors.

They had a key file (names changed to protect the innocent) as follows:
KEY_FILE
.CODE_ID                 X(12)  (unique index)
.CODE_LENGTH      N(2)
.CODE_TYPE            X(1)
.CODE_DESCRIPTION
(and a couple of other items)

CODE_FILE
.CODE_ID             X(12) (repeating index)
.DETAIL_CODE   X(12)
.DESCRIPTION    X(80)
.UPSHIFT_FLAG  X(1)

CODE_FILE_BREAKDOWN
.CODE_ID              X(12)
.DETAIL_CODE    X(10)
.ITEM_NAME        X(20)
.ITEM_TYPE          X(1)
.ITEM_START        9(2)
.ITEM_END            9(2)

The first table is where the new virtual "file/table" is declared.
The second table declares the different data that is to populate this
"file/table". The DESCRIPTION field can be broken down into as many fields as
you desire (not exceeding a total of 80 bytes in this case). The
CODE_FILE_BREAKDOWN file allows the user to specify what part of the DESCRIPTION
represents what data. This is not helpful when accessing the file (although it
could be if you put enough work into it), but rather is used to help in entering
the data into these fields (ie. save the user from spacing over to the 12th
position to enter a particular piece of data).  The ITEM_TYPE allows you to
declare the virtual "field/column" as "C"haracter, "N"umeric, "D"ate, or even
"T"able (to lookup on another code table).

There were USE files desclared to access this table properly and all the
programmer had to know was the CODE table name, the actual CODE being looked up
and the position of the data in the DESCRIPTION (if it represented more than one
piece of data). They had different USE files for INPUT procedures, EDIT
procedures and OUTPUT procedures as well as a general lookup which set a flag if
the lookup failed.

Here is an example of a lookup for a country:

Table:  COUNTRY    Description: This table represents various countries
Length: 3

    Code        Description
                    123456789012345678901234567890123456789012345678901234567890

01  CAN     Canada                                    Y        PROV
02  USA      United States of America          Y        STAT
03  MEX      Mexico                                    Y        PROV
04  ITA        Italy                                          N

In the above example to do a lookup you would need to know the code table
"COUNTRY". If you were calling this from the input procedure you could display
all the COUNTRY_CODEs and allow the user to select one. If you were looking up a
value in the edit procedure you would pass it the actual country code and issue
an error message if it didn't exist.  The flag in the 25th column is used to
indicate that there are provinces/states for this 'COUNTRY'. The 'PROV' and
"STAT' in the 30th column indicate the other table to lookup for
provinces/states.

I'm not sure if this is exactly what you were looking for, but it seems close.
Any missing features or mistakes are the my fault. This system is in use in many
different manufacturing plants across the US, Canada and it works great. I
myself was very impressed by it.



Michael Lee
MCL Systems Inc.


"Pickering, John (NORBORD)" wrote:

> Given the environment ...
> >Powerhouse 7.29
> >HP Image
>
> In spite of your concern about the complexity, I firmly believe that any
> system should have a "compile everything" job stream. Probably 2 jobs, one
> for Qdesign and another for Qtp and Quiz. This may actually be the single
> most important "future-proofing" tool!
>
> With new "even more y2k compliant" versions of PH being delivered regularly,
> a "compile everything" job makes installation simple. With an Image
> maintenance tool (e.g. Adager, DBGeneral), database changes are easily
> accomplished and then the system can be easily recompiled. This frees one
> from the lousy design decisions made because "it's too hard to change the
> database" or "it's too hard to recompile the system". The dataset with the
> filler is never the one which needs the new items:-)
>
> If faced with maintenance of a system without such jobs I will bite the
> bullet and create them. This may entail some housekeeping such as program
> and subfile renaming so that a sorted list of the programs will be in a
> compileable sequence. This also means that the PH component to be used to
> compile the source file must be determined from the file name (not always
> obvious in HP 3000 shops). The job should derive the program list itself
> (i.e. from a LISTF on MPE, from whatever passes for a directory list on
> other OS's) rather than relying on lazy programmers to keep a manual list up
> to date.
>
> My Qdesign job purges all existing compiled screens (great for cleaning up
> the junk) and then compiles all of the screens in the production source
> group. My Qtp and Quiz job purges all the compiled runs and reports and then
> compiles all the Qtp and Quiz in the production source group. Since this
> runs in batch and usually after hours I don't care too much about machine
> abuse so I run a fresh Qtp or Quiz for each source file. This saves me from
> the nasty problem of inheritance of "set" values between runs and reports.
>
> And permanent subfiles are a lousy idea too!
>
> /rant :-)
>
> John Pickering
> JWP Systems Inc.
> Toronto
>
> = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
> Subscribe: "subscribe powerh-l" in message body to majordomo@lists.swau.edu
> Unsubscribe: "unsubscribe powerh-l" in message to majordomo@lists.swau.edu
> powerh-l@lists.swau.edu is gatewayed one-way to bit.listserv.powerh-l
> This list is closed, thus to post to the list, you must be a subscriber.

= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
Subscribe: "subscribe powerh-l" in message body to majordomo@lists.swau.edu
Unsubscribe: "unsubscribe powerh-l" in message to majordomo@lists.swau.edu
powerh-l@lists.swau.edu is gatewayed one-way to bit.listserv.powerh-l
This list is closed, thus to post to the list, you must be a subscriber.