Let's begin again, that old argument still arises...

Would we better possess a business key like a primary key, or would we favour a surrogate id (i.e. an SQL Server identity) having a unique constraint around the business key area?

Please, provide good examples or proof to aid your theory.

Only a couple of causes of maintaining surrogate secrets:

1) Stability: Altering a vital due to a company or natural need will adversely affect related tables. Surrogate secrets rarely, when, have to be transformed because there's nothing associated with the worthiness.

2) Convention: Enables you to possess a standardized Primary Key column naming convention instead of needing to consider how you can join tables with assorted names for his or her PKs.

3) Speed: With respect to the PK value and kind, a surrogate key of the integer might be more compact, faster to index and check.

Both. Have your cake and eat it.

Remember there's nothing special in regards to a primary key, except that it's labelled as a result. It's simply a NOT NULL UNIQUE constraint, along with a table might have several.

If you are using a surrogate key, you'll still desire a business answer to ensure originality based on the business rules.

It seems that nobody has yet stated anything for non-surrogate (I hesitate to express "natural") secrets. Here goes...

A disadvantage of surrogate secrets is they are meaningless (reported being an advantage by some, but...). This sometimes can make you join much more tables to your query than should certainly be necessary. Compare:

choose sum(t.hrs)

from timesheets t

where t.dept_code = 'HR'

and t.status = 'VALID'

and t.project_code = 'MYPROJECT'

and t.task = 'BUILD'


choose sum(t.hrs)

from timesheets t

     join departents d on d.dept_id = t.dept_id

     join timesheet_statuses s on s.status_id = t.status_id

     join projects p on p.project_id = t.project_id

     join tasks k on k.task_id = t.task_id

where d.dept_code = 'HR'

and s.status = 'VALID'

and p.project_code = 'MYPROJECT'

and k.task_code = 'BUILD'

Unless of course anybody seriously thinks the next may be beneficial?:

choose sum(t.hrs)

from timesheets t

where t.dept_id = 34394

and t.status_id = 89

and t.project_id = 1253

and t.task_id = 77

"But" someone will say, "what goes on once the code for MYPROJECT or VALID or HR changes?" That my answer could be: "why can you need to alter it?" These bankruptcies are not "natural" secrets meaning that some outdoors body will legislate that henceforth 'VALID' ought to be re-coded as 'GOOD'. Only a tiny proportion of "natural" secrets really fall under that category - SSN and Zipcode being the typical good examples. I'd certainly make use of a meaningless number key for tables like Person, Address - although not for everything, which for whatever reason many people here appear to advocate.

See also: my response to another question