[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 >>