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
  #61  
Old January 26th, 2008, 11:57 AM posted to comp.databases.ms-access,comp.databases.theory,microsoft.public.access,microsoft.public.access.tablesdbdesign,microsoft.public.sqlserver
Neil
external usenet poster
 
Posts: 311
Default Separate PK in Jxn Tbl?

Bob,

Here's a programming tip for you: prozac.


"Bob Badour" wrote in message
...
Larry Daugherty wrote:

Every dog gets one bite. Then they go into the bs/Kill file.

Did you note the net contribution to the subject at hand? Their
commentary serves only to identify and characterize *themselves*

My hope is to make meaningful contributions to those who are actually
developing applications or learning to do so.


Your hope is futile in the face of your ignorance and laziness.


Keep up the good work!


I'll try.


"Tony Toews [MVP]" wrote in message
...

"Larry Daugherty" wrote:


This boil up is a variation of the "Autonumber vs. Natural Key"
religious wars that sweep the Access groups on even numbered(?)
years.
In the meantime OP is probably trying to hide the matches with which
he started the fires....



chuckle Yup, it's amazing how this all happens. To me the

particularly amusing
part is that we're generally quite civil in our discussions that

stay in the Access
groups.


Ignorance is bliss.


I'm saddened, although not surprised, at the comments from a few

regulars from the
c.d.theory newsgroup using words such as idiot, imbecile and

invincibly ignorant.

Precious few pleasant ways exist to express unpleasant truths. However,
intellectual honesty requires the expression of truths regardless of
appeal to oneself or to others.



  #62  
Old January 26th, 2008, 12:00 PM posted to comp.databases.ms-access,microsoft.public.access,microsoft.public.access.tablesdbdesign,microsoft.public.sqlserver
Neil
external usenet poster
 
Posts: 311
Default Separate PK in Jxn Tbl?


"Jamie Collins" wrote in message
...
On Jan 23, 12:45 am, "Neil" wrote:
I've done both; the separate primary key is (strictly speaking) never
necessary, but it can be handy if the junction table is itself related
one-to-many to an additional table or tables. Access doesn't make
multifield
foreign keys all that easy to use (e.g. you can't use them in a
combobox
without some messy code).


Good to know. That makes sense, about needing the PK to refer separately
to
the junction table, if that situation exists.


Why do you say "needing the PK"? JohnV no doubt makes a good case when
considering bound controls in Access but I would point out that he
also said the PK is "never necessary, but it can be handy".



OK, I'll rephrase:

"That makes sense, about the PK coming in handy to refer spearately to the
junction table, if that situation exists."

Better? :-)


  #63  
Old January 26th, 2008, 12:00 PM posted to comp.databases.ms-access,comp.databases.theory,microsoft.public.access,microsoft.public.access.tablesdbdesign,microsoft.public.sqlserver
Neil
external usenet poster
 
Posts: 311
Default Separate PK in Jxn Tbl?


"Larry Daugherty" wrote in message
...
In the meantime OP is probably trying to hide the matches with which
he started the fires....


Indeed. :-|


  #64  
Old January 26th, 2008, 01:26 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?


wrote in message
...
On Jan 25, 9:12 am, Jamie Collins wrote:
(quote)
What part of simpler don't you understand :-). Only one expression in
the ON is simpler. Needing less indexes is simpler. Not having to
look for your multi-key fields is easier, although your point that
Relationships can handle that is valid. If the AutoNumber key has a
one-to-one relationship with the multi-key fields then it's fine to
use it. There's no down side that I can see. I also like to rely on
coding to detect inconsistent data rather than on error trapping, so I
have to check the multi-key values anyway before adding a new record.
I think that your idea about enforcing constraints at both the table
level and in code is an excellent idea. The OP wanted to know what
people did and why. I still don't see any reason put forward for me
to change to a multi-field key. Are totals queries easier when multi-
field keys are used? BTW, "reduced the amount of denormalization"
works just as well. Real databases experience denormalizing
influences.

(end quote)

Simplicity is in the eye of the beholder.

I think it's simpler to rely on constraints enforced by the DBMS to prevent
duplicate entries
than it is to write code to accomplish the same thing.

When you want to delete an entry form a junction table, you almost always
know the two FKs that uniquely determine the entry to be deleted. You
almost never know the value of the superflous surrogate key. So it's simple
to use the two FK's as the criterion for deletion than it is to look up the
ID field, and then use that as the basis for deletion.

The above comment depends entirely on how you go about organizing you code.
I like to keep my code simple. At least "simple" in my own eyes.

