[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