Steve, Assuming TRANSACTIONS has an order no., I would make the order no. a primary key Since the LINE_ITEMS would also have an order no., it should be a FK referencing the PK in transactions. Otherwise line_items could become orphans.
Having an autonumbered sequence no. might be handy in case of corruption, I don't see the point in making it a PK. Bernie Lis ----- Original Message ----- From: "J. Stephen Wills" <[EMAIL PROTECTED]> To: "RBASE-L Mailing List" <[EMAIL PROTECTED]> Sent: Thursday, April 01, 2004 11:04 AM Subject: [RBASE-L] - Q About Defining PK/Unique ID's, In Practice > 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) > >
