[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

-----Oprindelig meddelelse-----
Fra: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] På vegne af Stuart McLachlan
Sendt: 4. marts 2011 23:43
Til: Access Developers discussion and problem solving
Emne: Re: [AccessD] Access and SQL Server

Sorry, but IMNSHO a PK is just a PK.

You can use a surrogate(physical) key or combined natural(logical) key for that, but in the 
end, either one is "the" PK.   There is no need to differentiate according to what the  key is 
based on.

-- 
Stuart

On 4 Mar 2011 at 23:10, Asger Blond wrote:

> Hi Shamil
> 
> In another posting you wrote:
> > Isn't it time now to recapitulate constructively this discussion and
> > to list pedantically pros and cons of every approach? Anybody?
> 
> Maybe it would be constructive to use the established distinction
> between "logical design" and "physical design". This might clear up
> some of the mismatch between Jim and John. From a logical design point
> of view the combination of AuthorID and BookID forms the PK. But from
> a physical point of view this PK may be implemented by a surrogate
> auto-increment key. So would all be happy if John (and I) calls the
> surrogate key a "physical PK", admitting that this key points to the
> combined natural key which then should be named a "logical PK"?
> 
> Asger
> 
> 
> -----Oprindelig meddelelse-----
> Fra: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] På vegne af Shamil
> Salakhetdinov Sendt: 4. marts 2011 01:27 Til: 'Access Developers
> discussion and problem solving' Emne: Re: [AccessD] Access and SQL
> Server
> 
> Hi Asger --
> 
> <<<
> That's why I always use surrogate PK's - even in a linking table which
> *for the moment* doesn't seem to need child tables. >>> Yes, that is
> what I call "data model design consistency principle" I'm applying to
> all my data models. Overheads of "fake" surrogate PK for pure
> relation/linking tables is not so big, and gains are many...
> 
> Thank you.
> 
> --
> Shamil
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Asger Blond
> Sent: 4  2011 . 2:46 To: 'Access Developers discussion and
> problem solving' Subject: Re: [AccessD] Access and SQL Server
> 
> Jim,
> Coming in on this discussion late and having read the whole posting so
> far I want to take up a point way back: Why impose the overhead of
> using a surrogate PK in a linking table instead of just using a
> composite natural PK? JC has clearly stated that using a surrogate PK
> key doesn't mean that you can omit a unique index on some other
> "natural column" or combination of "natural columns" in your table
> (which is also called "alternate keys"). So in your example, even if I
> use a surrogate PK I also need a natural unique index of the
> combination AuthorID and BookID. I think we all can agree on this. I
> also think we all can agree that the surrogate PK imposes an overhead
> compared to just using the composite natural key as a PK. But what
> happens if you need to create a child table to this table? Then the
> story is quite different: using a surrogate PK in the main table you
> only need a FK with a single column in the child table - using a
> natural composite PK in the main table you need a FK with as many
> columns as used in the main table. And this certainly imposes a much
> bigger overhead. Also to get a good performance you normally will
> create indexes on the FK. So having a composite FK will impose even
> more overhead. Not to mention that if you need one child table then
> chances are that you might need two or more child tables - each one
> imposing an overhead as compared to using a surrogate key with one
> column. That's why I always use surrogate PK's - even in a linking
> table which *for the moment* doesn't seem to need child tables.
> 
> Asger
> 
> <<< snip >>
> 
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com




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