Re: Surrogate Key

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Tony Rogerson (tonyrogerson_at_sqlserver.eu.com)
Date: 01/06/05 

Date: Thu, 6 Jan 2005 15:52:53 -0000

>>>>Being applicable at the logical level, surrogate keys have nothing by
>>>>themselves associated with performance, contrary to popular perception.

Mmmmm - i beg to differ Anith.

I use the good old IDENTITY property on columns definied as int for my
surrogate (artificial) keys and then use those id's in drop downs and keys
within the application outside the database pure and simply for performance
and easy of use, composite keys are painful and not really practical when
used as the value property on the listbox object for instance.

I think the biggest mistake people make is not treating the database as part
of the application, it is quite often treat as seperate entity.

More scalable, more matainable and quicker developed applications can be
built if application and database work together.

Draw backs of using primary keys are varied, two of the most important in my
mind is that changing the primary key is very difficult without using a
surrogate and the second is what i've mentioned about re: performance and
use of composite keys.

Should start a good debate ;) 

-- 
Tony Rogerson
SQL Server MVP
http://www.sqlserverfaq.com?mbr=21
(Create your own groups, Forum, FAQ's and a ton more)

Relevant Pages

  • Re: Database design, Keys and some other things
    ... >> Or 'the database has no opinion as what Donald Trump's e-mail address might ... some keys can be wrong or a data can ... Meaning is not related to just one number. ... > is concerned a VIN is not a surrogate key, ...
    (comp.databases.theory)
  • Re: Whats the best practice for primary keys?
    ... The idea behind surrogates is introduced not by Codd, but by Hall, Owlett & ... the surrogate values) and an e-relation (the corresponding relation with the ... The need for logical surrogates in relational databases is genuine; ... compound keys have missing values in part of the referencing columns. ...
    (microsoft.public.sqlserver.programming)
  • Re: How to ID a record
    ... Your questions go to the heart of a "natural" primary keys and "surrogate" ... Surrogate keys are usually autonumber or other ID type fields. ... how many fields it takes to compose the "natural" key and what I'm doing ... My database has a table called Employees, it has fields called "id" "fname" ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Access Many to Many relationship
    ... Base the main form on TblProject and ... base the subform on TblProjectEmployee. ... key autonumber for join tables along with composite keys. ... In our shop we use surrogate Autonumber/Identity keys almost ...
    (microsoft.public.access.tablesdbdesign)
  • Re: A real world example
    ... Design criteria for choice of candidate keys include: familiarity, irreducibility, simplicity and stability. ... I disagree that the concept of surrogate vs. natural is useful. ... Twenty years ago there was some discussion of surrogate keys vs. natural keys, but further reflection reveals that natural keys are nothing more or less than familiar surrogates. ... There really is little more to discuss about natural keys vs. surrogates except that self-aggrandizing ignorants periodically appropriate the terms in the nonsense they spout. ...
    (comp.databases.theory)