Separate PK in Jxn Tbl?

"Jamie Collins" wrote in message
...
On Jan 29, 12:52 pm, "David Cressey" wrote:
I'm completely unfamiliar with the UK usage of "moot". What is it?

Obviously I can't speak for the whole of the UK but I would say it was
the literal, dictionary meaning i.e. 'debatable' rather than 'not
worth debating'.


OK, I've learned something here.

From the American Heritage Dictionary:

moot adj. 1. Subject to debate; arguable. 2.a. Law. Without legal
significance, through having been previously decided or settled. b. Of no
practical importance; irrelevant.


I have always used this word, as an adjective, in the sense of 2b. above. A
moot point, as I've used it, is one that could be decided either way without
affecting the course of the larger debate. I now see that that's not
strictly what the word means.

"Sylvain Lafontaine" sylvain aei ca (fill the blanks, no spam please)
wrote in message ...
« But many of them seem to write as if contents as determined by
ddress ».

The content is not determined by the address and in fact, for those who
are
using surrogate keys, the exact value of an address inside the database
has
zero importance.

You misunderstand my point. A great many people use surrogate keys as if
they were surrogate addresses. They then use foreign keys that reference
surrogate keys as if they were surrogate pointers.

If this mimicry of pointers is pushed far enough, it can reduce a
relational or SQL database to an inferior imitation of the old network
databases. You lose the inherent advantages of content based addressing,
but retain all the extra overhead.

"JOG" wrote in message
...
On Jan 27, 2:18 pm, "Rick Brandt" wrote:
David Cressey wrote:
The idea of keeping garbage out of the database takes on an entirely
different meaning if you are dealing with hundreds of programs
written in COBOL, Java, or anything in between accessing a single
Oracle database on the one hand. On the other hand, if you are a
developer creating a self contained MS Access database cum
application (tables, queries, forms, reports, modules, etc.) all in
one file, the same issues arise, but they are resolved quite
differently.

I'm not saying either one is "right" or "wrong". I'm just suggesting
why an objection that makes perfect sense to you and me might be lost
on the MS Access community.

^
some of

I think if a thorough poll was done it would show that the majority of
professional Access developers (those that make their living at it)
would agree
that data integrity rules should be enforced by the database engine
wherever
that is possible.

Well thank goodness for that! For a scary moment I though Sylvian's
views were representative of the access community as a whole, and that
you guys didn't think that data integrity should be enforced primarily
by the db engine.


The fact is that Access is a tool predominantly for *users*, not
developers, and
Microsoft appears determined with each subsequent version to make that
more the
case. The majority of changes make it easier to do things incorrectly
because
that makes the program easier to use for people who have no idea what
they are
doing. Since that group vastly outnumbers the other one can hardly
argue with
their logic from a business standpoint.

My fear though is that many db developers cut their teeth using
Access. If bad practices are encouraged just because access doesn't
handle many concurent users, and tends to manage data where it's
unlikely one will hit the pitfalls that artificial keys can lay, when
developers graduate up to larger server systems they may well carry
those mistakes on with them.

I agree with you. However, we should keep in mind that the same arguments
could be made about people learning bad programming habits by building
amateur programs in BASIC, or bad website design habits by using a tool
like Front Page. In general, the tools that require a very short learning
curve encourage the belief that the longer learning curve is of no practical
value.

We've seen that view voiced here (perhaps facetiously) by one of the Access
MVPs. To the extent that he has acquired a lot of credibility with Access
newbies, however acquired, if he gives advice that will become bad advice
when scaled upward, he aggravates the pitfall you warn against.

Elsewhere in the discussion, I opined that Access applications were
generally stored in the same file as the database. I've heard enough
contrary opinions to stand corrected on that score. (I can't find that part
of the discussion anymore.)

However, I still think that hundreds of DIFFERENT application programs
accessing a single database and written by programmers who did not build the
database, is qualitatively different from the design target of the people
who write Access databases and applications.

If they ever get to the point where the complexity of what they are doing
matches the complexity of what practitioners using SQL Server, Oracle, or
DB2 are doing, or the complexity that database theorists are addressing,
they will be forced to either learn or disprove what some of us know, or
think we know.





I certainly don't think developers should excuse sloppy RDBMS design
just because they are using access (and of course I'm sure many of the
professionals here wouldn't dream of doing so, despite others
laxness).


I have to admit that, when I'm just playing around, I engage in sloppy
work. I would not go so far as to recommend sloppy habits as good ones in a
newsgroup, however.

"Jamie Collins" wrote in message
...
On Jan 28, 11:02 pm, Rob wrote:
I'm not sure what you mean by "moot". According to:

http://www.usingenglish.com/referenc...oot+point.html

quote
If something's a moot point, there's some disagreement about it: a
debatable point. In the U.S., this expression usually means that there
is no point in debating something, because it just doesn't matter. An
example: If you are arguing over whether to go the beach or to the
park, but you find out the car won't start and you can't go anywhere,
then the destination is said to be a moot point.
/quote

