Would any of you guys be willing to offer any insights, brief or otherwise,
based on your practical experience (as opposed to textbook compliance with
Codd & Date) about defining PK (Primary Key) values. I am especially
interested in this as it would pertain to generation 2-thru-n "child"
tables. That is, a table where the more-textbook-compliant PK would
actually consist of a set of FK values from a "parent" table.
As a typical example :
Table Name PK Comments
--------------- ---------------- -----------------------------------------
-----------
INVENTORY_ITEMS ItemID
CUSTOMERS CustomerID We'll leave out the issue of
1-Customer:N-Accounts
for this example
TRANSACTIONS TransactionID CustomerID is an FK here
LINE_ITEMS ??? The n-th child table in this example
The latter one is what I'm trying to get a better handle on. There are
several possibilities for defining a useable PK :
A) Arbitrary sequential numbering, even if they are not contiguous
B) Some sort of arbitrary numbering, using a checksum
C) Multiple FK fields (TransactionID & LineNumber if LineNumber is static
after INSERT)
D) Concatenation of appropriate FK values into a single field,
a kind of embedded intelligence, if I recall the term correctly.
Although I also recall that this is generally frowned upon,
if the FK values are maintained as attributes in their own right,
once the ORIGINAL concatenated value is INSERTed, it could remain
static, even if the FK-referenced values were changed in the parent
tables ...
E) ???
Any discussion of pro's/con's related to data-types, maintenance,
performance is appreciated.
Thanks,
Steve (in Memphis)
