[AccessD] Access and SQL Server

Shamil Salakhetdinov shamil at smsconsulting.spb.ru
Fri Mar 4 16:31:06 CST 2011


Hi Asger --

<<<
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"?
>>> 
I do not care how to call a surrogate key - a "physical PK" is OK with me. :)
As I noted I do care more on "consistent data modeling" when all and every table gets surrogate PK - as you and John do - I do the same, and I did have to get and to fix legacy data models "nightmares" with five levels hierarchies of natural keys propagating from upper to the lower level of that hierarchy...

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 г. 1:11
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Access and SQL Server

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





More information about the AccessD mailing list