I googled "moot" to make sure I knew what you meant, and was surprised
by the definition. I will take your meaning to be '"there is no point
in debating" whether NULL values in junction tables are ever useful
because they are not'.

Here in the UK I avoid using the word 'moot' when trying to write
'plain English' simply because the US usage has obscured the UK usage
i.e. it can cause confusion.


You could use the alternate "moo" point, meaning that the point is full of
methane gas. At least it would be clear what you mean.... ;-)

"Roy Hann" wrote in message
...
"David Cressey" wrote in message
news:zkFnj.391$Be.9@trndny04...

Here in the UK I avoid using the word 'moot' when trying to write
'plain English' simply because the US usage has obscured the UK usage
i.e. it can cause confusion.

I'm completely unfamiliar with the UK usage of "moot". What is it?

Having lived for several decades on both sides of the Atlantic I think I
know. In America the word tends mean "having no practical
significance"--a meaning which is borrowed from the legal profession I
think. In the UK to say something is moot is to say it is "still subject
to discussion" or "undecided".


I think the reason for the two meanings of the word comes from the legal
profession, where law students would would hold "moot court" of
previously-decided cases. Hence, both "debatable" and "previously-decided"
seem to be derived from its original meaning.

From Wikipedia, "Mootness" article:

"In United States law, a matter is moot if further legal proceedings with
regard to it can have no effect, or events have placed it beyond the reach
of the law. Thereby the matter has been deprived of practical significance
or rendered purely academic. This is different from the ordinary British
meaning of "moot," which means "to raise an issue." The shift in usage was
first observed in the United States."

I concede the point that for the two keys of the junction table, using an
autonumber primary key is overkill except for special situations.

Shouldn't a database be designed right from the beginning?

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"James A. Fortune" wrote in message
...
Neil wrote:
"James A. Fortune" wrote in message
...

Neil wrote:

Whenever I've created junction tables in the past, I always made the PK
of the junction table the combined pks from the two other tables. Ex:
Table1 PK=A; Table2 PK=B; Junction table consists of two fields, A and
B, which together comprise the PK for the junction table.

However, I just came across some code in which the person created a
junction table with a separate PK consisting of an autonumber field, and
then the two fields.

So I was wondering how others did junction tables -- with a standalone
autonumber PK, or with a PK consisting of the PKs of the tables being
joined? And, if a standalone PK, then why?

Thanks!

Neil

Whenever I have multiple key fields, natural or not, I create an
AutoNumber PK for pragmatic reasons. The main reason is that it makes it
easier to create the joins. The theorists are champions at joining
tables and don't have to be concerned with the complexity of the SQL they
write. If I convert an Access table over to SQLServer I add even another
field as a primary key, usually prefixed with SS (Gasp!).

I keep Jamie's advice in the back of my mind, about how enforcing
constraints at the table level is better than enforcing them through
code, but where I work, no one is going to access the table data using
anything other than Access so I am able to take the high road at my
leisure. Maybe my coding practice just needs to catch up with my
philosophy. Theoretically, the idea of using natural keys is more
intellectually satisfying, but for now the lure of simpler joins is
winning out. Lately, I've increased the amount of normalization in one
of my databases and the joins got even more complicated, adding about a
line or so in the SQL view in Access for every new query using those
tables. Queries involving many to many relationships often add
additional tables later and highlight the need to keep joins as simple as
possible.

James A. Fortune




I tend to do the same as you: I tend to put autonumber primary key fields
in place where a multi-field PK will do, for the same reasons: i like to
be able to refer to a single key. However, when it comes to junction
tables, it just seems pointless. Referring to two fields instead of one
isn't that big of a deal; and a table made up of two foreign keys is very
clean -- adding an additional key on top of that seems useless and added
baggage.

Larry posted that there are times when an autonumber PK is needed, such
as when using the junction talbe in a combo box, and I would agree with
him there. So in a few cases it might serve a purpose (even though it's
not absolutely necessary there either). But for the most part, it seems
unnecessary.

Neil

I concede the point that for the two keys of the junction table, using an
autonumber primary key is overkill except for special situations.

James A. Fortune

You misunderstand my point. A great many people use surrogate keys as if
they were surrogate addresses. They then use foreign keys that reference
surrogate keys as if they were surrogate pointers.

Not sure to fully understand you on this sentence. However, if you mean by
that that a surrogate key should never cross the boundaries of a database -
with the inclusion of the interface as part of the database - then yes,
theoritically you're right but practically - ie. in the real world with real
budget - it's not necessary wrong to do it.

Some people consider databases as some kind of Gods and you shouldn't ask
what the database can do for you but what you can do for the database.
Personnally, I've never made a sacrifice - either monetary, animal, human
or other - in the name of a database and all I'm asking of a database is
what it can do for the client; never the other way.

However, like everything else, you must think about it and everytime you see
a situation where a surrogate key is getting out of the database, there is a
potential problem to look at.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"David Cressey" wrote in message
news:8gHnj.525$4f.510@trndny08...

