This is more of a general design question. I’ve followed the endless debates on other forums as to what to use for a table’s primary key (mainly GUID vs. Identity). As a practical matter I’ve found that using the identity type and letting the DB engine take care of creating new keys works well. My question comes down to, what is the best way to address the user’s requirement for a unique ID when using an identity value for the pk. For example if I had an order table, a natural key could be the sales order number. Unfortunately the identify pk by itself doesn’t make a good sales order, there is nothing to differentiate it from an invoice number that may also be an identity value. One solution would be to create the SO number from the identify value by adding a prefix or some other modification. Another approach would to generate a unique SO number, but then that just reintroduces a similar problem of maintaining a unique value for the pk that was solved by using identity value. At that point an option would be to forget the identity value altogether and just generate a unique value that is both the SO number and the primary key.  I’m curious as to what others are doing in this situation.

Another PK dilemma.   I remember attending a training class in Denver when I was first exposed to the concept of a PK in the 90's -- what a wonderful experience.    It was like, why didn't I think of that..

IMHO, I think an integer identity value for a PK is best.  Ours standards dictate that we have a PK for every table regardless how unimportant we may think of the table's status.  One could debate the GUID theory but with our applications, it is not necessary.  Many people are passionate, about this subject, but I am only speaking from the vantage point of our medical system.

I would handle the SO as a separate issue; thus, allowing more flexibility in its design. 

Have a great weekend Larry..

Steve,

Thanks for the input. I agree the integer identify is probably the best way to go since it keeps things simple.  For the applications I’m involved with I don’t need the additional flexibility that using a GUID would provide. Since I have to generate unique ID’s that will be exposed to the user, I was just questioning why not just use that instead of the integer identity as the PK or somehow use the integer identity to generate the ID’s exposed to the users. However keeping them completely separate probably provides the most flexibility in the long run.

-Larry