[AccessD] Access and SQL Server

Shamil,

<<Let me note that in the case of unique rows "maximal (length) logical PK"
would be combination of all columns' values of a row.>>

  Yes, that would be what is referred to as a "supper key"; any combination
of one or more attributes that can form a unique combination is a super key.
Out of that group comes the candidates and then out of that group, one is
chosen as a primary key. That candidate being the one which is as minimal as
possible, as familiar as possible, and as stable as possible.

Jim.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Shamil
Salakhetdinov
Sent: Saturday, March 05, 2011 06:37 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Access and SQL Server

Hi Asger --

<<<
When designing a table with unique rows you can't just 
add a surrogate PK key (a "physical PK"). If you don't 
have a natural column or combination of natural columns 
which are unique (a "logical PK" or "natural alternate key")
then the table won't be in 1NF.
>>>
Let me note that in the case of unique rows "maximal (length) logical PK"
would be combination of all columns' values of a row.

I suppose I'm in your and JC's camp - let me be in? :)

Thank you.

--
Shamil
 
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Asger Blond
Sent: 5 марта 2011 г. 3:52
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Access and SQL Server

Stuart (and Shamil)

Disagree. It's not just a matter of words - it's exactly a matter of
words...

Distinguishing between a logical and a physical PK makes clear which natural
columns or combination of natural columns uniquely identifies each row in
the table (the "logical PK") as opposed to a surrogate unique column (the
"physical PK"), both of which should be present in every table.
When designing a table with unique rows you can't just add a surrogate PK
key (a "physical PK"). If you don't have a natural column or combination of
natural columns which are unique (a "logical PK" or "natural alternate key")
then the table won't be in 1NF.
My point is to avoid misunderstanding when talking about PK's. From a
logical point of view you always need to have one or a combination of more
natural columns in the table which uniquely identifies each record. This is
the "logical PK". You really always need this!
But that doesn't mean that you should implement this as the actual
("physical") PK. For other reasons (i.e. performance) it may be prudent to
add a surrogate auto-increment column and make this the actual ("physical")
PK.
When planning a database with customers I have learned to keep my mouth shut
telling that I use surrogate keys. If the customer identifies ProductNumber
as the primary key in a Products table I don't say: sorry for this I'll use
an extra surrogate ProductID column as PK. Why? Because saying this would
confuse two quite different languages. The customer is actually quite right:
ProductNumber is the PK in the "logical design language". My surrogate
ProductID is the PK in the "physical design language".
The customer don't need to know my technical reasons for choosing a
surrogate PK and this doesn't mean that the customer is wrong when calling
the natural ProductNumber a PK. It certainly is a PK - in the logical sense.
And don't underestimate logic...

Asger

<<< snip >>


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com