Separate PK in Jxn Tbl?

[Brendan Reynolds]

I believe this is a moot point when it comes to MS Access. The app and
the
database are all stored together in Access. There is, by definition, only
one Access.

Access regulars, feel free to correct this if it's wrong.

The app and the data *can* be stored in one MDB, but do not *have* to be
stored in one MDB (linked tables). It is entirely possible - and in my
experience not unusual - to have more than one application reading and or
updating data in an MDB (or ACCDB).

 

[Bob Badour]

I believe this is a moot point when it comes to MS Access. The app and the
database are all stored together in Access. There is, by definition, only
one Access.

Access regulars, feel free to correct this if it's wrong.

While awkward, slow and kludgy, it is possible to attach an Access app
to an external database.

 

[David Cressey]

I think you may be asking too much of a natural key. I always took "stable"
to mean *almost* never changes ratherer than meaning absolutely immutable
(which I take to mean "intrinsically incapable of changing"). But whatever
one thinks it should mean, one always has to accommodate the possibility
that a key value will be mis-typed during manual entry by a user, and
therefore that it will have to be corrected. Mismanagement is, as you
rightly say, a fact of life.

You are right. Whether or not mismanagement is a sufficient reason for
distrust of a natural key depends on the degree of mismanagement. I should
have made that explicit in my earlier post.

But that's not usually a sufficiently good reason to introduce a synthetic
key. I have found it possible to tolerate quite a lot of updates to
supposedly stable keys using ON UPDATE CASCADE, with imperceptible
response-time costs. (One can of course measure the costs, but that's not
what counts.) A key would have to be really unstable to justify using a
synthetic key IMO.

One problem with cascading updates occurs when you want to relate data still
in the database to data that was extracted from the database at an earlier
point in time. This includes, but is not limited to, data in the log file.

 

[Brian Selzer]

This is a specific example of a syndrome that I described more generally:
the mismanagement of natural keys.

If a key is "natural", it is managed, if at all, beyond the scope of the
DBMS. It might be managed at the application layer, or it might be
managed
by people, or it might indeed be unmanaged data, like sunspots.

When it is managed by other people, it is subject to mismanagement.
Changing values that ought to be immutable is one of many ways that
natural
keys can be mismanaged.

As I said before, I prefer to use natural keys where ever possible. If
that's not possible due to mismanagement of the natural keys, I'll use
synthetic keys.

I don't think it's productive to blame it on the users. The problem you're
referring to is due to the nature of keys, and misunderstandings on the part
of the database designer as to what constitutes a key. All that is required
for a key to be a key is that in every possible database instance, a
projection over the attributes in the key for a relation has the same
cardinality as the relation. This does /NOT/ mean that a particular
combination of values /always/ identifies the same individual in the
Universe of Discourse, but only in the picture of the Universe that is a
database instance. In other words, a particular combination of values may
not /necessarily/ identify an individual, but rather may only /contingently/
identify an individual. 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.

 

[Sylvain Lafontaine]

Bof, if you live in a perfect world, one with infinite budget and infinite
time to do any project, then I understand your concerns.

However, in my case and probably in the case of Tony Toews - but I cannot
vouch for sur for him - I don't live in such a world. First of all, if I
were to live in a perfect world, I wouldn't have to work to earn a living in
the first place. Second, 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.

I have a client who has such a system at the moment as one critical part of
their business process, 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. 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? To give pleasure to people like Celko? If I
were to tell them that Celko would like to see them changing their system,
they would probably tell that if Celko was to bring them a check to pay for
the change, then maybe they would give it a try.

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

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

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

For you maybe.

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...").
Did I mention that I sincerely appreciate Tony's honesty?

Also consider the amount of posts we see in the Access groups asking
to reseeding autonumbers, gaps in sequences, etc. Wrong mental model,
perhaps, but the mentality certainly exists.

Jamie.

--

 

[Tony Toews [MVP]]

I acknowledge the fact I have nothing on you


Well, my quick search revealed you have called *yourself* an 'idiot'
far more often and recently admonished a regular for name calling. You
are a gentleman.

Ah, thanks. I had actually forgotten all that.

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]]

While awkward, slow and kludgy, it is possible to attach an Access app
to an external database.

And how is Access more awkward, slow and kludgy than other products such as say VB
6.0, VB.Net, or whatever?

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]]

I saw your earlier post on this and wondered if you were being
genuine. Now that I see you are...

Seriously, have you tried doing this kind of thing in Visual Studio
2005 i.e. using the .NET framework? I was helping a work colleague
(non-dev) recently who was using the free version of VS to do a simple
club subscriptions app, with data bound controls, navigation bar, etc
i.e. at a glance very reminiscent of Access Forms (except the look and
feel was much nicer <g>). I've also made initial investigations into
third party frameworks (i.e. extending .NET framework) to bind
business rules' logic to controls. If you believe that MS are not
evolving Access in the developer's interests (and I think you may well
be correct) and at the same time are making .NET Windows Forms
development more accessible (pun intended), maybe it's time to re-
evaluate your personal direction...

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>

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/

 

[James A. Fortune]

A typical schema with artificial keys:

Customer {CustomerKey, CustomerNo, ...}
Key {CustomerKey}, Key {CustomerNo}

Item {ItemKey, ItemNo, ...}
Key {ItemKey}, Key {ItemNo}

CI {CustomerItemKey, CustomerKey, ItemKey, CustomerItemNo}
Key {CustomerItemKey}, Key {CustomerKey, ItemKey}
CI[ItemKey] IN Item[ItemKey]
CI[CustomerKey] IN Customer[CustomerKey]

SOLine {SOLineKey, SOKey, SOLineNo, CustomerItemKey, Quantity, Price}
Key {SOLineKey}, Key {SOKey, SOLineNo}
SOLine[CustomerItemKey] IN CI[CustomerItemKey]


A typical schema with natural keys

Customer {CustomerNo, ...}
Key {CustomerNo}

Item {ItemNo, ...}
Key {ItemNo}

CI {CustomerNo, ItemNo, CustomerItemNo}
KEY {CustomerNo, ItemNo}
CI[CustomerNo] IN Customer[CustomerNo]
CI[ItemNo] IN Item[ItemNo]

SOLine {SO#, SOLineNo, CustomerNo, ItemNo, Quantity, Price}
SOLine[CustomerNo, ItemNo] IN CI[CustomerNo, ItemNo]


Now write a query that returns how many of item '12345' were sold to
customer '4321'

It should be obvious that with the natural keys, no joins are
necessary--it's just a simple select from SOLine since all of the
information is actually /in/ SOLine; whereas, with the artifical keys,
several joins are required because in order to query by item number and
customer number, SOLine must be joined to CI which must then be joined to
Customer and Item.

Brian,

Thanks for attempting a concrete example to show your point. Although
the first schema doesn't look like something I would have created I will
go over your example and post back later, perhaps when the dust has
settled a bit in this thread. In spite of my sometimes unorthodox ways
I am a reasonable person who is always looking for better ways to do things.

James A. Fortune
(e-mail address removed)

 

[Brian Selzer]

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.

 

[David W. Fenton]

m:

*Sigh*. Yes, but as bob has pointed out, you've misconstrued my
point. Because it is marketed at different business problems (ones
with few concurrent users, simple domains, comparatively smaller
schema), a lot of Access users can get away with mistakes that
someone using, say, Oracle 11g to keep track of millions of facts
would in the end get called up on. So that's nothing to do with
the technology, just the market, which makes your empassioned
defence of the super-duper jetomatic engine a bit misplaced.

I'm not defending Jet here. I'm pointing out a logical error in your
attributing to "Access" something that has nothing specifically to
do with Access.

I'll also ignore the diatribe that followed in light of your
misunderstanding. (And the fact that you share my mother's maiden
name, so may well be long distant family...).

I do not misunderstand. You clearly are not distinguishing a
development platform (Access) from a database engine (Jet) and from
the fact that the issue at hand is a schem design question, and has
nothing whatsoever to do with Access, or with any particular
database engine.

That you can't seem to keep this distinction straight in your posts
shows one of two things:

1. massive ignorance of the tools you are disparaging

2. extremely bad writing skills.

Of maybe it's some of both.

 

[Rick Brandt]

I believe this is a moot point when it comes to MS Access. The app
and the database are all stored together in Access. There is, by
definition, only one Access.

Access regulars, feel free to correct this if it's wrong.

It's wrong

It is the norm for a single-user non-serious app built largely by using the
wizards.

It is the opposite of the norm for a multi-user app built by someone who knows
what they are doing.

 

[Rick Brandt]

While awkward, slow and kludgy, it is possible to attach an Access app
to an external database.

There are a great many of us who almost exclusively build Access apps that use
server back ends. Mine use SQL Server and UDB400 on the IBM ISeries. I only
use MDBs to store data in single-user desktop apps.

There is nothing awkward, slow, or kludgy about it.

 

[Bob Badour]

There are a great many of us who almost exclusively build Access apps that use
server back ends. Mine use SQL Server and UDB400 on the IBM ISeries. I only
use MDBs to store data in single-user desktop apps.

There is nothing awkward, slow, or kludgy about it.

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.

 

[Bob Badour]

m:


I'm not defending Jet here. I'm pointing out a logical error in your
attributing to "Access" something that has nothing specifically to
do with Access.

You are an idiot. Jim didn't attribute anything to Access. In fact, he
said it would be wrong to let an ignoramus blame his ignorance on the tool.

I do not misunderstand.

Then I can only conclude you lack the capacity to comprehend the
relatively simple written english that appears above.

[irrelevancies snipped]

 

[Rick Brandt]

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.

I switch the database instance all the time between development and live. Takes
longer to describe than it does to perform.

 

[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
(e-mail address removed)

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

 

[Brian Selzer]

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.

What do you mean? What I have for identification is a driver's license. It
has a driver's license number on it, which singles me out as a driver in my
State. Several years ago, I had a different driver's license with a
different driver's license number on it, but that one expired. The one I
have now will also expire. Obviously, a driver's license number isn't a
permanent identifier: are you then saying that a driver's license isn't
sufficient for identification? I also have a voter's identification card,
which has a number that singles me out as a voter in my State, locality and
precinct. I had a different one a couple years ago, and I'll get a
different one in a couple years. Are you saying that my voter's
identification card isn't sufficient for identification? According to the
Board of Elections, all I have to do is present it in order to vote. If I
didn't have one, I would have to produce a driver's license or State
identification along with a utility bill.

A functional dependency is a statement that says that whenever two tuples
from the same relation agree on values for one set of attributes, they must
also agree on values for another set of attributes. Note that it doesn't
say that whenever two tuples agree, but rather that whenever two tuples
/from the same relation/ agree.

 

[Keith Wilby]

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

 

[Frank Hamersley]

True - but N/A to moi!

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.

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

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!

None of the aforementioned
die-hards even know what a split Access application is.

Cheers, Frank.