Compound Keys

From: <matt222_at_my-dejanews.com>
Date: Wed, 03 Mar 1999 19:52:03 GMT
Message-ID: <7bk3sv$l09$1@nnrp1.dejanews.com>

I have four tables.

Table #1 MODEL
Table #2 PART
TAble #3 SERIAL
Table #4 ORDER
Table #1 MODEL contains the PK= Model_Num

(there is a one to many join from MODEL to PART)
*one model can have many part numbers
Table #2 PART contains the PK Part_Num plus the FK (primary id) Model_Num together as a composite/compound unique primary key

(there is a one to many join between MODEL and SERIAL)
*each model number can have many serial numbers

Table #3 SERIAL contains the PK Serial_Num plus the FK Model_Num together as a composite/compund unique primary key

(there are two joins - there is a one to many join between PART to Orders and

a one to many join between Serial to Orders)

Table #4 ORDER has a PK ORD_Number. There are 2 migrated FK's from Table PART with values PART.PART_Num and PART.Model_Num as well as another 2 migrated FK's from table SERIAL with values SERIAL.Serial_Num and SERIAL.Model_Num.

THE SITUATION: I want to build an Order Entry Screen using the ORDER TABLE. The user has to only input one model number, the serial number and the part number. I want the form to validate the two combinations 1)Check to see if Model_Num and Part_Num exist and 2) Check to see if Model_Num and Serial_Num exist. The ORDER TABLE however, contains 2 model number items. I want to consolidate the two model number FK - the PART.Model_num and SERIAL.Model_Num. Is this possible? Alternatively, I can program some kind of copy trigger to copy upon data entry to copy the model number from one field (i.e. the SERIAL.MODEL_Num to the PART.Model_Num) - and have redundant data in order for the validation. What kind of code can I use / if any? DOES ANYONE KNOW THE BEST WAY TO SOLVE THIS PROBLEM? Your help would be greatly appreciated. Thanks in Advance matthew_wong_at_mail.toshiba.ca

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Wed Mar 03 1999 - 13:52:03 CST