A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Separate PK in Jxn Tbl?



 
 
Thread Tools Display Modes
  #131  
Old January 28th, 2008, 03:58 PM posted to comp.databases.ms-access, comp.databases.theory,microsoft.public.access, microsoft.public.access.tablesdbdesign,microsoft.public.sqlserver
Jamie Collins
external usenet poster
 
Posts: 1,705
Default Separate PK in Jxn Tbl?

On Jan 28, 2:03 pm, "David Cressey" wrote:
What if there's more than one application built on top of the
database?


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.


The properties of the Access user interface clearly need to be stored
somewhere and the place is in an mdb (or equivalent) file. Jet table
data is also persisted in mdb files. Yes, in a single user,
traditional Access/Jet application, this is likely to be the same file
as the Jet tables. However, in a multiuser Access/Jet application set
up is most common to have the Forms, Reports, etc ('front end')
'split' from the 'back end; requiring a FE/BE split is one of the
Access Ten Commandments (I kid you not). Typically, each user machine
will have its own copy of a 'front end', therefore in such a situation
there are indeed multiple applications accessing the same 'back end'.

Jamie.

--

  #132  
Old January 28th, 2008, 04:12 PM posted to comp.databases.ms-access, comp.databases.theory,microsoft.public.access, microsoft.public.access.tablesdbdesign,microsoft.public.sqlserver
Jamie Collins
external usenet poster
 
Posts: 1,705
Default Separate PK in Jxn Tbl?

On Jan 28, 3:10 am, "Tony Toews [MVP]" wrote:
chuckle You almost got me there. However I was ridiculuing other people who
weren't part of the conversation.


I acknowledge the fact I have nothing on you

Not much of an excuse.


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.

Jamie.

--

  #133  
Old January 28th, 2008, 05:09 PM posted to comp.databases.ms-access,comp.databases.theory,microsoft.public.access,microsoft.public.access.tablesdbdesign,microsoft.public.sqlserver
David Cressey[_2_]
external usenet poster
 
Posts: 50
Default Separate PK in Jxn Tbl?


"Roy Hann" wrote in message
...
"David Cressey" wrote in message
news:%9lnj.5893$cm6.4751@trndny05...

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


  #134  
Old January 28th, 2008, 06:02 PM posted to comp.databases.ms-access,comp.databases.theory,microsoft.public.access,microsoft.public.access.tablesdbdesign,microsoft.public.sqlserver
Roy Hann
external usenet poster
 
Posts: 25
Default Separate PK in Jxn Tbl?

"David Cressey" wrote in message
news:U6nnj.8$1f.5@trndny02...

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.


Good points, and I will always concede that these are the kinds of external
factors that in practice force one into using more synthetic keys than we'd
like. Happily in my case the problem isn't too bad because we have a very
nice journal analyzer that quickly reveals any key updates.

I hope I have also been pretty careful to warn people that creating
redundant copies of data outside the database will cause all the problems
one always associates with redundant copies of data. We tend to do a lot of
refreshes instead of hoping our snapshots haven't mutated too badly. (In a
way it's been a chicken and egg solution: we never allowed the egg, so we
don't have to deal with the chicken! :-) We also make use of replication
in several systems, and to our replicator an updated key is just another
update.

But to repeat, I do understand synthetic keys will sometimes be forced on
me. I don't insist they can always be avoided. They are my just last
resort, not my first.

Roy


  #135  
Old January 28th, 2008, 06:24 PM posted to comp.databases.ms-access,comp.databases.theory,microsoft.public.access,microsoft.public.access.tablesdbdesign,microsoft.public.sqlserver
Brian Selzer
external usenet poster
 
Posts: 32
Default Separate PK in Jxn Tbl?


"David Cressey" wrote in message
news:%9lnj.5893$cm6.4751@trndny05...

"Sylvain Lafontaine" sylvain aei ca (fill the blanks, no spam please)
wrote in message ...

This is the big problem with natural keys. When the theory was first
elaborated, the point was that a natural key never change its value.
That
was the point that was making the choice of a natural key on par with the
use of a surrogate key as the primary key of a table. However, like

anyone
have discovered with experience, a natural key can change its value under

a
set of various circonstances. One could argue that if a key can change

its
value, than it's not a natural key but as you know, this argument bring
nothing in regard to help you choosing a natural key.


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.


  #136  
Old January 28th, 2008, 06:30 PM posted to comp.databases.ms-access,comp.databases.theory,microsoft.public.access,microsoft.public.access.tablesdbdesign,microsoft.public.sqlserver
Sylvain Lafontaine
external usenet poster
 
Posts: 528
Default Separate PK in Jxn Tbl?

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)


"Jamie Collins" wrote in message
...
On Jan 27, 9:59 pm, "Sylvain Lafontaine" sylvain aei ca (fill the
blanks, no spam please) wrote:
« 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.

--


  #137  
Old January 28th, 2008, 07:36 PM posted to comp.databases.ms-access,comp.databases.theory,microsoft.public.access,microsoft.public.access.tablesdbdesign,microsoft.public.sqlserver
Tony Toews [MVP]
external usenet poster
 
Posts: 3,776
Default Separate PK in Jxn Tbl?

Jamie Collins wrote:

chuckle You almost got me there. However I was ridiculuing other people who
weren't part of the conversation.


I acknowledge the fact I have nothing on you

Not much of an excuse.


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/
  #138  
Old January 28th, 2008, 07:38 PM posted to comp.databases.ms-access,comp.databases.theory,microsoft.public.access,microsoft.public.access.tablesdbdesign,microsoft.public.sqlserver,microsoft.public.access
Tony Toews [MVP]
external usenet poster
 
Posts: 3,776
Default Separate PK in Jxn Tbl?

Bob Badour wrote:

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.


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/
  #139  
Old January 28th, 2008, 07:41 PM posted to comp.databases.ms-access,comp.databases.theory,microsoft.public.access,microsoft.public.access.tablesdbdesign,microsoft.public.sqlserver
Tony Toews [MVP]
external usenet poster
 
Posts: 3,776
Default Separate PK in Jxn Tbl?

Jamie Collins wrote:

On Jan 28, 3:38 am, "Tony Toews [MVP]" wrote:
I'd very much like to see a business rules layer implemented that worked well within
Access. However I rather much doubt we'll ever see it given that Microsoft is
focusing their attentions on the office information worker rather than the developer.


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/
  #140  
Old January 28th, 2008, 09:49 PM posted to comp.databases.ms-access,comp.databases.theory,microsoft.public.access,microsoft.public.access.tablesdbdesign,microsoft.public.sqlserver
Roy Hann
external usenet poster
 
Posts: 25
Default Separate PK in Jxn Tbl?

"Brian Selzer" wrote in message
news

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.


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

Roy


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:52 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.