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