Edward Zuris
edzedz at comcast.net
Wed Mar 2 11:48:58 CST 2011
My pleasure. . . Have to run, and get back to my can of worms. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Shamil Salakhetdinov Sent: Wednesday, March 02, 2011 10:00 AM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Access and SQL Server Hi Edward, Thank you for your note. <<< It seems many projects start out 'consistent' then become 'inconsistent'. >>> Refactoring is used to keep a project consistent/get a project back into a consistent state. See also "Technical Debt" (http://en.wikipedia.org/wiki/Technical_debt) related books, articles, discussions... and how to handle that "technical debt" to keep one's project consistent... In my experience approx. 20% of a project time is needed to keep it consistent. That "additional" efforts do pay back a lot in long run. And if one will keep a "technical debt" growing they will get "technically bankrupt" sooner or later. One (a seasoned developer) should probably better not inform their customers when and how they keep their project "technical debt" work performed but for a "good" customer with long lasting relations such a "secret" should be opened... Thank you. -- Shamil -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Edward Zuris Sent: 2 ????? 2011 ?. 18:57 To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Access and SQL Server Go for it. . . . It seems many projects start out 'consistent' then become 'inconsistent'. A great debate about consistent vs inconsistent might be more profitable than discussing the person in the street reacting to the Banksters losing all the world's money. It seems only a few nations are immune to such. Well I better get back to work straightening out my once 'consistent' project. . . -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Shamil Salakhetdinov Sent: Wednesday, March 02, 2011 8: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