Prev: SQL Server Ranking: Count Consecutive Values / Reset
Next: Collecting data...!
From: Eric Isaacs on 15 Jul 2010 13:56
Tony,

I just read over your article. It's a great article. I did find it
interesting that Codd and Date seemed to disagree on whether surrogate
keys should be exposed to the users.

Date says, "Surrogates must not be concealed from the user because of
the Information Principal." where he's referring to Codd's
Information Principle.

Codd says, "Database users may cause the system to generate or delete
a surrogate, but they have no control over its value, nor is its value
ever displayed to them."

My feeling is that it's okay to expose a surrogate key if there is no
other candidate key to expose, but in doing so you risk the chance
that it will be required to change in the future. But in that case,
you would just create a new candidate field and maintain your
surrogate key separate from your new candidate key and expose that new
field to the users instead of the surrogate key.

-Eric Isaacs
From: Erland Sommarskog on 15 Jul 2010 17:50
Tony Rogerson (tonyrogerson(a)torver.net) writes:
> 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.

Oh, if it always was that well! In the system I work with, surrogate
keys are often exposed, because developers don't know better.

Recently, I reviewed a change specificiation, which said that in an
certain functon, the surrogate id should be displayed, because the
user for reason needed to know this. (It had something to do with
integration with another system that the customer has.)


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: Eric Isaacs on 15 Jul 2010 18:18
> Recently, I reviewed a change specificiation, which said that in an
> certain functon, the surrogate id should be displayed, because the
> user for reason needed to know this. (It had something to do with
> integration with another system that the customer has.)

Yes, if it's for integration with other systems, that's when another
candidate key or a GUID should be used instead of a surrogate (INT
IDENTITY) primary key. The GUID can be displayed or used for
reference, though it's not very user friendly. I would still opt to
keep the primary key as the INT IDENTITY column even if a GUID was
also used as a candidate key.

-Eric Isaacs
From: Tony Rogerson on 16 Jul 2010 03:26
Its no longer a surrogate and its artificial in that case.

If we start exposing them to the business then they attract changeability
because of business merging etc... and that breaks the rules and purpose of
surrogate keys.

Semantics I know but its important.

Tony.

"Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message
news:Xns9DB6F29027C93Yazorman(a)127.0.0.1...
> Tony Rogerson (tonyrogerson(a)torver.net) writes:
>> 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.
>
> Oh, if it always was that well! In the system I work with, surrogate
> keys are often exposed, because developers don't know better.
>
> Recently, I reviewed a change specificiation, which said that in an
> certain functon, the surrogate id should be displayed, because the
> user for reason needed to know this. (It had something to do with
> integration with another system that the customer has.)
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000:
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
From: Eric Isaacs on 16 Jul 2010 15:43
I suppose that's like using a surrogate mother; if you tell anyone
you're using one and who it is, everyone will know it's actually
artificial.

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