Separate PK in Jxn Tbl?

[Keith Wilby]

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

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

 

[David Cressey]

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

Good. We have agreement on the essentials here.

 

[David Cressey]

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?

 

[Keith Wilby]

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

I wasn't aware of any special UK meaning. I'm a UK-nian and to me it means
"debatable". Isn't that what it means in BushLand?

Keith.

 

[Keith Wilby]

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".

But then again the US don't have colour television programmes either do
they? Or aluminium ;-)

Keith.

 

[David Cressey]

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.

 

[David Cressey]

« 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.

 

[David Cressey]

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.


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.

 

[Neil]

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.... ;-)

 

[Neil]

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."

 

[Sylvain Lafontaine]

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]

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]

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.

 

[Frank Hamersley]

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.

 

[Salad]

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

I prefer KISS.

Thus I prefer an autonumber.

Then again, junction tables are rarely needed.

 

[Brian Selzer]

I prefer KISS.

Thus I prefer an autonumber.

Then again, junction tables are rarely needed.

Simple is as Simple does.

 

[Tony Toews [MVP]]

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?

Datums - NAD27, NAD83, and WGS84.all have slightly different meanings for the same
latitude and longitude for North America.
http://en.wikipedia.org/wiki/Geodetic_datum

And there are different latitudes http://en.wikipedia.org/wiki/Latitude

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

Apparently you have never had to attach an access app to a new instance
of the database it uses. I found that process incredibly awkward, slow
and kludgy.

Now you won't read this because you have me plonked. Nevertheless there are two
basic means of relinking an Access front end (FE) to the data source such as an
Access backend (BE).

You can use the Linked Table Manager wizard to do the work.

However I prefer using the code which, after the first table is linked, then opens a
recordset against that table, relinks the rest of the tables and then closes the
recordset. If someone else is already in the BE MDB this will give you a much
better performance. As much as ten or twenty times as fast.

I use an older version of the code at Relink Access tables from code
http://www.mvps.org/access/tables/tbl0009.htm

Also "ACC2000: Slower Performance on Linked Tables - 261000 indicates that if the
database has many linked tables that also have many relationships, and the table that
you are opening has its subdatasheet Name property set to [Auto], this can make the
table slow to open. Subdatasheets are a new feature in Access 2000 Therefore, you are
more likely to notice this behaviour after you convert a database from an earlier
version.

It is recommended that we set the subdatasheet Name property on each table in the
back-end database to [NONE]. Making this change in the front end won't help if it
even works at all."

Also "Links to tables on MDB which are not accessible. You may have some tables
linked to a secondary MDB for which the server is no longer available or drive letter
is no longer mapped."

The above is from Microsoft Access Performance FAQ on my website
http://www.granite.ab.ca/access/performancefaq.htm

Maybe you should've asked in an Access newsgroup or done some googling?

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

Therein lies its criminality <g> - it screams encouragement for dabblers
and barely offers anything for artisans except stupendous numbers of
mouse clicks!

OTOH do you want them using Excel for data management? Single user?

A friend was telling me of a huge Excel file with many workbooks where data
normalizing meant going through the cells looking for occurances of similar names and
ensuring they all had the same name.

And, of course, Excel is single user as far as I know.

Take for instance the number of versions it took before separating the
data from the "code" was a core feature by way of the provision of a
menu option to reattach a data .mdb!

A97 has the wizard. I don't recall about previous versions.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

But then again the US don't have colour television programmes either do
they? Or aluminium ;-)

We in Canada do have colour TVs, although I don't actually own one. But we don't
have aluminium. So where does that put us?

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/