Advice needed on use of PK, FK and surrogate keys
Date: Fri, 20 Apr 2001 08:22:15 +0200
Message-ID: <9bok54$5sr$1_at_boile.a2000.nl>
Hi,
Please consider the following:
Table1: CUSTOMER
Table2: ORDER
Table3: ORDER_DETAILS
CUST_ID: integer PK --> generated ID by the database
CUST_NUMBER: integer UNIQUE_KEY --> assigned by user
CUST_NAME:char(20)
ORDER_ID: integer PK --> generated ID by the database
ORDER_NUMBER: integer UNIQUE_KEY
ORDER_DATE: date
ORDER_CUSTOMER: ????
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 Fri Apr 20 2001 - 08:22:15 CEST