Re: Seeking Database Design Guide -- MIDRANGE-L

David wrote:
> 
> Greetings!
> 
<snip>
> 
> Now it's time for the critic to take some of his own medicine. 
>  I want to bring my design skills up
> to standard to take advantage of the features DB2/400 offers.
> 
> Your advice, please!  

Grab yourself any book which explains database normalization.
In a nut shell there are 5 levels of normalization.
If a data base is designed to at least the third level:
No compound fields, no repeating fields, no file containing data that is
not dependant upon the key.
Then you're doing pretty good.  The objective is that a piece of
information only exists in one place.

Some examples:
If you have a 15 digit item code where the first 3 digits indicate
product line, it should be a separate field.  
If you have a file keyed by item code and year with 12 accumulators in a
record for each month of the year (a common practice) it works until the
company revises their accounting to 13-4 week periods.  The file should
be keyed by item/year/period with a single accumulator.  Or even more
common violation is an employee file with a fixed number of voluntary
deductions. The array instance then becomes part of the 'data' key, and
it's data you are indexing.

If you have an employee file which is indexed by employee number, that
record would only contain data that is dependant solely upon the
employee.  If you are a union highway construction company, rate of pay
for operators is determined by the equipment operated and the current
contract in effect for the job, therefore the employee file would not
contain rate of pay. (although you would have a field for it for other
employees)
It would have the fact the the operator is either an apprentice or
journeyman, the equipment definition would have the equipment class, the
pay rate file would be indexed by
employee_grade/equipment_class/contract_date with rate of pay as a data
field.
The time card entry would have the employee number, the job code (to
determine contract date), which equipment operated (to determine class),
the date worked (to test contract expiration) and how many hours.

Allowable replication of data would be something like an item definition
which contains an assigned sales department, the sales history file
could be indexed by item/year/period/department in the event that an
item is reclassified, you have the original departments sales figures
intact since by such indexing you are capturing data values at a given
point in time. Joining the sales and item files lets you see sales by
current assignment or actual occurance.

Once you've done this a couple of times it becomes second nature.  My
biggest hurdle was getting over the fact that to normalize meant
creating a zillion files! :-)

James W. Kilgore
qappdsn@ibm.net
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* This is the Midrange System Mailing List!  To submit a new message,   *
* send your mail to "MIDRANGE-L@midrange.com".  To unsubscribe from     *
* this list send email to MAJORDOMO@midrange.com and specify            *
* 'unsubscribe MIDRANGE-L' in the body of your message.  Questions      *
* should be directed to the list owner / operator: david@midrange.com   *
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *