[RBASE-L] - Re: Q About Defining PK/Unique ID's, In Practice

[Dr. Stacy]

I do the same thing, and wonder why database programs don't just do it automatically, at the engine level, so that I don't have to do it myself. bill van der Zwaag, Frank wrote: Hi Lawrence, we are running a massive database and we use autonumbering for all tables. - It ensures that each record is unique - It gives us assurance around completeness of the number of records (we don't delete records, we mark records as deleted). - We use the unique recordnos for referencing into other tables: -- Each table starts with a field called recordno (integer, unique, autonum, indexed). E.g. table persons: first field is recordno -- When a table is cross referenced into another table the field is inserted in the other table directly after the recordno field, and is named as tablename_recno. E.g. table transactions: first field is recordno, next field is person_recno, etc -- I use PK/FK for referential integrity; e.g. when I want to ensure that a parent record doesn't get accidentally deleted with childs dangling around in the system. -- I don't necessarily use the unique recordno facility for lookup tables, like POL = Polynesian, EUR. Jury is still in deliberation on that one. The only real reason would be to be consistent around the table layouts. My $ 0.02 Frank -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Lawrence Lustig Sent: Friday, 2 April 2004 04:55 To: [EMAIL PROTECTED] Subject: [RBASE-L] - Re: Q About Defining PK/Unique ID's, In Practice Any discussion of pro's/con's related to data-types, maintenance, performance is appreciated. My policy is that every table gets an autonumbered, non-meaningful primary key. Very occasionally, I will "succumb" to the allure of a two -FK-column primary key if I believe the table will not have any children. I often come to regret it. Personally, I would recommend always putting that single-column autonumbered primary key on the table. If you want to enforce uniqueness for the foreign keys, create a two-column unique key on them. One case in which I don't use autonumbered keys is for code tables which I do not plan on allowing the user to change. For instance, a payments type table might look like this: CHK Check CS Cash CC Credit Card The reason is that I won't have to join the payment type table to the transaction table in order to print the payment type -- I can just print the CHK, CS, or CC code. -- Larry __________________________________ Do you Yahoo!? Yahoo! Small Business $15K Web Design Giveaway http://promotions.yahoo.com/design_giveaway/ ____________________________________________________________________ CAUTION - This message may contain privileged and confidential information intended only for the use of the addressee named above. If you are not the intended recipient of this message you are hereby notified that any use, dissemination, distribution or reproduction of this message is prohibited. If you have received this message in error please notify Air New Zealand immediately. Any views expressed in this message are those of the individual sender and may not necessarily reflect the views of Air New Zealand. _____________________________________________________________________ For more information on the Air New Zealand Group, visit us online at http://www.airnewzealand.com _____________________________________________________________________ .