[AccessD] Access and SQL Server

Fri Mar 4 19:09:01 CST 2011

B*gger,

I've just realised that JC and Jim managed to sucker me in to this debate after all.

I should have left it at my first posting on this subject, which was the very succint

<quote>
D,RFC :-)
</quote>

And that stands as my last comment on this thread too!

-- 
Stuart

On 5 Mar 2011 at 1:52, Asger Blond wrote:

> 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
> 
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com