The use of multi-key fields in star schemas doesn't make individual queries
any simpler. But it expands by orders of magnitude the number of different
combinations that can be used as selection criteria when computing totals or
other aggregates. This makes the entire system simpler, on a lerger scale.

I realize that star schema discussions may be out of place in MS access
newsgroups. The same is true in c.d.t. There is almost nothing of
theoretical interest in star schemas.




  #65  
Old January 26th, 2008, 05:07 PM posted to comp.databases.ms-access,comp.databases.theory,microsoft.public.access,microsoft.public.access.tablesdbdesign,microsoft.public.sqlserver
Bob Badour
external usenet poster
 
Posts: 38
Default Separate PK in Jxn Tbl?

Roy Hann wrote:

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

To that, I would add that the increased simplicity of using a surrogate
(or artificial or autonumber) key as the primary key in place of a
composite key is only half their advantage.

The biggest problem that I have with composite keys is that they share the
same fundamental problem as natural keys: using them as the primary key is
allowing the fact that a primary key can change its value over time.
IMHO, a primary key should never be allowed to change its value once it
has been created; a assumption which will forbid the use of a composite
key in many cases. (Of course, if you don't mind to see a primary key
changing its value after its creation then you are not concerned by this
argument.).



I have decided not to respond to this post in detail because there isn't a
single point it makes that I agree with (as stated). I really don't know
where to start.


That, in a nutshell, is Date's _Principle of Incoherence_.


One specific comment I will make is that my progression has
been the opposite of yours. I once used synthetic keys everywhere, but now
try to limit my use of them, with wholly beneficial effects. That may be
why I write about this with the fervor of a born-again convert.

I would have a lot more sympathy for these kinds of claims if the people
making them would give any hint that they know what the alternatives are,
and why their solutions make sense within application development tools.
For example, I don't think I've ever seen anyone enthusing about the liberal
use of synthetic keys who also noted that they are aware of the possibility
of declaring a foreign key constraint WITH ON UPDATE CASCADE. If you can
persuade me you've looked at it and had to reject for reasons X, Y, and Z, I
can respect that. Or tell me you know about it but your particular product
doesn't support it and I can respect that. Or tell me that you understand
that the DBMS handles the problem almost trivially but the application
development tools make you write extra code and I can respect that.

And what is all this tripe about composite keys making the SQL more complex?
If I bodge up my tables with a spurious third synthetic key (skey) so that
instead of writing:

select i.description, b.cause_of_damage
from orderitems i left join breakages b
using (ordernr,itemnr)

I can instead write:

select i.description, b.cause_of_damage
from orderitems i left join breakages b
on i.skey = b.skey

How much easier is THAT?? And at what cost?

If you don't convince me that you properly understand the problems, and
crucially, where the problems *really* lie, then you aren't going to
convince me that your solutions are anything but cut-and-paste rote-learned
hackery that seems elegant/sensible only to those with limited knowledge of
very limited products. You have to establish that you are credible.
Merely claiming years and years of experience (as others have)


I have found some people can work for 10 years and get a year's
experience 10 times.


could just
mean they've been successfully getting away with being incompetent--and
goodness knows, that happens, so they won't get the benefit of the doubt.

Roy

  #66  
Old January 26th, 2008, 05:58 PM posted to comp.databases.ms-access,comp.databases.theory,microsoft.public.access,microsoft.public.access.tablesdbdesign,microsoft.public.sqlserver
Bob Badour
external usenet poster
 
Posts: 38
Default Separate PK in Jxn Tbl?

Hi Sylvain,

First, let me thank you for being so kind as to volunteer the
information that you are a Most Vociferous Person (MVP). It does a fair
service to the world when the self-aggrandizing ignorants self-declare
that information.

Sylvain Lafontaine wrote:

To that, I would add that the increased simplicity of using a surrogate (or
artificial or autonumber) key as the primary key in place of a composite key
is only half their advantage.


At this point, a prudent man would Plonk! you while mentally citing
Date's _Principle of Incoherence_. Never the prudent man, instead, I
observe the absurdity of your suggestion that adding features,
structures or attributes increases simplicity. What nonsense!


The biggest problem that I have with composite keys is that they share the
same fundamental problem as natural keys: using them as the primary key is
allowing the fact that a primary key can change its value over time. IMHO,
a primary key should never be allowed to change its value once it has been
created; a assumption which will forbid the use of a composite key in many
cases.


I find your absolutism foolish suggesting ignorance and/or stupidity.

The design criteria for keys a uniqueness, irreducibility,
simplicity, stability and familiarity (in no particular order). If any
criterion is absolute, it is uniqueness not stability.


