Re: Seeking Database Design Guide

>> 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..

Has anyone here used "surrogate keys" for their files?
For example, we have a payroll master file containing 
Employee#, Employee name
we have a payroll transaction file containing
Employee#, date, hours, rate of pay

Let's say that the time-entry had a foobar, causing employee A's
time to be entered under employee B and vice versa.  With the
database as-is, we can't easily exchange the employee numbers
because of duplicate keys.

If we add a surrogate key to the files:
Key,Employee#, Employee name
Key,Employee#, date, hours, rate of pay

and made Key the unique key to the file, then Employee# is simply
an attribute, like Name and Hours.  

Now, given the same mistake, it's easy to simply swap the employee
numbers because the surrogate keys are different for each employee.

Our Synon developers are starting to use the surrogate key
concept, and I was curious as to how widespread the practise is.

Buck Calabro
Commsoft, Rensselaer, NY
mcalabro@commsoft.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   *
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *