Depending on the database, you can probably get the Unique ID returned to you when you create the record in the database.
As for naming every Unique ID with the same name, I, too, find that suboptimal . . . in fact, when I have been involved in design standards for databases I have specifically outlawed that practice. Why? because I prefer to use the same name for the Primary Key in TalbeA (e.g.ClientID) as the FK in TableB (which may be an Orders table with the ClientID linking it to the Clients Table). That keeps things straight in both tables. Otherwise, you might wind up with an Orders table that links to an Accounts table, a Clients Table, and a Terms table and has 4 instances of UID lurking in it somehow. At 08:33 PM 8/6/2007, you wrote: >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: delphi-talk@elists.org > > 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 -> Delphi-Talk@elists.org > > http://www.elists.org/mailman/listinfo/delphi-talk >__________________________________________________ >Delphi-Talk mailing list -> Delphi-Talk@elists.org >http://www.elists.org/mailman/listinfo/delphi-talk Ralph D. Wilson II Systems Analyst Home: (210) 497-2643 Cell: (210) 387-7744 Fax: (210) 497-5726 [EMAIL PROTECTED] [EMAIL PROTECTED] __________________________________________________ Delphi-Talk mailing list -> Delphi-Talk@elists.org http://www.elists.org/mailman/listinfo/delphi-talk
