Re: Create a foreign key from a field that is part of a mulitple primary key

From: Mark C. Stock <mcstockX_at_Xenquery>
Date: Sun, 5 Sep 2004 16:15:46 -0400
Message-ID: <9-2dnVX5hr5k7qbcRVn-sg@comcast.com>

"Niall Litchfield" <niall.litchfield_at_dial.pipex.com> wrote in message news:413b6693$0$20243$cc9e4d1f_at_news-text.dial.pipex.com...
| "Mark C. Stock" <mcstockX_at_Xenquery .com> wrote in message
| news:SbSdnR0ccbBME6rcRVn-sQ_at_comcast.com...
| > so, if you're offended, sorry. but if you're up for a lively discussion,
| why
| > not post a structure that you feel requires more than one column for the
| > PK -- either we'll see each other's side better, or you'll have the
| > satisfaction of proving me wrong ;-)
|
| A possibility for you. We have an helpdesk app. It contains, not entirely
| surprisingly, an inventory table. That table contains the fields MODEL and
| SERIAL#, we use (MODEL,SERIAL#) as the PK. I'm not prepared to guarantee
| uniqueness of the serial# across manufacturers and over time. I am prepared
| to gurantee the uniqueness of SERIAL# across the same model from the same
| manufacturer over time. I'll grant you that we could use an artificial key
| (and in fact it would be my preference), never the less for the design of
| the app the important thing is that it is an item of inventory (not that it
| is a laptop made by manufacturer y or a router made by company Z).
|
|
| --
| Niall Litchfield
| Oracle DBA
| http://www.niall.litchfield.dial.pipex.com
|
|

thanks, good example, although i'd have to go on a couple assumptions or get further info, as indicated below

first, that's a perfectly legitimate PK structure -- but since it is multi-column, it implies dependency on another table (or entity)

so, the MODEL column likely is an FK to a SUPPLIER_MODEL table, and if the SUPPLIER_MODEL table does not exist it should consciously have been determined to be out of scope (i'm calling this the SUPPLIER_MODEL table based on your description, even though you didn't indicate any reference to a SUPPLIER table or SUPPLIER_ID in the PK)

if any info is required about the SUPPLIER_MODEL (such as description, weight, dimensions, length of warranty, etc) it should go in the SUPPLIER_MODEL table (which then brings it back into application scope if it had been consciously excluded), not the INVENTORY table. If such attributes are stored in columns of the INVENTORY table, you've got a denormalized design, which should be fully documented as to why it is denormalized, and how data anomalies are prevented -- i.e., when updating a model's description in the inventory table

(if i understood correctly that the MODEL number is the supplier model number, than there probably should be a SUPPLIER table out there somewhere -- and unless the MODEL number is an internally made-up number, and not really the model number assigned by the supplier, the SUPPLIER_ID probably needs to be showing up in the INVENTORY or SUPPLIER_MODEL table as well -- otherwise, it may be that the MODEL number you've got might actually be an intelligent key indicating who the supplier is?)

in other inventory or BOM systems where the MODEL# is determined by the company maintaining the system, not by the supplier (i.e., for a manufacturer or a distributor who has many models or sub-assemblies supplied by different vendors), you may see 3 tables (only PK columns shown):

  a.. MODEL(MODEL#)
  b.. SUPPLIER(SUPPLIER_ID)
  c.. INVENTORY(MODEL#, SUPPLIER_ID, SERIAL#) 

or perhaps 4 tables,

  a.. MODEL(MODEL#)
  b.. SUPPLIER(SUPPLIER_ID)
  c.. MODEL_SUPPLIER(MODEL#, SUPPLIER_ID)
  d.. INVENTORY(MODEL#, SUPPLIER_ID, SERIAL#)

In the 4-table scenario, the INVENTORY table has one FK: MODEL# and SUPPLIER_ID comprise an FK to the MODEL_SUPPLIER table MODEL# is not an FK column in itself and SUPPLIER_ID is not an FK column in itself -- i.e., you can't inventory an item if it is not already known to be available from the supplier

In the 3-table scenario, the INVENTORY table has two FKs: MODEL# is an FK to the MODEL table
SUPPLIER_ID is an FK to the SUPPLIER table

• in this case, you can inventory an item as long as the model and supplier are both known to the system, without considering whether that supplier is known to be valid for that model.

++ mcs Received on Sun Sep 05 2004 - 15:15:46 CDT