(Of course, if you don't mind to see a primary key changing its
value after its creation then you are not concerned by this argument.).

This is not only a theoritical argument as many interfaces - like Access -
won't like to see a primary key that could change it value.


It is not a theoretical argument at all. You simply regurgitate
ignorance and stupidity.

[remaining nonsense snipped]

Plonk!
  #67  
Old January 26th, 2008, 06:05 PM posted to comp.databases.ms-access,comp.databases.theory,microsoft.public.access,microsoft.public.access.tablesdbdesign,microsoft.public.sqlserver
Bob Badour
external usenet poster
 
Posts: 38
Default Separate PK in Jxn Tbl?

David Cressey wrote:

wrote in message
...
On Jan 25, 9:12 am, Jamie Collins wrote:
(quote)
What part of simpler don't you understand :-). Only one expression in
the ON is simpler. Needing less indexes is simpler. Not having to
look for your multi-key fields is easier, although your point that
Relationships can handle that is valid. If the AutoNumber key has a
one-to-one relationship with the multi-key fields then it's fine to
use it. There's no down side that I can see. I also like to rely on
coding to detect inconsistent data rather than on error trapping, so I
have to check the multi-key values anyway before adding a new record.
I think that your idea about enforcing constraints at both the table
level and in code is an excellent idea. The OP wanted to know what
people did and why. I still don't see any reason put forward for me
to change to a multi-field key. Are totals queries easier when multi-
field keys are used? BTW, "reduced the amount of denormalization"
works just as well. Real databases experience denormalizing
influences.

(end quote)

Simplicity is in the eye of the beholder.


I tend to disagree. I suspect one can quantify simplicity and complexity.


I think it's simpler to rely on constraints enforced by the DBMS to prevent
duplicate entries
than it is to write code to accomplish the same thing.


Using the dbms uses fewer tools, fewer concepts, fewer computational
models, fewer structures, fewer machines. I suggest the observed
simplicity is more than a matter of perspective or opinion.

[further demonstrations of simplicity snipped]
  #68  
Old January 26th, 2008, 08:48 PM posted to comp.databases.ms-access, comp.databases.theory,microsoft.public.access, microsoft.public.access.tablesdbdesign,microsoft.public.sqlserver
Marshall[_3_]
external usenet poster
 
Posts: 20
Default Separate PK in Jxn Tbl?

On Jan 26, 4:26 am, "David Cressey" wrote:

When you want to delete an entry form a junction table, you almost always
know the two FKs that uniquely determine the entry to be deleted. You
almost never know the value of the superflous surrogate key. So it's simple
to use the two FK's as the criterion for deletion than it is to look up the
ID field, and then use that as the basis for deletion.


Yes, exactly.

One of the greatest benefits, and one of the fundamental
differences between how SQL treats data and how
(most) conventional programming languages treat data
is that in SQL we specify data by its value, instead of by
location. I often observe that superfluous keys in the field
are an attempt to make SQL data have an address, to
make it behave the way the programmer's mental model
(perhaps influenced by years of using pointers) does.


Marshall
  #69  
Old January 27th, 2008, 12:38 AM 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?

Your argument about the use of a DRI WITH ON UPDATE CASCADE is an
interesting argument and one that come back often; however it's not a silver
buller. First of all, it's another level of complexity that you must add to
the design of your database; ie, you must make sure that they are all there
and no one is missing. Second, this DRI cannot be used with cyclic
relationship with SQL-Server but with Oracle, you can. (From your example,
I believe that you are working with Oracle). On SQL-Server, you must use
triggers to implement such a feature when there is a cyclic relationship.
Of course, when you are dealing with tens and hundreds of relationships,
this can quickly translate into a nightmare. There is also the qestion of
the diminution of performance and of general design: when you have to update
multiples records on multiple tables for what should be the change of a
single value in a single table make it hard to believe that this is a proper
normalized database design and this situation quickly worsen if you have to
take into account the correspondance with backups, reports and linked
databases; all systems for which there is no automatic DRI.

But why make it simpler when you can make it harder?

Finally, I don't understand your example at all. You are introducing us to
the NATURAL JOIN and USING statement that have been introduced by Oracle in
its 9i version (also in MySQL and Postgres, I believe) but I fail to see
what this has to do with the subject of this thread; the use of a separate
PK in a junction table and its highly related topic, ie. the use of natural
keys versus the use of surrogate keys. There is no relationship at all
between a NATURAL JOIN and a natural key and the Natural Join can be used as
easily with a surrogate key than with a natural key. The only thing that is
important with the Natural Join is the name of the key. (BTW, if you were
to ask me what I'm thinking about this little monstruosity, I would tell you
that this is a perfect example of a Pandora box.).

