[AccessD] Access and SQL Server

Jim Dettman jimdettman at verizon.net
Mon Mar 7 08:29:52 CST 2011


Asger,

  Sorry about being MIA on this, but I had some major systems work to
perform at an out of town client this past weekend, so time was very limited
Friday, Saturday, and Sunday.

  <<But what happens if you need to create a child table to this table?>>

  That's the point where you'd tack on an auto number, but I don't agree
with a design where you do that up front just for the sake of consistency.

  Even with the extended/non-simple linking table that Stuart brought up
(which I don't think of as a linking table even though he was correct in
that it really is one), I would not use an auto number as a key until I
needed to use it as a FK some where.

Jim.   

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Asger Blond
Sent: Thursday, March 03, 2011 06:46 PM
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


-----Oprindelig meddelelse-----
Fra: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] På vegne af Jim Dettman
Sendt: 1. marts 2011 21:23
Til: 'Access Developers discussion and problem solving'
Emne: Re: [AccessD] Access and SQL Server

Lambert,

 It needs a unique index, which the Author/Book combination provides.

 Not sure why the original post got formatted that way, but it should have
looked like this:

tblBooksAndAuthors
LinkID - Autonumber - PK
AuthorID - Long - FK to tblAuthors - CK-A
BookID - Long - FK to tblBooks - CK-B

And not simply:

tblBooksAndAuthors
AuthorID - Long - FK to tblAuthors - PK-A
BookID - Long - FK to tblBooks - PK-B

  Which might be clearer.

Jim.
 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Heenan, Lambert
Sent: Tuesday, March 01, 2011 01:50 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Access and SQL Server

Doesn't SQL server require a field with a unique index in order for the
table to be updatable?

AuthorID cannot be unique (obviously)
BookID might not be unique (as some books have multiple authors)

So don't you need LinkID - Autonumber PK?

Lambert

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman
Sent: Tuesday, March 01, 2011 1:40 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Access and SQL Server


 So on a many to many linking table you would do this:

tblBooksAndAuthors
LinkID - Autonumber - PK
AuthorID - Long - FK to tblAuthors - CK-A BookID - Long - FK to tblBooks -
CK-B

And not simply:

tblBooksAndAuthors
AuthorID - Long - FK to tblAuthors - PK-A BookID - Long - FK to tblBooks -
PK-B

 and eliminate an index?  If so, why not?

Jim.
 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Tuesday, March 01, 2011 12:47 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Access and SQL Server

When I create a table in any datastore, the first thing I do is create an
autoincrement PK.  I no longer even think about it - if need a table, I need
an autonumber pk!

I then proceed to create the fields.

John W. Colby
www.ColbyConsulting.com

On 3/1/2011 12:17 PM, Jim Lawrence wrote:
> Many years ago I was taking over an Access project as the clients were 
> having problems with their invoices. After about two days I discovered 
> the problem with the invoice.
>
> It appears that the subform was connected to the main form by grouping 
> together 3 fields, creating natural foreign key between the two 
> tables. By some odd set of bad luck certain combinations of this key 
> hash matched another unrelated key value and the sub form data was 
> pulling from
multiple
> invoice details.
>
> The only reliable solution was to move all the tables to auto PKs but 
> it cost the client a fair chunk of change. For that reason I have 
> never inflicted natural keys, on a client, no matter how strong the
temptation.
>
> Jim
>
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart
McLachlan
> Sent: Monday, February 28, 2011 3:00 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Access and SQL Server
>
> I see a lot of sense in it having a separate Autonumber PK.   This is a
> classic case of why
> you should not use a natural key as your PK.
>
> What happens when the Description changes and the existing Code is no
longer
> an accurate
> short representation of Description?  Do you change it throughout all 
> the tables which store it or do you leave your customer with strange 
> Codes which don't match the description
>
> (And please don't tell me that you use Relationships with "Cascade Update"
> turned on.)
>
>
--
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





More information about the AccessD mailing list