Prev: SQL Server Ranking: Count Consecutive Values / Reset
Next: Collecting data...!
From: --CELKO-- on 14 Jul 2010 08:08
>> When it comes to UPDATE, well, if someone wants to change the id
for the "shopkeepers" customer category from 13 to 234, no big deal.
....unless there is a table where this value appears in millions and
millions of rows. Personally, I am of the position that PKs should be
immutable, which makes the question moot. <<

A few years ago, the US changed from UPC to EAN bar codes on all
retail items, from ISBN-10 to ISBN-13 and the VIN is about to be
expanded. There was no choice in those hundreds of millions of cases
of the most widely used nautural keys on earth.

So I don't mind mutable keys, but it takes a lot planning to do it
right.




From: Eric Isaacs on 14 Jul 2010 13:33
Keys, whether natural or surrogate that are exposed to users
eventually need to change by necessity. UPC to EAN is a prime example
of that. License plates are yet another. Social Security Numbers,
yet another.

-Eric Isaacs
From: Tony Rogerson on 15 Jul 2010 02:52
> Keys, whether natural or surrogate that are exposed to users

By definition a surrogate key can NEVER change, that's their purpose.

In my part one on surrogates I list some of the rules that Codd, Date and
others in that area have defined.

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2010/03/13/database-design-surrogate-keys-part-1-of-many-rules-for-surrogate-keys-e-f-codd-and-c-j-date-research-and-problems-they-solve.aspx

1.They must never be related (directly or indirectly) to the physical
hardware or storage - to use the Relational phrase - they are not tuple-id's
(row/record locators).
2.Once a value has been used - it must never be reused by something else -
basically once the value "5" has been used, then the surrogate key generator
must never generate the number "5" again even if the original row has been
deleted.
3.They are read only - once generated that value must never be changed.
4.The value must be atomic, that means no sets - just a constant for
instance "5".
5.The value should never be exposed outside the limits of the application as
a whole (the database is not a black box) - I explain more later but
basically the User of your application for instance the Call Centre staff
entering client details, the external machine calling your web service
should never see the surrogate key value (they should see one of the
candidate key values instead) - that is because the surrogate key cannot be
used for verification purposes but the candidate key can.
6.A surrogate key can be the sole key for a table in a situation where there
are no natural occurring candidate keys for example a Message Board. When
used in this scenario arguably it's no longer a surrogate key but an
artificial one but I would still follow rules 1 - 4. Having said that you
need to consider verification for instance if the rows were re-inserted and
the artificial key re-generated then the same rows (entities) should get the
same artificial key value; there are hashing solutions to provide that
facility but the obvious problem of duplicate rows come into play - that
discussion is outside the scope of surrogate keys.

It's why we use surrogates in the source systems, they protect us from the
natural key changing.


"Eric Isaacs" <eisaacs(a)gmail.com> wrote in message
news:75f43ed7-bc43-4a10-ac59-92f3fe166c3e(a)u38g2000prh.googlegroups.com...
> Keys, whether natural or surrogate that are exposed to users
> eventually need to change by necessity. UPC to EAN is a prime example
> of that. License plates are yet another. Social Security Numbers,
> yet another.
>
> -Eric Isaacs

From: Eric Isaacs on 15 Jul 2010 13:21
Tony,

I don't disagree with anything you've written. My point was that if
you take a surrogate key and expose it, even within the same
application, chances are that users will start using it outside of the
system (and by your definition, it is no longer a surrogate at that
point.) If the users start to use it to reference the data outside of
the system, that can lead to requests later on to change or merge
those keys with keys from other systems. I'm very much in favor of
surrogate keys, but do not agree with exposing those keys to the
users, even within the same system because of the high potential to
receive requests to change those surrogates into something else.

-Eric Isaacs
From: Tony Rogerson on 15 Jul 2010 13:48
Totally agree - the scope of the surrogate key visibility is with the
plumbing of the system - so the developers writing the apps can use it (but
don't see it).

Users never see it at all.

Tony

"Eric Isaacs" <eisaacs(a)gmail.com> wrote in message
news:21930a36-4e1a-4e60-9ef3-5331f3ca5c2b(a)s24g2000pri.googlegroups.com...
> Tony,
>
> I don't disagree with anything you've written. My point was that if
> you take a surrogate key and expose it, even within the same
> application, chances are that users will start using it outside of the
> system (and by your definition, it is no longer a surrogate at that
> point.) If the users start to use it to reference the data outside of
> the system, that can lead to requests later on to change or merge
> those keys with keys from other systems. I'm very much in favor of
> surrogate keys, but do not agree with exposing those keys to the
> users, even within the same system because of the high potential to
> receive requests to change those surrogates into something else.
>
> -Eric Isaacs

First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: SQL Server Ranking: Count Consecutive Values / Reset
Next: Collecting data...!