Re: Advice needed on use of PK, FK and surrogate keys

keep in mind that there are "logical keys" and "physical keys". these terms are significant when building tables since logical keys almost always become alternate keys (indexes). the "logical key" is composed of those fields which uniquely identify a record from a human's perspective (such as full name and complete address in a database of all households in the US) whereas the physical key is the system generated unique identifier (which should remain invisible, transparent, and of no conern in any way to a user) .

your question, then, is should you use the physical key or the logical key as the foreign key in the child table. i suggest the physical key be used as there is a better guarantee of maintaining data integrity.

a minor note on your problem statment. i would say orders belong to customers rather than customers belong to orders. now, assuming a given order belongs to one and only one customer (an important assumption), use customer_id as the foreign key in orders and order_id in order-details. if you would like to denormalize so order-details can be retrieved by a single join to customer, rather than joining customer to order and order to order-detail, then use both order-id and customer-id as foreign keys in order-details.

whenever there is a "many-to-many" relationship, a junction entity (resolving table) is required so the m-m becomes m-1-1-m. in your case, however, i expect customer to orders is 1 to many (one customer may have 0 or more orders) and the relationship from order to order detail is also one to many (one order may have 0 or more line items). in this case, a junction entity, customer-orders, is not required. you may simply add customer_id after order_id in the order table and order-id after order-detail-id in the order-detail table.

if, however, more than one customer can be associated with only one order, then you do have a many-to-many condition which would necessitate a customer-order table to resolve.

"Ronald" <ronald_at_oec.nl> wrote in message news:9bok54$5sr$1_at_boile.a2000.nl...
> Hi,
>
> Please consider the following:
>
> Table1: CUSTOMER
>
> CUST_ID: integer PK --> generated ID by the database
> CUST_NUMBER: integer UNIQUE_KEY --> assigned by user
> CUST_NAME:char(20)
>
> Table2: ORDER
>
> ORDER_ID: integer PK --> generated ID by the database
> ORDER_NUMBER: integer UNIQUE_KEY
> ORDER_DATE: date
> ORDER_CUSTOMER: ????
>
> Table3: ORDER_DETAILS
> OD_ORDER_ID: integer FK --> contains ORDER_ID from table ORDER to
 establish
> 1-n relation
> OD_LINE: integer UNIQUE_KEY
> OD_DATA .....
>
> I use .._ID's to create primary keys for several tables I'm are using.
 Those
> are not 'real' data but generated by the database. They are not there just
> 'for the fun' but I actually have a use for them. We have some kind of
> object-linking
> functionalty which uses these id's (because the ID's al have the same
> datatype it can be generic for all tables).
>
> Ok, now for my question: as you can see I also use the ID for 1-n
 relations
> between tables who have a parent-child relation (ORDER_DETAILS belong to
> ORDER's and don't have a meaning on there own). But what about
> ORDER_CUSTOMER ? This is kind of a lookup-field which should assign a
> customer to the order. Should I fill this field with a CUST_NUMBER or
 should
> I use the CUST_ID ???
>
> Any comments are very welcome,
>
> TIA,
>
> Ronald
>
>
Received on Sat Apr 21 2001 - 02:04:13 CEST