Jim Dettman
jimdettman at verizon.net
Mon Mar 7 08:29:52 CST 2011
Asger, Sorry about being MIA on this, but I had some major systems work to perform at an out of town client this past weekend, so time was very limited Friday, Saturday, and Sunday. <<But what happens if you need to create a child table to this table?>> That's the point where you'd tack on an auto number, but I don't agree with a design where you do that up front just for the sake of consistency. Even with the extended/non-simple linking table that Stuart brought up (which I don't think of as a linking table even though he was correct in that it really is one), I would not use an auto number as a key until I needed to use it as a FK some where. Jim. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Asger Blond Sent: Thursday, March 03, 2011 06:46 PM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Access and SQL Server Jim, Coming in on this discussion late and having read the whole posting so far I want to take up a point way back: Why impose the overhead of using a surrogate PK in a linking table instead of just using a composite natural PK? JC has clearly stated that using a surrogate PK key doesn't mean that you can omit a unique index on some other "natural column" or combination of "natural columns" in your table (which is also called "alternate keys"). So in your example, even if I use a surrogate PK I also need a natural unique index of the combination AuthorID and BookID. I think we all can agree on this. I also think we all can agree that the surrogate PK imposes an overhead compared to just using the composite natural key as a PK. But what happens if you need to create a child table to this table? Then the story is quite different: using a surrogate PK in the main table you only need a FK with a single column in the child table - using a natural composite PK in the main table you need a FK with as many columns as used in the main table. And this certainly imposes a much bigger overhead. Also to get a good performance you normally will create indexes on the FK. So having a composite FK will impose even more overhead. Not to mention that if you need one child table then chances are that you might need two or more child tables - each one imposing an overhead as compared to using a surrogate key with one column. That's why I always use surrogate PK's - even in a linking table which *for the moment* doesn't seem to need child tables. Asger -----Oprindelig meddelelse----- Fra: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] På vegne af Jim Dettman Sendt: 1. marts 2011 21:23 Til: 'Access Developers discussion and problem solving' Emne: Re: [AccessD] Access and SQL Server Lambert, It needs a unique index, which the Author/Book combination provides. Not sure why the original post got formatted that way, but it should have looked like 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 Which might be clearer. Jim. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Heenan, Lambert Sent: Tuesday, March 01, 2011 01:50 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Access and SQL Server Doesn't SQL server require a field with a unique index in order for the table to be updatable? AuthorID cannot be unique (obviously) BookID might not be unique (as some books have multiple authors) So don't you need LinkID - Autonumber PK? Lambert -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman Sent: Tuesday, March 01, 2011 1:40 PM 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 -- 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