Path: news.easynews.com!easynews!cyclone.swbell.net!cyclone-sf.pbi.net!64.245.249.51!sfo2-feed1.news.algx.net!jfk3-feed1.news.algx.net!allegiance!news.maxwell.syr.edu!fu-berlin.de!uni-berlin.de!public1-leds2-5-cust57.leed.broadband.ntl.COM!not-for-mail From: "Tobin Harris" Newsgroups: comp.databases.oracle.misc,comp.databases.theory,comp.databases.ms-sqlserver,microsoft.public.sqlserver.programming Subject: Re: Normalization, Natural Keys, Surrogate Keys Date: Thu, 16 May 2002 23:13:31 +0100 Lines: 35 Message-ID: References: NNTP-Posting-Host: public1-leds2-5-cust57.leed.broadband.ntl.com (80.0.35.57) X-Trace: fu-berlin.de 1021587070 22819698 80.0.35.57 (16 [135366]) X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2600.0000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 Xref: easynews comp.databases.oracle.misc:81721 comp.databases.theory:20807 comp.databases.ms-sqlserver:72045 microsoft.public.sqlserver.programming:238326 X-Received-Date: Thu, 16 May 2002 15:08:43 MST (news.easynews.com) > As an aside, I've not seen a clear guide for the use of the terms "surrogate > key" and "natural key" to describe what are really four kinds of keys -- > keys for internal use by the dbms, keys which are declared in the data model > but not used by applications (other than for joining) and are unknown to the > outside world, keys which are generated but externally used (account #s, > invoice #s, etc), and keys which are natural attributes of the entity being > modeled (from names to DNA). I've been using "surrogate" to represent the > second of these and "natural" to cover the third and fourth. I'm not sure if I'm right here, but I've got a feeling that surrogate keys tend to become natural keys once they start getting used by the business. Most natureal keys I see are unique numbers/alpha-numeric sequences, which makes me wonder whether they started out as surrogate keys. I've recently worked on a project where a developer had assigned surrogate keys to all entities. One entity actually already had a 'real world' (or natural ) key, but this was ignored. Within 2 weeks of working with the client, we now have to clarify whether we're talking about the original 'natural' key, or the newly created 'surrogate' key. IMHO this is a nightmare. A 2 minute design decision is costing us 5 seconds each time we talk about business entites - just clarifying keys. To me this is unwelcomed complexity, to which there is little gain. I think the point is to think about your surrogate keys carfully, not just assign them as habit (which many do). I believe that if you can find an existing natural key, then use it! Tobin Harris