[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