Access 2010 beta - Publish to Sharepoint
in [Help]
|
Prev: Keeping A Citrix Session Alive?
Next: Help in A2010
From: James A. Fortune on 24 Nov 2009 09:48 On Nov 23, 2:45 pm, "David W. Fenton" <XXXuse...(a)dfenton.com.invalid> wrote: > I have dozens of such tables in my apps. Doesn't everyone? I have plenty of junction tables. I typically have an AutoNumber PK and two indexed Foreign Key fields. When I add new combinations to the junction table, I use a form that checks for existing combinations (if I even allow existing combinations to show up at all). The indexed Foreign Key fields seem adequate for speeding up queries, although most of the junction tables I use don't get very large. So I don't perceive a "major, major" lack. YMMV. Here's an example: tblTravelClubMembers TID AutoNumber MemberName Text TID MemberName 1 Jane 2 Joe 3 Jill tblDestinations DID AutoNumber DestinationCity Text DID DestinationCity 1 Paris 2 Rome 3 Albuquerque tblClubMemberHasVisited CMHVID AutoNumber CID Long (Foreign Key) DID Long (Foreign Key) CMDVID CID DID 1 1 1 2 2 1 3 1 2 4 1 3 5 2 3 qryHasBeenTo SELECT MemberName, DestinationCity As HasBeenTo FROM (tblTravelClubMembers INNER JOIN tblClubMemberHasVisited ON tblTravelClubMembers.TID = tblClubMemberHasVisited.TID) INNER JOIN tblDestinations ON tblClubMemberHasVisited.DID = tblDestinations.DID ORDER BY tblTravelClubMembers.TID, tblDestinations.DID; !qryHasBeenTo MemberName HasBeenTo Jane Paris Jane Rome Jane Albuquerque Joe Paris Joe Albuquerque The primary keys from the related tables plus the two indexed fields in the junction table adequately index all the joins in qryHasBeenTo. I suppose that an AutoNumber primary key is not actually necessary in the junction table. I see little value in using a compound primary key for the junction table unless it is being edited directly and you want to prevent the user from accidentally duplicating a combination. BTW, a many-to-many relationship corresponds to the formal definition of a Relationship in Mathematics, but I like to point out that, under normal circumstances, the relationship is between the keys rather than between the data. I.e., the junction table is where the actual mathematical relationship between the keys is defined. It is not until the query is run that the data pointed to by a key becomes related to other data pointed to by other keys. I.e., query results, rather than the records in a junction table, define a mathematical relationship on the data. James A. Fortune CDMAPoster(a)FortuneJames.com My dad took pool lessons from pro player Buddy "the Rifleman" Hall. He was chosen to play Willie Mosconi in an exhibition match of 14.1 (continuous) and lost to Willie, but routinely beat pros for cash in nineball.
From: David W. Fenton on 24 Nov 2009 20:23 Salad <oil(a)vinegar.com> wrote in news:vM2dnUD5G6QmcJfWnZ2dnUVZ_hmdnZ2d(a)earthlink.com: > David W. Fenton wrote: > >> Salad <oil(a)vinegar.com> wrote in >> news:Od2dneGl3cUl7ZXWnZ2dnUVZ_uSdnZ2d(a)earthlink.com: >> >> >>>David W. Fenton wrote: >>> >>>>"Albert D. Kallal" <PleaseNOOOsPAMmkallal(a)msn.com> wrote in >>>> >>>>>So, to be clear: >>>>>for 2010 you can have many indexes. And as many as those >>>>>columns can be set as unique. >>>>> >>>>>We just don't have a compound index ability. >>>> >>>>And that's a major, major lack. >>>> >>> >>>Could I ask why? I remember using compound indexes back in the >>>days of Dbase and FoxPro but with Access...never. I'm sure >>>there's a reason and a use for them but I wouldn't know where >>>they'd be useful or why. >> >> >> N:N join table, two IDs from the tables you are joining. You want >> a unique index on the combination of the two columns, and, of >> course, it should also be the PK. >> >> I have dozens of such tables in my apps. Doesn't everyone? > > What is a N:N? Many-to-many. Standard terminology, actually, though in Access we see the infinity symbol in its place (ASCII 236, which my newsreader won't let me paste into this message -- it keeps coming out as an 8). > I suppose there's a use for compound indexes and if you have lots > of tables that utilize compound indexes, you have a need. I > haven't found the need but that might be due to my application > logic. If you have many-to-many joins, you *must* have compound indexes, or you're forcing yourself to enforce uniqueness in your application. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/
From: David W. Fenton on 24 Nov 2009 20:34 "James A. Fortune" <CDMAPoster(a)FortuneJames.com> wrote in news:a8dd5ff2-b5de-41ac-b10b-708a9060b9f2(a)f10g2000vbl.googlegroups.co m: > I see little value in using a compound primary > key for the junction table unless it is being edited directly and > you want to prevent the user from accidentally duplicating a > combination. Why bother with RI? You can enforce it in your application? This is a *really* old argument, and you're going to lose it. If the entities are unique, then a standard N:N join record *must* be unique. That is, if there can be only one record joining the two FKs together, then you need a unique compound index. Now, if you have other fields, such that there can be more than one record with any pair of FK values, you've just moved the uniqueness index from a 2-column key to a 3-column key, i.e., the two FK fields plus the third field that has to be unique. For instance, that could be a date field, such that you have only one record joining the two tables for any particular day. The unique index is *required*, not for performance, but for enforcing uniqueness at the engine level. You never leave anything that can be enforced at engine level to the UI level because you can't guarantee that your data is going to be edited by your particular application. A new application might replace yours and fail to note the restriction, with the result that invalid data could be entered. I am stunned that two prolific posters in the Access newsgroups could fail to appreciate such a basic point. It's not even debatable, in my opinion -- if any combination of fields in a table must be unique because of the entities being modelled, you have to enforce that with a unique index, insofar as that's possible (in some cases it isn't, i.e., when you have to have Null fields and default values are going to cause problems). What this means for an Access app that is published to Sharepoint is that the Access app has to be crippled in terms of relaxed data integrity. The reason lack of RI was such a show-stopper in the previous Sharepoint verion was exactly that, i.e., that you couldn't create a Sharepoint app that would enforce the most basic aspects of data integrity. N:N join tables with a unique compound key on the two fields are a basic part of that, and the lack of support for compound keys is a major deficiency. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/
From: Salad on 24 Nov 2009 20:53 David W. Fenton wrote: > Salad <oil(a)vinegar.com> wrote in > news:vM2dnUD5G6QmcJfWnZ2dnUVZ_hmdnZ2d(a)earthlink.com: > > >>David W. Fenton wrote: >> >> >>>Salad <oil(a)vinegar.com> wrote in >>>news:Od2dneGl3cUl7ZXWnZ2dnUVZ_uSdnZ2d(a)earthlink.com: >>> >>> >>> >>>>David W. Fenton wrote: >>>> >>>> >>>>>"Albert D. Kallal" <PleaseNOOOsPAMmkallal(a)msn.com> wrote in >>>>> >>>>> >>>>>>So, to be clear: >>>>>>for 2010 you can have many indexes. And as many as those >>>>>>columns can be set as unique. >>>>>> >>>>>>We just don't have a compound index ability. >>>>> >>>>>And that's a major, major lack. >>>>> >>>> >>>>Could I ask why? I remember using compound indexes back in the >>>>days of Dbase and FoxPro but with Access...never. I'm sure >>>>there's a reason and a use for them but I wouldn't know where >>>>they'd be useful or why. >>> >>> >>>N:N join table, two IDs from the tables you are joining. You want >>>a unique index on the combination of the two columns, and, of >>>course, it should also be the PK. >>> >>>I have dozens of such tables in my apps. Doesn't everyone? >> >>What is a N:N? > > > Many-to-many. Standard terminology, actually, though in Access we > see the infinity symbol in its place (ASCII 236, which my newsreader > won't let me paste into this message -- it keeps coming out as an > 8). Ahh. I've seen M:M before. The infinity symbol would have made sense as well. >>I suppose there's a use for compound indexes and if you have lots >>of tables that utilize compound indexes, you have a need. I >>haven't found the need but that might be due to my application >>logic. > > > If you have many-to-many joins, you *must* have compound indexes, or > you're forcing yourself to enforce uniqueness in your application. > I don't have any compound indexes in any of my apps. SQL seems to be good enough to present/filter data, a form to validate data before committing, and the need for a compound index hasn't presented itself to me. In the first ADH book I read back in '97 or '98 I remember some mention about N:N but the author felt there was little complelling reason for one. My goal in an app is to do something that works and works quickly and accurately. If it does, fine. If it doesn't, not fine. In programming there oftentimes is more than one way to skin a cat...we see it all the time in posts here. So if I don't use them and my app works...fine. If you use them and your app works...fine. The goal was met.
From: David W. Fenton on 25 Nov 2009 17:32
Salad <oil(a)vinegar.com> wrote in news:QIudndKDdaiPE5HWnZ2dnUVZ_qmdnZ2d(a)earthlink.com: > In the first ADH book I read back in '97 or '98 I remember some > mention about N:N but the author felt there was little complelling > reason for one. I don't believe that you understood that correctly. Common N:N: You have a table of people. You have a table of categories (e.g., Vendor, Customer, etc.). Each person can be in more than one category, so you have a table in between the people table and the categories table, with two columns, PersonID and CategoryID. That's your standard N:N join table, and it needs to have a unique index on the combination of PersonID and CategoryID. Now, the world will not end if you end up with a category listed twice (or even 100 times) for 1 person, but it's nonsense data, as the duplicate records are completely redundant. In other N:N cases, it's more important that the uniqueness be enforced, and where that's required, Sharepoint requires you to do it in the application, which is a lot more work than having the database engine enforce such a simple requirement. And then there's the case of unique compound indexes that prevent the insertion of duplicate records in a non-join table (usually on the natural key when you've chosen to use a surrogate key as the PK). An example might be a list of companies, where you have a unique index on company name and city/state/zip (or some subset of those 4 fields). Now, you might choose to model that as a company table and then a related table of addresses, but that's not always the optimal way to store the data (even if it would be more normalized in an abstract sense). But if your data model works better with a flatter structure, you need that unique index. There really isn't any case that can be made for not having compound unique indexes except one that ignores all the best principles of database design. Saying you'll do it in the app is a complete cop-out -- that's bad design. You do as much in the database engine as possible in order to insure that there is no possibility that bad data can get inserted into your database. This is a principle that should not be in dispute. And arguing that it doesn't matter that you don't have compound indexes is abandoning that principle, and arguing for bad schema/application design. Sharepoint is forcing that on users, but that's not a good thing. It's not even a neutral thing -- it's very serious deficiency. Granted all that, the lack of RI Sharepoint before 2010 is a much worse lack. But when they fix things like the lack of RI, they should be fixing the other deficiencies, like the lack of compound indexes. It may be "good enough" that doesn't make it GOOD. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |