Should I use a unique string as a primary key or should I make it as a separate autoincrement INT? [duplicate]

Question

Possible Duplicates:
Surrogate Vs. Natural/Business Keys
When not to use surrogate primary keys?

So, I would prefer having an INT as a primary key with AI. But, here is the reason why I am considering making a unique string as a primary key: I don't have to query related tables to fetch the primary key since I will already know it.

For example:
I have a many to many relation:

Customer - Order - Product

Let's say I want to add a new customer and a new order, and I already know what they bought. I have to do a query on product table to get the INT, but if I have the string (unique) that is a primary key I don't have to do the query (this seems cleaner to me, I am not talking about optimization/run-time-speeds, not at all).

Answer 1

If you are not worried about optimization, the main 2 criteria for primary key are:

• Uniqueness

• Constanteness (never changes)

E.g. if your problem domain is - and will always be - such that 2 product names are always distinct, AND that no product will ever change its name (think Norton Antivirus -> Symantec Antivirus for a simple example of name change), then you may use the product name as the unique key.

The two MUST be 100% true not only today, but for any foreseeable future lifetime of the database.

Therefore using numeric ID is highly recommended as you may not always be able to forecee such things - and changing the DB structure later on to have a product ID is of course orders of magnitude worse than a minor inconvenience of needing to map and ID from the name in your queries.