Re: Seeking Database Design Guide

RE:     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!  

Buck said; 

>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

Buck

<JMHO>
I can't see ever designing a file in that manner. I'm (I hope I don't start
another DOU/DOW discussion) from the school of thought that every file (with
very rare exceptions) requires a unique key(as per CODD). And if that is 
true, it should be on the physical file itself. (As for the arguements for 
having a non-keyed PF and a unique key LF,  I personally never have had 
either of the two scenerios happen(or heard of them) that justifies that)

In your example the employee master file (if keyed unique) is unique by
just the field "KEY".  Employee number is just an attribute. If thats true
then it's only the intestinal fortitude of your application code to keep
from having two employees with the same employee number.  

Or else have a unique key using a logical file.  But as I said I don't 
subscribe to that school of thought.  

In your example and others like it, It would just as easy to fix the two 
invalid hour amounts instead of the two employee numbers.  

After all, What is the real problem you're trying to fix?  Did you; 
"Enter the wrong hours for an employee,  or  did you 
Enter the wrong employee for that number of hours"?

JMO

John Carr


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