Home » RDBMS Server » Server Administration » 1ST one WRONG - correction(READ THIS ONE) Composite/Compound Keys *Data Modelling Question*
1ST one WRONG - correction(READ THIS ONE) Composite/Compound Keys *Data Modelling Question* [message #370226] Tue, 02 March 1999 20:58 Go to next message
Matthew Wong
Messages: 1
Registered: March 1999
Junior Member
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@mail.toshiba.ca
Re: Composite/Compound Keys *Data Modelling Question* [message #370227 is a reply to message #370226] Thu, 04 March 1999 07:27 Go to previous message
Chris Hunt
Messages: 27
Registered: March 1999
Junior Member
Well it certainly works, I've just tried the following without trouble:

CREATE TABLE models
(model#         NUMBER,
 description    VARCHAR2(30),
CONSTRAINT model_pk PRIMARY KEY (model#))
/
CREATE TABLE serials
(serial#        NUMBER,
 model#         NUMBER,
 description    VARCHAR2(30),
CONSTRAINT serial_pk PRIMARY KEY (serial#, model#),
CONSTRAINT serial_fk FOREIGN KEY (model#) REFERENCES model (model#))
/
CREATE TABLE parts
(part#          NUMBER,
 model#         NUMBER,
 description    VARCHAR2(30),
CONSTRAINT part_pk PRIMARY KEY (part#, model#),
CONSTRAINT part_fk FOREIGN KEY (model#) REFERENCES model (model#))
/
CREATE TABLE orders
(order#         NUMBER,
 model#         NUMBER,
 part#          NUMBER,
 serial#        NUMBER,
 description    VARCHAR2(30),
CONSTRAINT order_pk PRIMARY KEY (order#),
CONSTRAINT order_fk1 FOREIGN KEY (model#,serial#) REFERENCES serial (model#,serial#),
CONSTRAINT order_fk2 FOREIGN KEY (model#,part#) REFERENCES part (model#,part#))
/


Whether all this is a sensible approach is more difficult to say. Your structure implies that any given part is only used in one model - this seems unlikely. I also don't fully understand what kind of entity "Serial No" is - it sounds more like an attribute to me.

My instinct would be to avoid composite keys where possible. The key for "parts" should be "part#" unless "part#" can be duplicated across models, ie. Model A and Model B both have a (different) part# 1.

It's a tricky business this data design - if you care to mail me direct (chunt@foobar.co.uk) I'll see if I can offer any more help.

Previous Topic: Re: Help oracle time fomatting is driving me crazy
Next Topic: Re: PL-SQL Procedure URGENT
Goto Forum:
  


Current Time: Fri Oct 23 17:25:33 CDT 2020