[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 >>