Separate PK in Jxn Tbl?

"Brian Selzer" wrote in message
...

"Roy Hann" wrote in message
...
This is the nature of keys:
either the values for a key are permanent identifiers, or they're not.
It has nothing to do with how well keys are managed. The values for a
key may be managed perfectly, yet still not be permanent
identifiers--the position of something in a list of things comes to
mind.

No, the position number identifies the same position, for all time. The
facts about the current occupant of the position may change willy-nilly.

I don't think so. In the domain of positions, a position number
identifies the same position, for all time: that is the nature of a
domain. But whenever a position number appears as a key in a relation, it
identifies an occupant, even though the occupant identified may not be the
same occupant at different database instances: this is the nature of a
key.

A key as you defined it earlier is sufficient to provide only
addressability, not identification. They are not the same thing (although
anything that provides identification would also be a key). This is a basic
part of the concept of functional dependency.

Roy

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

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 28, 6:41 pm, "Tony Toews [MVP]" wrote:
It's my understanding that the ease of use and speed of development of the latest
.Net products is getting closer to that of Access. So I'm certainly keeping my eye
on these.

Not convinced yet but then I haven't done next to no research in this area. Too
busy. smile

Good approach

Jamie.

--

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.

Jamie.

--

On Jan 28, 11:35 pm, "David W. Fenton"
wrote:
You clearly are not distinguishing a
development platform (Access) from a database engine (Jet)

I regularly admonish Access group regulars, sometime even Access MVPs,
for committing the same error. I suggest you pick on them and learn to
be nice to guests, even if you do have an extremely massive chip on
your shoulder g.

Jamie.

--

"David W. Fenton" wrote in message
.89...

If your impression of Access comes from futzing with it for 10
minutes and from encountering kludged-together apps created by your
company receptionist, then you just haven't a clue what Access
offers, either as an application development platform or as a data
store (using its native Jet engine).


I frequently have Oracle die-hards tell me (and more worryingly, management)
that Access is a toy that will fall over when more than a couple of users
log on. I have yet to have any of them rise to the challenge of siting a
single example of a correctly set up, split application in Access that isn't
stable under load. Mine are certainly stable and reliable, the only
problematic ones are set up by dabblers. None of the aforementioned
die-hards even know what a split Access application is.

Keith.
www.keithwilby.com

The problem has been known as the "cat food" problem for years and years.

LOL! When the question of duplicates came up in SQL Committee
meetings, we decided to leave it in the standard. The example we used
internally, and which later appeared in the letter columns of Database
Programming & Design and Datamation magazines in replies from other
X3H2 Committee members, was a cash register receipt with multiple
occurrences of cans of cat food on it. That is how this got to be the
"cat food problem" in the literature.

You therefore can't tell whether two bottles of gin were checked out, or whether one bottle was scanned twice.

The way I drink, Gin would be a commodity entity But fine wines
are identified by grape, vineyard, vintage and harvest; maybe an
auction number is a surrogate for all of that jey?

On Jan 29, 10:39 am, "Keith Wilby" wrote:
[I'm told] Access is a toy that will fall over when more than a couple of users
log on

Maybe your informants had bases in truth but were exaggerating for
effect...

I frequently have Oracle die-hards tell me

....or perhaps the exaggeration is your own ;-)

As a measure, when I say I "frequently" encounter autonumber problems
I mean that every SQL DBMS schema I've worked on (bar one) that was
not designed exclusively by me have suffered from them, and on the
system I currently support there is an autonumber related problem
almost certainly every week. Fixing such flaws is an ongoing process
but costly because of the need to migrate users' existing data, and
the need to balance bug fixes against new features (i.e. to be
competitive while remaining profitable) means the process is slow.

Jamie.

--

If a key is "natural", it is managed, if at all, beyond the scope of the DBMS.


I have never understood the people that blindly slap their own "fake
keys" on tables, either. A trusted external source that does all the
meta-data and encoding maintenance for us! Oh, how horrible! I guess
this is why universal bar codes in retail are such a failure and every
shop puts their own "synthetic key" labels on items? Oh wait, that is
not how things work, is it? You might not be old enough to remember
when things did not have UPC and EAN codes -- I am.

When it is managed by other people, it is subject to mismanagement.

But when it is managed by one cowboy coder, it is always perfect?
Gee, I can bet on one local encoding that nobody else knows (the pre-
UPC inventory bar codes) or a universal, centrally maintained industry
standard with a full time staff doing validation and verification
processes. I would call that a "no-brainer" business decision.

Changing values that ought to be immutable is one of many ways that natural keys can be mismanaged.

Let's take an example that is a very strong natural key -- (longitude,
latitude). Established for centuries. Well-defined operations, etc.
Validation can be done by GPS or a few million maps. Can you explain
how this immutable key gets changed more often that some "synthetic
key" for locations?

Natural keys are the hardest thing to mis-management *because* they
are natural. "Against stupidity the gods themselves struggle in
vain." - Die Jungfrau von Orleans; Friedrich von Schiller (1759-1805)

Sorry, had to rant a bit ..