And finally, a for your request of asking me to convince you that I'm
properly understand the problem here of to etablish that I'm credible: I can
tell you that I have absolutely no intention of doing it and that I have
absolutely no interest at all about what you are thinking of me. The only
things that are of interest to me are the arguments that I'm seeing posted
here - whatever the people who might write them - but for someone who has
just make a confusion between a natural key and the NATURAL JOIN, asking for
such a thing make it looks very strange.

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


"Roy Hann" wrote in message
...
"Sylvain Lafontaine" sylvain aei ca (fill the blanks, no spam please)
wrote in message ...
To that, I would add that the increased simplicity of using a surrogate
(or artificial or autonumber) key as the primary key in place of a
composite key is only half their advantage.

The biggest problem that I have with composite keys is that they share
the same fundamental problem as natural keys: using them as the primary
key is allowing the fact that a primary key can change its value over
time. IMHO, a primary key should never be allowed to change its value
once it has been created; a assumption which will forbid the use of a
composite key in many cases. (Of course, if you don't mind to see a
primary key changing its value after its creation then you are not
concerned by this argument.).


I have decided not to respond to this post in detail because there isn't a
single point it makes that I agree with (as stated). I really don't know
where to start. One specific comment I will make is that my progression
has been the opposite of yours. I once used synthetic keys everywhere,
but now try to limit my use of them, with wholly beneficial effects. That
may be why I write about this with the fervor of a born-again convert.

I would have a lot more sympathy for these kinds of claims if the people
making them would give any hint that they know what the alternatives are,
and why their solutions make sense within application development tools.
For example, I don't think I've ever seen anyone enthusing about the
liberal use of synthetic keys who also noted that they are aware of the
possibility of declaring a foreign key constraint WITH ON UPDATE CASCADE.
If you can persuade me you've looked at it and had to reject for reasons
X, Y, and Z, I can respect that. Or tell me you know about it but your
particular product doesn't support it and I can respect that. Or tell me
that you understand that the DBMS handles the problem almost trivially but
the application development tools make you write extra code and I can
respect that.

And what is all this tripe about composite keys making the SQL more
complex? If I bodge up my tables with a spurious third synthetic key
(skey) so that instead of writing:

select i.description, b.cause_of_damage
from orderitems i left join breakages b
using (ordernr,itemnr)

I can instead write:

select i.description, b.cause_of_damage
from orderitems i left join breakages b
on i.skey = b.skey

How much easier is THAT?? And at what cost?

If you don't convince me that you properly understand the problems, and
crucially, where the problems *really* lie, then you aren't going to
convince me that your solutions are anything but cut-and-paste
rote-learned hackery that seems elegant/sensible only to those with
limited knowledge of very limited products. You have to establish that
you are credible. Merely claiming years and years of experience (as others
have) could just mean they've been successfully getting away with being
incompetent--and goodness knows, that happens, so they won't get the
benefit of the doubt.

Roy



  #70  
Old January 27th, 2008, 03:09 AM posted to comp.databases.ms-access,comp.databases.theory,microsoft.public.access,microsoft.public.access.tablesdbdesign,microsoft.public.sqlserver
James A. Fortune
external usenet poster
 
Posts: 903
Default Separate PK in Jxn Tbl?

Marshall wrote:
On Jan 26, 4:26 am, "David Cressey" wrote:

When you want to delete an entry form a junction table, you almost always
know the two FKs that uniquely determine the entry to be deleted. You
almost never know the value of the superflous surrogate key. So it's simple
to use the two FK's as the criterion for deletion than it is to look up the
ID field, and then use that as the basis for deletion.



Yes, exactly.

One of the greatest benefits, and one of the fundamental
differences between how SQL treats data and how
(most) conventional programming languages treat data
is that in SQL we specify data by its value, instead of by
location. I often observe that superfluous keys in the field
are an attempt to make SQL data have an address, to
make it behave the way the programmer's mental model
(perhaps influenced by years of using pointers) does.


Marshall


Personally, I don't take the natural keys out either, so they can still
be used for the deletion. The thought of giving the SQL data an address
and following a programmer's mental model did not enter into my thinking
at all. I am not trying to give the data an order either. You've been
listening to Celko too much. Because of his overall manner, which I
find quite offensive, I don't even want to listen to him when he's right
:-). Is the AutoNumber primary key a denormalization of the schema?
Yes. Is it added for a reason? Yes again. I'm still waiting for a
cogent reason for me to go to using natural keys.

James A. Fortune

 




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 01:41 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.