Access 2010 beta - Publish to Sharepoint
in [Help]
|
Prev: Keeping A Citrix Session Alive?
Next: Help in A2010
From: James A. Fortune on 28 Nov 2009 20:24 On Nov 24, 8:34 pm, "David W. Fenton" <XXXuse...(a)dfenton.com.invalid> wrote: > "James A. Fortune" <CDMAPos...(a)FortuneJames.com> wrote innews: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? You seem to have missed the point of my argument. I was talking only about junction tables and the seriousness of the need for RI in junction tables alone. We all agree that RI enforced at the table level is wonderful. Nearly every engineering lab I took stressed the importance of doing everything possible to avoid bad data and to ensure that the raw data gathered made sense. The ability of having compound keys is also good. It was using the junction table as a prime example of the need for a compound key that was bad. > 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. Your proposed disaster scenario is implausible in the case of junction tables. > 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 [sic] was exactly that, i.e., that you couldn't > create a Sharepoint app that would enforce the most basic aspects of > data integrity. AFAICT, no one here disputes that point. > > 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. While not a major deficiency for junction tables, I agree that support for compound keys would be a good thing to be added to SharePoint, along with RI support. James A. Fortune CDMAPoster(a)FortuneJames.com
From: David W. Fenton on 28 Nov 2009 23:32 "James A. Fortune" <CDMAPoster(a)FortuneJames.com> wrote in news:1e61f177-ffff-43dc-9970-a52010b85f51(a)m16g2000yqc.googlegroups.co m: > On Nov 24, 8:34 pm, "David W. Fenton" > <XXXuse...(a)dfenton.com.invalid> wrote: >> "James A. Fortune" <CDMAPos...(a)FortuneJames.com> wrote >> innews:a8dd5ff2-b5de-41ac-b10b-708a9060b9f2(a)f10g2000vbl.googlegrou >> ps.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? > > You seem to have missed the point of my argument. I was talking > only about junction tables and the seriousness of the need for RI > in junction tables alone. I wasn't talking about RI in regard to junction tables, and I never was at any point. > We all agree that RI enforced at the table > level is wonderful. Nearly every engineering lab I took stressed > the importance of doing everything possible to avoid bad data and > to ensure that the raw data gathered made sense. The ability of > having compound keys is also good. It was using the junction > table as a prime example of the need for a compound key that was > bad. I wasn't using it as the "prime" example. It was just the easiest to explain. Losing multi-column RI would not bother me, as I don't compound keys for RI. But losing the compound index would be a severe problem for junction tables, as well for all the multi-column natural unique keys (which I never use for RI, but which still need uniqueness enforced). >> 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. > > Your proposed disaster scenario is implausible in the case of > junction tables. Eh? What disaster scenario are you talking about? [] >> 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. > > While not a major deficiency for junction tables, I agree that > support for compound keys would be a good thing to be added to > SharePoint, along with RI support. It *is* a major deficiency for junction tables, precisely because most of them require a unique compound index on the combination of keys being joined. There is no getting around that as proper schema design. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/
From: James A. Fortune on 30 Nov 2009 09:58 Comments are contained "in-line" below. On Nov 28, 11:32 pm, "David W. Fenton" <XXXuse...(a)dfenton.com.invalid> wrote: > "James A. Fortune" <CDMAPos...(a)FortuneJames.com> wrote innews:1e61f177-ffff-43dc-9970-a52010b85f51(a)m16g2000yqc.googlegroups.co > m: > > > > > On Nov 24, 8:34 pm, "David W. Fenton" > > <XXXuse...(a)dfenton.com.invalid> wrote: > >> "James A. Fortune" <CDMAPos...(a)FortuneJames.com> wrote > >> innews:a8dd5ff2-b5de-41ac-b10b-708a9060b9f2(a)f10g2000vbl.googlegrou > >> ps.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? > > > You seem to have missed the point of my argument. I was talking > > only about junction tables and the seriousness of the need for RI > > in junction tables alone. > > I wasn't talking about RI in regard to junction tables, and I never > was at any point. Then maybe I misunderstood what you meant by an N:N join table. Did you not mean a junction table? > > > We all agree that RI enforced at the table > > level is wonderful. Nearly every engineering lab I took stressed > > the importance of doing everything possible to avoid bad data and > > to ensure that the raw data gathered made sense. The ability of > > having compound keys is also good. It was using the junction > > table as a prime example of the need for a compound key that was > > bad. > > I wasn't using it as the "prime" example. It was just the easiest to > explain. Losing multi-column RI would not bother me, as I don't > compound keys for RI. But losing the compound index would be a > severe problem for junction tables, as well for all the multi-column > natural unique keys (which I never use for RI, but which still need > uniqueness enforced). Multicolumn indices are indeed convenient when natural keys are used, but natural keys don't seem to be as "natural" for junction tables. > > >> 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. > > > Your proposed disaster scenario is implausible in the case of > > junction tables. > > Eh? What disaster scenario are you talking about? The disaster where some database person, who knows what a junction table is, creates a new application based on my particular application then either adds duplicate entries (foreign keys) directly to the table by hand or creates a form that doesn't check for existing combinations when adding new combinations. > > [] > > >> 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. > > > While not a major deficiency for junction tables, I agree that > > support for compound keys would be a good thing to be added to > > SharePoint, along with RI support. > > It *is* a major deficiency for junction tables, precisely because > most of them require a unique compound index on the combination of > keys being joined. There is no getting around that as proper schema > design. It wasn't that difficult to find a workaround. It's true almost by definition that a workaround is not is good as the best way. How "major" the deficiency is depends on how bad it is to use the workaround. It is apparently frightful in your world not to be able to conform to the absolute best practices all the time. For me, getting to the point where I use the best of everything I know and incorporate it into my databases is an ongoing process. So someone like myself who has not yet reached database programming perfection is not as shocked by such deviations from the best way. I have still not added many CHECK constraints to tables that I know should have them because, frankly, it's not that urgent. I think your pedantry is to be admired, but we're not talking about a "2012" crisis here. James A. Fortune CDMAPoster(a)FortuneJames.com Historical books I'm currently reading: After Tamerlane The House of Medici
From: David W. Fenton on 30 Nov 2009 17:32 "James A. Fortune" <CDMAPoster(a)FortuneJames.com> wrote in news:525ed430-c268-4c15-a2f9-10768fb9dea4(a)h2g2000vbd.googlegroups.com : > On Nov 28, 11:32 pm, "David W. Fenton" > <XXXuse...(a)dfenton.com.invalid> wrote: >> "James A. Fortune" <CDMAPos...(a)FortuneJames.com> wrote >> innews:1e61f177-ffff-43dc-9970-a52010b85f51(a)m16g2000yqc.googlegrou >> ps.co m: >> > On Nov 24, 8:34 pm, "David W. Fenton" >> > <XXXuse...(a)dfenton.com.invalid> wrote: >> >> "James A. Fortune" <CDMAPos...(a)FortuneJames.com> wrote >> >> innews:a8dd5ff2-b5de-41ac-b10b-708a9060b9f2(a)f10g2000vbl.googleg >> >> rou ps.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? >> >> > You seem to have missed the point of my argument. I was >> > talking only about junction tables and the seriousness of the >> > need for RI in junction tables alone. >> >> I wasn't talking about RI in regard to junction tables, and I >> never was at any point. > > Then maybe I misunderstood what you meant by an N:N join table. > Did you not mean a junction table? Yes, but the issue was not RI, but the required compound index. And it *is* required for most junction tables, as multiple join records are logically nonsensical in most junction tables. >> > We all agree that RI enforced at the table >> > level is wonderful. Nearly every engineering lab I took >> > stressed the importance of doing everything possible to avoid >> > bad data and to ensure that the raw data gathered made sense. >> > The ability of having compound keys is also good. It was using >> > the junction table as a prime example of the need for a >> > compound key that was bad. >> >> I wasn't using it as the "prime" example. It was just the easiest >> to explain. Losing multi-column RI would not bother me, as I >> don't compound keys for RI. But losing the compound index would >> be a severe problem for junction tables, as well for all the >> multi-column natural unique keys (which I never use for RI, but >> which still need uniqueness enforced). > > Multicolumn indices are indeed convenient when natural keys are > used, but natural keys don't seem to be as "natural" for junction > tables. I don't know what you'd call a join between Person and Category on PersonID and CategoryID, natural or surrogate, but it *must* have unique index. As to natural keys, if there is a unique natural key (independent of whether it is used as the PK or a surrogate key is used instead), it has to have a unique index on it. This is something that everyone, including those who are anti-natural key (like me) agrees on, i.e., that even if you don't use it as the PK, it still has to have a unique index *if* it is unique. This is pretty basic stuff. >> >> 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. >> >> > Your proposed disaster scenario is implausible in the case of >> > junction tables. >> >> Eh? What disaster scenario are you talking about? > > The disaster where some database person, who knows what a junction > table is, creates a new application based on my particular > application then either adds duplicate entries (foreign keys) > directly to the table by hand or creates a form that doesn't check > for existing combinations when adding new combinations. How is that implausible? That individual might very well be *you*, just some time later when you've forgotten that you have to enforce uniqueness in the UI because you can't do it at the engine level. >> [] >> >> >> 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. >> >> > While not a major deficiency for junction tables, I agree that >> > support for compound keys would be a good thing to be added to >> > SharePoint, along with RI support. >> >> It *is* a major deficiency for junction tables, precisely because >> most of them require a unique compound index on the combination >> of keys being joined. There is no getting around that as proper >> schema design. > > It wasn't that difficult to find a workaround. It's true almost > by definition that a workaround is not is good as the best way. > How "major" the deficiency is depends on how bad it is to use the > workaround. It is apparently frightful in your world not to be > able to conform to the absolute best practices all the time. Well, no. But I consider uniqueness of N:N tables to be so basic as to be a significant deficiency when not present. Now, I've written applications for MySQL using MYISAM tables, which means no referential integrity at all, so, yes, I've worked around these kinds of things. But it makes for much less reliable apps. In one case, a PHP upgrade caused the application's code to behave differently and inserted a bunch of records with a 0 foreign key. With proper restrictions at the database engine level, incorrect data would not have been insertable at all. > For me, > getting to the point where I use the best of everything I know and > incorporate it into my databases is an ongoing process. So > someone like myself who has not yet reached database programming > perfection is not as shocked by such deviations from the best way. > I have still not added many CHECK constraints to tables that I > know should have them because, frankly, it's not that urgent. I > think your pedantry is to be admired, but we're not talking about > a "2012" crisis here. This is a common rhetorical strategy for the intellectually dishonest, i.e., to characterise the opposing argument as having been made in terms that are hysterical. I understand once again why I long ago put you in my killfile. There you shall remain. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/
From: James A. Fortune on 30 Nov 2009 17:42
On Nov 30, 5:32 pm, "David W. Fenton" <XXXuse...(a)dfenton.com.invalid> wrote: > This is a common rhetorical strategy for the intellectually > dishonest, i.e., to characterise the opposing argument as having > been made in terms that are hysterical. You're not hysterical - you just overreacted. > > I understand once again why I long ago put you in my killfile. There > you shall remain. As you wish. James A. Fortune CDMAPoster(a)FortuneJames.com |