Firstly I have never understood why people want to have the same name for the primary key column of different tables. My policy is to use the table name followed by ID. Eg ClientId, EmployeeId etc. The only good reason I know of for using the same name, is that it makes object persistence easier.
Re the uniqueness, I am now moving to making the id's globally unique. I create them in code and assign them when required. In part this is because I am moving to using tiOPF, but I am also doing it as a general move. Auto incs are a pain in the proverbial if you actually need to use the pk in code. Once the record is posted, you need to query it to get the pk back again. This is a pain in multi-tier apps. Also, as you noted, they are hard to use with globally unique identifers. I only use auto incs if the pk is never used in code. You could use triggers to assign the pk. Or you could use GUIDs which are always unique and can be generated on the client. tiOPF uses the high low algorithm to generate an integer pk on the client. Basically it pulls a unique number out of the database and uses that as the high part of the unique number. The low part starts at 0 and increases to eg 99 so you can generate 100 unique ids before you need to hit the database again. FYI tiOPF is an object persistence framework. See http://tiopf.sourceforge.net/. Regards Sean Cross IT Systems Development Manager Catalyst Risk Management PO Box 230 50 Dalton St Napier 4140 DDI: 06-8340362 mobile: 021 270 3466 Visit us at http://www.catalystrisk.co.nz/ Offices in Auckland, Napier, Wellington & Christchurch Disclaimer: "The information contained in this document is confidential to the addressee(s) and may be legally privileged. Any view or opinions expressed are those of the author and may not be those of Catalyst Risk Management. No guarantee or representation is made that this communication is free of errors, viruses or interference. If you have received this e-mail message in error please delete it and notify me. Thank you." > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:delphi-talk- > [EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] > Sent: Tuesday, 7 August 2007 11:10 a.m. > To: [email protected] > Subject: SQL question > > SQL question: > > I am firm believer that all tables' starts with one column, I my case I > always call the column UID. The UID is a Unique Identifier. This way I > can reference any other column in another table to this unique record. > I here assume everyone knows what I am talking about. > > The question I have is should the UID be unique to only that table or > should it be unique to the database? > > If unique to the table, then configuring the table to have the first > column to be an AUTO INCERMENT field and most issue are solved here. > > If unique to the database then I assume you need to create a table for > UID's with only one column which would be an AUTO INCREMENT field and > maybe a Session number field to know which new UID belongs to whom. > When the requester has gotten the UID the requester can delete the > record. In this case the program has to do a little work in regards to > get the UID from this table before you can insert a new record into any > tables. > > OK now lets make it a little more interesting since real life > application many time have one-to-many or many-to-one relationships one > could have a cross reference table that would either have two columns > (UID | UID) if we have database unique UIDs or we could have four > columns (UID | TableName | UID | TableName) if we have UID that is only > unique to a table. > > Any input on this topic? > > > > Bjarne \v/ > Coordinate your email-newsletters with NTS eBlast tool suite > http://www.go2nts.com/eblast > > _______________________________________________________________________ > _ > AOL now offers free email to everyone. Find out more about what's free > from AOL at AOL.com. > =0 > __________________________________________________ > Delphi-Talk mailing list -> [email protected] > http://www.elists.org/mailman/listinfo/delphi-talk __________________________________________________ Delphi-Talk mailing list -> [email protected] http://www.elists.org/mailman/listinfo/delphi-talk
