[AccessD] Access and SQL Server

Jim Dettman jimdettman at verizon.net
Wed Mar 2 07:04:14 CST 2011


Shamil,

  Don't loose sight of the fact that I was specifically talking about a many
to many linking table.

  All the attributes your talking about below would belong in the books
table.  As this is simply a many to many linking table, no other fields
would ever be added to it.

 I have never found a need to point to a record in a many to many table
either, so adding a auto number field just for the reason of "that's always
the way I do it" or "because it's consistent", I think is a waste.

 If this happened to involve tables with millions of records, the addition
of another index would mean a pretty sizeable performance hit.

Jim.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Shamil
Salakhetdinov
Sent: Wednesday, March 02, 2011 03:13 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Access and SQL Server

Hi Jim -- 

<<<
<<If not - be prepared that inconsistent data model design will bring you a
lot of troubles in long run.>>
  Have never had a problem yet...
>>>
Lucky man :)

In your sample case with tblBooksAndAuthors the relation table's compound
index (AuthorID, BookID) could become non-unique, e.g. when books will get
[Edition] field introduced. 
And you'll have to decide where to put [Edition] (+ [CopyrightDate], ...)
fields - into [tblBooksAndAuthors] or into [tblBook] table or into new
[tblBookEditions] table.
Then you'll have to investigate all your Queries: Joins, Group Bys, ... and
related Forms, Reports, VBA code (if any) to see how [Edition] field
introduction influenced them. 

Yes,  in the case when  [tblBooksAndAuthors] uses dedicated Autonumber field
[LinkId] , introduction of [Edition] field would also require to decide in
what table to put the new [Edition] field and would also require
reinvestigation/refactoring of (some of) Queries, Forms, Reports, VBA code
but all the work in that case would be "routine & mechanical".

Your customer might also decide (one fine day) that having just one author
for a book isn't a "good idea" and that introduction of several authors for
a book is a "simple change request"...

Thank you.

--
Shamil
 
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman
Sent: 2 ????? 2011 ?. 3:21
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Access and SQL Server

Shamil,

<<If not - be prepared that inconsistent data model design will bring you a
lot of troubles in long run.>>

  Have never had a problem yet...

Jim.

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

Hi Jim --
 
<<<
 and eliminate an index?  If so, why not?
>>>
In my opinion good data model should be consistent - therefore all and every
table's PK should be an Autonumber/Identity column, even linking/relation
tables.
If not - be prepared that inconsistent data model design will bring you a
lot of troubles in long run.

--
Shamil
 
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman
Sent: 1 ????? 2011 ?. 21:40
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




More information about the AccessD mailing list