Darryl Collins
Darryl.Collins at iag.com.au
Wed Mar 2 17:01:15 CST 2011
_______________________________________________________________________________________ Note: This e-mail is subject to the disclaimer contained at the bottom of this message. _______________________________________________________________________________________ haha "become a new "great debate"". That was pretty much what I was thinking. Just when things around here had settled down on the bound / unbound skirmishes. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Shamil Salakhetdinov Sent: Thursday, 3 March 2011 2:13 AM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Access and SQL Server Jim -- That our "'consistent' vs. 'inconsistent'" data modeling discussion promise to become a new "great debate" as "bound vs. unbound" and similar debates... Real world is inconsistent or consistent? - no answer? many answers? Should data modeling practice follow real world inconsistency (consistency?) or not? Your and others choice now is to start "consistent vs. inconsistent" thread to find constructive answers when it's worth following and when it's not - for sure there will be no definitive one answer as a result of such a discussion still it could be rather useful... Start it? Yes/No? 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 ?. 16:04 To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Access and SQL Server 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 -- 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 _______________________________________________________________________________________ The information transmitted in this message and its attachments (if any) is intended only for the person or entity to which it is addressed. The message may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon this information, by persons or entities other than the intended recipient is prohibited. If you have received this in error, please contact the sender and delete this e-mail and associated material from any computer. The intended recipient of this e-mail may only use, reproduce, disclose or distribute the information contained in this e-mail and any attached files, with the permission of the sender. This message has been scanned for viruses. _______________________________________________________________________________________