Relationship / Composite Key problems

J

Joel Wiseheart

I have two questions, marked by *** symbols at the bottom
of this post.

I have four main tables in a database, used to track
quality defects in our products. The E-R diagram would
show something like this, in its collapsed form:

Header Info
|
- Defect Info
|
- Disposition Info
|
- Corrective Action Info

Expanding it to include the key fields (PK = Primary Key,
FK = Foreign Key, CK = Composite Key):

Header Info
.MRR Number (PK)
|
- Defect Info
.MRR Number (FK), (CK)
.Defect Number (CK)
|
- Disposition Info
.MRR Number (FK),(CK)
.Defect Number (FK),(CK)
.Disposition Number (CK)
|
- Corrective action info
.MRR Number (FK),(CK)
.Disposition Number (FK),(CK)
.CA Number (CK)

This lists some data examples, and the key fields:

The header contains general information about the part(s)
on a work order that are being written up, like the date,
part number etc.:

MRRNo 100200

The Defects table could have several defects on one work
order (One part may have a cover with a dent, another part
may have a scratch in the paint):

MRRNo 100200, Defect 1; MRRNo 100200,Defect 2, etc.

The Dispositions say what to do about the defects. One
defect can have several dispositions (If 10 parts are
defective, 7 may be reworked and 3 may be scrapped, for
instance):

MRRNo 100200, Defect 1, Disposition 1; MRRNo 100200,
Defect 1, Disposition 2; MRRNo 100200, Defect 2,
Disposition 1, etc.

The Corrective Actions are issued to suppliers only if
there are multiple occurences of the same disposition (For
instance, if we have received 200 scratched covers from
the same supplier, which could be a handling issue):

MRRNo 100200, Defect 1, Disposition 1, CA 1; MRRNo
100200, Defect 1, Disposition 1, CA 2 etc.

The end result is a nest of composite keys, trying to
relate all the fields in this hierarchal structure, as
shown in the second diagram above.

The composite keys are not only a headache to set up in
the relationships, but have been also causing me a lot of
problems in developing queries. This is due to that the
combination of fields are unique, but the data in one of
the composite key fields by itself is not.

***Is there any better way to relate these tables, to show
this hierarchal structure?***

Also, I have been looking all over for a reference that
goes into more detail on how to work with composite keys,
but can't seem to find much info anywhere. It shows how to
make a composite key, but nothing about some of the
difficulties in working with linking composite key tables,
and how to work with (or around) them.

***Does anyone know where I can get more information on
composite keys?***

Sorry for the long-winded post. It's tough to desrcibe in
ASCII over the web. Also, these are the conceptual names
for E-R diagram purposes, not the names in the actual
database (for example, the Corrective Action Info table is
actually named tblMRRCA).

Thanks!
Joel
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top