Re: Access 2010 beta - Publish to Sharepoint

"James A. Fortune" <CDMAPoster@xxxxxxxxxxxxxxxx> wrote in
news:525ed430-c268-4c15-a2f9-10768fb9dea4@xxxxxxxxxxxxxxxxxxxxxxxxxxx
:

On Nov 28, 11:32 pm, "David W. Fenton"
<XXXuse...@xxxxxxxxxxxxxxxxxxx> wrote:
"James A. Fortune" <CDMAPos...@xxxxxxxxxxxxxxxx> wrote
innews:1e61f177-ffff-43dc-9970-a52010b85f51@xxxxxxxxxxxxxxxxxxxxxx
ps.co m:
On Nov 24, 8:34 pm, "David W. Fenton"
<XXXuse...@xxxxxxxxxxxxxxxxxxx> wrote:
"James A. Fortune" <CDMAPos...@xxxxxxxxxxxxxxxx> wrote
innews:a8dd5ff2-b5de-41ac-b10b-708a9060b9f2@xxxxxxxxxxxxxxxxxxx
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/
.

Relevant Pages

  • Re: Access 2010 beta - Publish to Sharepoint
    ... only about junction tables and the seriousness of the need for RI ... having compound keys is also good. ... Multicolumn indices are indeed convenient when natural keys are used, ...
    (comp.databases.ms-access)
  • Re: strategy for data entry in multiple tables
    ... I sometimes use composite keys to prevent duplicates in a table, ... > what appears to be excess entry is necessary for data validation and for ... > tables be opened as subforms within the junction table form? ... >> the bird info...just the info necessary to determine whether the bird ...
    (microsoft.public.access.forms)
  • Re: Access 2010 beta - Publish to Sharepoint
    ... only about junction tables and the seriousness of the need for RI ... having compound keys is also good. ... You never leave anything that can be enforced at engine level to ...
    (comp.databases.ms-access)
  • Re: Still Struggling...
    ... I still think the relationship is between keys and locks, ... Ignoring master keys for the moment, you have One Lock: Many Keys, so LockID is a FK in tblKeys. ... One master key can open several locks, and each lock can be opened by several different keys including the master key. ... If there is to be a junction table I think it would be between locks and keys. ...
    (microsoft.public.access.gettingstarted)
  • Re: Data Primary key vs. Artificial (Autonumber) primary key
    ... "natural" keys, I use unique indexes. ... the message about having table constraints on the candidate keys ... but I would wager than most users in the 'Autonumber PK' camp put ... worse) with multi-column natural keys (I have no objection on ...
    (microsoft.public.access.tablesdbdesign)