Advice needed on use of PK, FK and surrogate keys

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 Fri Apr 20 2001 - 01:22:15 CDT