Stuart McLachlan
stuart at lexacorp.com.pg
Fri Mar 4 20:04:39 CST 2011
You'll notice that I have changed the subject!
D,RFC = Ducking, Running For Cover!
I knew as soon as I saw the first posting that it would end up as another round of the great
surrogate/natural PK debate that somehow comes up every year or so on the list with no-
one's opinions being changed. I really didn't want to get dragged into it again
--
Stuart
On 5 Mar 2011 at 2:28, Asger Blond wrote:
> Stuart,
> Worried, are you feeling well? Don't understand a word of your mumble.
> Asger
>
> -----Oprindelig meddelelse-----
> Fra: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] På vegne af Stuart
> McLachlan Sendt: 5. marts 2011 02:09 Til: Access Developers discussion
> and problem solving Emne: Re: [AccessD] Access and SQL Server
>
> 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
>
>
>
>
> --
> 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