Best Primary Key Solution?

Hi there,

Looking for a bit of help with my problem:

Say i have 3 tables-

tblClients
clientID (primary key identity/autonumber)
clientName (varchar 50)

tblCities
cityID (primary key identity/autonumber)
cityName (varchar 50)

tblClientsCities
ID (primary key identity/autonumber)
clientID (int)
cityID (int)

A client can be located in more than 1 city so i have tblClientsCities (think thats the right way to do it). Say i add a new client and the autonumber changes to "10" which is that client's identifier. How do i then add that identifier to tblClientsCities? I mean it could have been 3,7,205 absolutley anything.

I thought is would be easier to make up a unique key for each client with a script eg

client name: PJ Computers
Unique key Generated: PJCOMP58784

Now that the primary key is known in advance it can be added to tblClients and then tblClientCities. But! i was reading around and many seem to think primary key's like this will slow things down.

So my question is what's the best way of accomplishing this?

Any help would be much appreciated, thanks :)Check out @.@.identity (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_globals_50u1.asp) and/or scope_identity (http://msdn.microsoft.com/library/en-us/tsqlref/ts_sa-ses_6n8p.asp). These allow you to work with IDENTITY columns.

"Smart key" values like you suggested are bad for many reasons. The biggest practical problem is key colisions. The biggest theoretical problem is data changes and how those affect the smart key. There are many other problems, these are just the tip of the iceberg.

If you want to pursue an avenue a lot like your "smart key" approach that does not have the problems, consider using GUID values using NewId (http://msdn.microsoft.com/library/en-us/tsqlref/ts_na-nop_4pt0.asp) and UniqueIdentifier (http://msdn.microsoft.com/library/en-us/tsqlref/ts_ua-uz_6dyq.asp) columns.

-PatP