Dear All,

This question popped out after reading several articles on the
merits of using surrogate keys in relational databases. My
apologies since this is not an interbase-specific issue.

Normally we would have ORDER and ORDER_DETAIL arranged as such:
...
Create Table ORDER (
ORDER_ID integer not null,
ORDER_DATE timestamp not null,
CUST_ID integer not null,
...,
...,
constraint PK_ORDER primary key (ORDER_ID) );

Create Table ORDER_DETAIL (
ORDER_ID integer not null,
ORDER_LineSeq integer not null,
ITEM_ID integer not null,
Amount integer not null,
...,
...,
constraint PK_ORDER_DETAIL primary key (ORDER_ID, ORDER_LineSeq) );

alter table ORDER_DETAIL
add constraint FK_ORDER foreign key (ORDER_ID)
references ORDER (ORDER_ID);
...

Note:
1. ORDER_ID is generator-based.
2. ORDER_LineSeq is sequence of number, starting from 1, usually generated
from the client (delphi) side.

Now it seems to me that the surrogate keys could/should also be applied to
the
ORDER_DETAIL table, since this table has a compound primary key. The
resulting
ORDER_DETAIL table would look like this:

...
Create Table ORDER_DETAIL (
ORDER_DETAIL_ID integer not null,
ORDER_ID integer not null,
ITEM_ID integer not null,
Amount integer not null,
...,
...,
constraint PK_ORDER_DETAIL primary key (ORDER_DETAIL_ID) );
...

The most notable change is that ORDER_ID column is not part of the
primary key anymore, but just a 'reference' (what's the correct term
for this??? :D). Aside from that, ORDER_DETAIL_ID now can also be
generator-based, not generated from the client side, which is mostly
a good thing (I think).

At this point I cannot decide which approach is 'better'. The major
complaints people are having with the surrogate model is that it's
'unusual'. Opinions/suggestions/tips or even links to appropriate
'reading materials' will be greatly appreciated. Many thanks in advance.

Salam,
sugi.

PS : I guess this can be the tip of the iceberg for bigger question,
like "is surrogate keys ALWAYS a good idea ?".