"Sylvain Lafontaine" sylvain aei ca (fill the blanks, no spam please)
wrote in message ...
« But many of them seem to write as if contents as determined by
ddress ».

The content is not determined by the address and in fact, for those who
are
using surrogate keys, the exact value of an address inside the database
has
zero importance.

You misunderstand my point. A great many people use surrogate keys as if
they were surrogate addresses. They then use foreign keys that reference
surrogate keys as if they were surrogate pointers.

If this mimicry of pointers is pushed far enough, it can reduce a
relational or SQL database to an inferior imitation of the old network
databases. You lose the inherent advantages of content based addressing,
but retain all the extra overhead.

I was making a direct reference to the following quote:

In a recent thread on this subject, Tony Toews Access MVP qualified
that he liked using incremental autonumbers (rather than random)
because they where easier to type (WHERE ID = -2001736589 may
encourage typos) and easier to drop into conversation ("Hello Tony?
I'm seeing a problem with the record where the ID is -2001736589...").

Of course, theoritically and in a world with unlimited budget, you're right
in the sense that a surrogate key should never cross the boundaries of a
database (the interface beeing located inside in these boundaries) but my
clients don't have infinite budget and my brain isn't infinite either.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"Jamie Collins" wrote in message
...
On Jan 28, 5:30 pm, "Sylvain Lafontaine" sylvain aei ca (fill the
blanks, no
if you live in a perfect world, one with infinite budget and infinite
time to do any project, then I understand your concerns.

if I
were to live in a perfect world, I wouldn't have to work to earn a living
in
the first place.

Are you really aiming your comments at me? I've already said in this
thread that I frequently encounter 'autonumber' problems so how could
that be a "perfect world" for me?

I understand your concerns.

Oh yeah? Do you understand that my concern is not that Access MVPs in
typically use autonumbers on most, if not all, their tables? I'm sure
they know enough to make their own decisions. Rather, my concern is
that Tony Toews Access MVPs would promote such practise without giving
good reasons or presenting a balanced view, and that readers will he
says because of the letters M, V and P and in lieu of understanding
the issues themselves. Once again, I should say I have no problem with
Tony Toews Access MVP generally, I think he was just having an off day
or perhaps wanted to provoke a response by being flippant. And I've no
problem with anyone being provocative round here (I'd be a hypocrite
if I said I did g).

you cannot codifying everything for a variety of
reasons: budget, system already in place and working well, impossibility
to
anticipate everything, more art than a science, etc., etc.

Hm, "codifying" is not a word I'm overly familiar with, I had to look
it up: "the process of collecting and restating the law of a
jurisdiction in certain areas, usually by subject... To arrange or
systematize". If that's an accusation then Tony is just as guilty as I
with his, "It's one of my rules" comment.

Why would they pay to
change something that had worked well for them for many years and at the
risk of finding themselves at the front of something new that might not
work
as well as the one system?

Did I suggest they should? Re-engineering code for the sake of it is
not my philosophy and I offer the following recent thread in evidence:

http://groups.google.com/group/micro...635944f8042608

Pete says my real question/agenda is: Should I go through my app
and change everything that's Double to Decimal?

Jamie says I don't think you should reengineer your code in the way
you suggest.
That would be committing the same mistake as those who add an
autonumber primary key to every table i.e. done out of habit, knee
jerk reaction rather than engaging the brain. Fix bugs instead ;-)

Back to the current thread:

this system is working well at the moment and has
done so for many years; it would probably make your teeth gnashing but
it's
not on their radar at this moment to change it.

I'm sure that if I looked at code I wrote last year that is working
well at the moment I'd want to do it differently given the opportunity
but I wouldn't seek to create such an opportunity; I'd rather put it
down to experience and tackle something new.

In this message, you can replace the name of Celko with the name of a lot
of
persons around here but not with mine.

So are you aiming you comments at me directly, merely implicating me
or have you just chosen to attach your 'rant' to my post at random?

Jamie.

--

On Jan 29, 7:56 am, "Neil" wrote:
"Jamie Collins" wrote in message

Here in the UK I avoid using the word 'moot' when trying to write
'plain English' simply because the US usage has obscured the UK usage
i.e. it can cause confusion.

You could use the alternate "moo" point, meaning that the point is full of
methane gas. At least it would be clear what you mean.... ;-)

Amusingly, a lot of people misperceive the word as "mute."

It's a mute point, meaning it can't say anything any longer.
It sorta vaguely works in a metaphoric way.

Of course, on the internet, you can find many examples of
fractured usage. Ultimately it's just a waist of time.snicker


Marshall

Keith Wilby wrote:
"Frank Hamersley" wrote in message
...

provision of a menu option to reattach a data .mdb!

Is it just me or is that complete gibberish? Reattach?

Just you mate (at least I hope so or we are doomed).

Pray tell how do you distribute new versions of "code"?

Never had that concern? - always just hacked the live .mdb?

Gawd.