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
  #81  
Old January 27th, 2008, 02:46 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?


"James A. Fortune" wrote in message
...

Personally, I don't take the natural keys out either, so they can still
be used for the deletion.


There are really two issues being discussed in a single discussion here.

The issue of synthetic keys versus natural keys is one issue. The issue of
a composite PK in a junction table, made up of FKs, versus a new simple key
is a separable issue.

Please note that, if the two FKs under discussion both reference synthetic
PKs, all of your arguments concerning the problems of dealing with natural
keys become moot.

If we have three tables, Students, Courses, and Enrollments, where
enrollments is a junction between Students and Courses, we could have a
synthetic key, StudentID for students, and a synthetic key, CourseID, for
Courses.

The question then remains which is simpler. To define enrollments with a
composite key
(StudentID, CourseID), or to define a new synthetic key, EnrollmentID.
Neither of these two solutions uses natural keys.

I prefer to se natural keys whenever possible, but I use synthetic keys
when natural ones just won't do. When do natural keys fail to do the job?
When the poeple who control them are mismanaging them.







  #82  
Old January 27th, 2008, 02:53 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?


"Brian Selzer" wrote in message
. ..

Well, that's just dumb. Checks in code can reduce database round-trips,

and
therefore can improve performance, but are not and cannot be a substitute
for constraints on the tables. It is the constraints on the tables that
keeps garbage out of the database.


The idea of keeping garbage out of the database takes on an entirely
different meaning if you are dealing with hundreds of programs written in
COBOL, Java, or anything in between accessing a single Oracle database on
the one hand. On the other hand, if you are a developer creating a self
contained MS Access database cum application (tables, queries, forms,
reports, modules, etc.) all in one file, the same issues arise, but they
are resolved quite differently.

I'm not saying either one is "right" or "wrong". I'm just suggesting why an
objection that makes perfect sense to you and me might be lost on the MS
Access community.



  #83  
Old January 27th, 2008, 03:18 PM posted to comp.databases.ms-access,comp.databases.theory,microsoft.public.access,microsoft.public.access.tablesdbdesign,microsoft.public.sqlserver
Rick Brandt
external usenet poster
 
Posts: 4,354
Default Separate PK in Jxn Tbl?

David Cressey wrote:
The idea of keeping garbage out of the database takes on an entirely
different meaning if you are dealing with hundreds of programs
written in COBOL, Java, or anything in between accessing a single
Oracle database on the one hand. On the other hand, if you are a
developer creating a self contained MS Access database cum
application (tables, queries, forms, reports, modules, etc.) all in
one file, the same issues arise, but they are resolved quite
differently.

I'm not saying either one is "right" or "wrong". I'm just suggesting
why an objection that makes perfect sense to you and me might be lost
on the MS Access community.

^
some of

I think if a thorough poll was done it would show that the majority of
professional Access developers (those that make their living at it) would agree
that data integrity rules should be enforced by the database engine wherever
that is possible.

The fact is that Access is a tool predominantly for *users*, not developers, and
Microsoft appears determined with each subsequent version to make that more the
case. The majority of changes make it easier to do things incorrectly because
that makes the program easier to use for people who have no idea what they are
doing. Since that group vastly outnumbers the other one can hardly argue with
their logic from a business standpoint.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com



  #84  
Old January 27th, 2008, 04:06 PM posted to comp.databases.ms-access, comp.databases.theory,microsoft.public.access, microsoft.public.access.tablesdbdesign,microsoft.public.sqlserver
JOG
external usenet poster
 
Posts: 30
Default Separate PK in Jxn Tbl?

On Jan 27, 2:18 pm, "Rick Brandt" wrote:
David Cressey wrote:
The idea of keeping garbage out of the database takes on an entirely
different meaning if you are dealing with hundreds of programs
written in COBOL, Java, or anything in between accessing a single
Oracle database on the one hand. On the other hand, if you are a
developer creating a self contained MS Access database cum
application (tables, queries, forms, reports, modules, etc.) all in
one file, the same issues arise, but they are resolved quite
differently.


I'm not saying either one is "right" or "wrong". I'm just suggesting
why an objection that makes perfect sense to you and me might be lost
on the MS Access community.


^
some of

I think if a thorough poll was done it would show that the majority of
professional Access developers (those that make their living at it) would agree
that data integrity rules should be enforced by the database engine wherever
that is possible.


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.


The fact is that Access is a tool predominantly for *users*, not developers, and
Microsoft appears determined with each subsequent version to make that more the
case. The majority of changes make it easier to do things incorrectly because
that makes the program easier to use for people who have no idea what they are
doing. Since that group vastly outnumbers the other one can hardly argue with
their logic from a business standpoint.


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


--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com


  #85  
Old January 27th, 2008, 04:29 PM posted to comp.databases.ms-access, comp.databases.theory,microsoft.public.access, microsoft.public.access.tablesdbdesign,microsoft.public.sqlserver
JOG
external usenet poster
 
Posts: 30
Default Separate PK in Jxn Tbl?

On Jan 27, 3:06 am, "James A. Fortune"
wrote:
JOG wrote:
On Jan 27, 2:09 am, "James A. Fortune"
wrote:


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.


* Artificial keys allow you to enter the exact same statement of fact
twice. This would simply be nonsense.
* Artificial keys allows a tuple at t1 and a tuple at t2 to be
corresponded to each other, even if they don't have a _single_
attribute from the real world in common. This would also simply be
nonsense.


Access programmers use forms to interact with the data. If I follow
Jamie's advice and constrain the data at both the table level and in
code, then your points make more sense. Right now, they're just
arguments for me not to constrain the data at the table level because
the reasons you gave might make natural keys preferable in that
situation :-).


I'd suggest you read Rick's comments James. To be honest, its database
101 really that shared databases should handle data integrity
centrally. But I'm glad you see the logic of not allowing duplicates,
etc
(I've encountered people who don't...)


* Referencing an artificial key in a child table can complicates
queries - and not just with a longer restrict clause, but with a whole
extra join that may well have been unrequired if a natural key had
been used.


I don't agree with that point. The child table can contain the
AutoNumber primary key from the main table as a foreign key if desired.
I don't see how using the natural key fields requires less joins than
that. Maybe an example would help me understand what you mean.


No probs, although off the top of my head its gonna be a bit
contrived. With an artificial key:

Marriages {id, husband, wife, date}
Kids_from_Marriage {from_id, name, birth}

A query that asks "fetch me all the children whose mother is x"
obviously requires an equijoin, matching Marriages.id and
Kids.from_id. However with the original natural keys:

Marriages {id, husband, wife, date}
Kids_from_Marriage {mother, father, name, birth}

The same query is a simple select. That certainly seems a lot less
complicated to me



So not one, but three cogent reasons of the top of my head. I wouldn't
say there are never cases when an artificial key is useful, but they
certainly shouldn't be hidden, and adding them blindly to every
relation is surely just a bit silly. Regards, J.


Did I imply that that's what I do?


Erm, thats the impression you seem to give. Tony Toes certainly stated
he encouraged blindly adding artificial keys to everything. However
with duplicates, the extra reason's that Brian has given, the above
examples and the recommendations from access pro's that you shouldn't
rely on code to maintain integrity, I hopefully can assume that you've
at least seen the strength of the arguments.


James A. Fortune


  #86  
Old January 27th, 2008, 07:16 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, 6:09 pm, "James A. Fortune"
wrote:
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.


Personally, I don't take the natural keys out either, so they
can still be used for the deletion.


Since natural keys are data that needs to be managed, this
is an unsurprising claim. I don't throw out the data I'm supposed
to be managing either. Also consider this thread is about junction
tables. Imagine what you would have left if you threw out the
natural key of a junction table: nothing! Just the capriciously
introduced surrogate key in a table by its lonesome self.

Perhaps I've just stumbled into a sanity check for table
design: every projection of a table should have a meaningful
predicate. (Do I hear JOG's ears pricking up?) In a junction
table in which a surrogate key has been introduced, what's
the meaning of the projection of the table over the s.k. column?
Oops!


The thought of giving the SQL data an address and
following a programmer's mental model did not enter
into my thinking at all.


Your not being consciously aware of it doesn't mean
it didn't happen. The mind doesn't work like that.


You've been listening to Celko too much.


Who? ;-)


Marshall
  #87  
Old January 27th, 2008, 07:18 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, 10:01 pm, wrote:

If the users only access the tables through forms, conforming to best
practices in Access, how are they going to get garbage into the
tables?


Users aren't the only ones who update tables.


Marshall
  #88  
Old January 27th, 2008, 07:25 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, 11:13 pm, "Brian Selzer" wrote:

There can be several forms that access the same table, so you would have to
duplicate the code behind each form that accesses a table, or you can get
garbage into the database.


Right. And then you need to change a constraint, and you modify the
code and you remember to update three out of the four forms that
implement the check, and you get garbage in the database. Or
any of a number of other things that can go wrong.


Marshall
  #89  
Old January 27th, 2008, 07:30 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 27, 5:53 am, "David Cressey" wrote:
"Brian Selzer" wrote in message

Well, that's just dumb. Checks in code can reduce database round-trips,

and
therefore can improve performance, but are not and cannot be a substitute
for constraints on the tables. It is the constraints on the tables that
keeps garbage out of the database.


The idea of keeping garbage out of the database takes on an entirely
different meaning if you are dealing with hundreds of programs written in
COBOL, Java, or anything in between accessing a single Oracle database on
the one hand. On the other hand, if you are a developer creating a self
contained MS Access database cum application (tables, queries, forms,
reports, modules, etc.) all in one file, the same issues arise, but they
are resolved quite differently.

I'm not saying either one is "right" or "wrong". I'm just suggesting why an
objection that makes perfect sense to you and me might be lost on the MS
Access community.


There's another approach to enforcing constraints: just tell your
users not to do that. In the hierarchy of what works, it comes
below checks in client code, but above manually inserting
garbage at design-time. It also has the advantage of being
even lower performance overhead for the software. Note
that centrally enforced declarative integrity constraints
are an endpoint in this hierarchy.


Marshall
  #90  
Old January 27th, 2008, 08:23 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?

Your argument about the use of a DRI WITH ON UPDATE CASCADE is an
interesting argument and one that come back often;


Really? I can put my hand on my heart and say I've never seen anyone
suggest it when they cross-post to comp.databases.theory. I don't doubt
it has come up, but I do doubt it is often.


Oh, the word "often" is all relative here. Of course, in a newsgroup about
Access and/or SQL-Server, you won't see it coming very often because these
two databases don't offer support for DRI updating on cyclic relationships.
What you are more likely to see would be people complaining about the
absence of such support in SQL-Server. However, I suppose that many of
those peoples who are making this complaint have probably the use of natural
keys behind their mind (the other strong possibility beeing the use of some
form of replication); so this close the circle. I've also suppose that on
other newsgroups dedicated to systems like Oracle, the discussion about this
point is probably much more heated.

I should also add that if I remember correctly, this one was a feature that
has been promised to be released with SQL-Server 2000 but that it didn't
make the last cut and a lot of people were angry about that. Nowadays, in
regard to the next release of SQL-Server 2008, peoples seem to be more
interested with features such as the integration with .NET and other big
stuff like that than to know if Katmai will offer support for this or not.

First of all, updates to keys should be very rare. A fundamental property
of a well-chosen key is that it is reasonably stable. Imposing the little
self-discipline required to make sure you choose stable keys is
inconsequential when compared to the overall database design effort.


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.

Now, how it's important the fact that the update to a key should be very
rare? To me, there is a big difference between 0 and 1 time but there is
not between 1 and 1 million times. If you have to put code in order to take
into account the fact that the key can change its value, it should be the
same code if the key change its value one single time for the life of the
database or multiple times each day. In many cases, you are even placed in
the situation where there is a strong possibility that the key will never
change its value a single time for the whole lifetime of the database but
that you must take into account the possibility that it might do so.

Of course, there are many possible solutions: put a lot of DRI/triggers and
other pieces of code; forbid any change (and make the clients unhappy on
many occasions); suggest to delete the records and recreate them from
scratch or even rebuild the whole database; etc.; etc. but why bother with
all these in the first place?

To me, the use of natural keys is like someone bringing me a box full of
Damocles' swords: he would usually tell me to be very careful when I will
put these on the ceiling. Later, when he will come back and see the empty
ceiling and ask me why, I would answer him that I've put the box in the
garbage bin but if he want them, he can bring the box home for free; at the
condition that I'll never see them again. The fact that some theorists like
these swords too is of no interest to me. If they like them, they too can
bring them home for free but at the same condition, that I'll never them
again.

--
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 ...
Your argument about the use of a DRI WITH ON UPDATE CASCADE is an
interesting argument and one that come back often;


Really? I can put my hand on my heart and say I've never seen anyone
suggest it when they cross-post to comp.databases.theory. I don't doubt
it has come up, but I do doubt it is often.

however it's not a silver buller.


I implicitly allowed that it is not a silver bullet by actually suggesting
the kinds of reasons one might exclude it.

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.


Of course one must make sure "they are all there"; you are absolutely
right. But it absurd to suggest doing that is "another level of
complexity". It is trivial to do it, and trivial to check that you've
done it by querying the DB catalogs.

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


I'm not. But the fact that you are distinguishing the behaviour of
particular products gets close to the real problem. The real problem is
that the products we use are all more or less defective, but instead of
clamouring to have them fixed (by establishing suitable standards and
following them) we promote workarounds as if they are actually desirable.
I have no problem at all with people describing workarounds for defects
but I have a major problem when it is implied that the workaround is some
kind of best-practice or even desirable.

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


First of all, updates to keys should be very rare. A fundamental property
of a well-chosen key is that it is reasonably stable. Imposing the little
self-discipline required to make sure you choose stable keys is
inconsequential when compared to the overall database design effort.

Secondly, even having to update hundreds of tables to amend a key is only
about the same effort required to insert all those rows in the first
place. Against the background of work the system does all the time, that
will be inconsequential. (Of course, if you unwisely choose a key that is
not stable, your argument would be more nearly correct. But that is why
the long-standing advice has been to avoid keys that are not stable.)

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.


I don't entirely agree with all these reasons, but as I said in my earlier
post, there often *are* good reasons why one might not be able to use ON
UPDATE CASCADE in a particular product and I will take your word for it
that these reasons apply with the product you use. My challenge to you
was to signal you know that, and you have now done so.

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


Hm.

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.


I am confused about your argument here. I was giving counter-example to
disprove the claim that composite keys make the SQL code more complex,
which was being presented as an argument to introduce yet more, spurious,
synthetic/surrogate keys. In fact if you read my example carefully, you
will have seen that I talked explicitly about *three* synthetic keys
because I aware I was already using two (order number and item number).

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


Well, I have to admit that I'm not over-fond of relying on names to imply
that two columns represent the same thing, so in fact I never use that
syntax. I do prefer to assert all the conditions on all the key columns
explicitly and I just don't notice the few extra keystrokes when it's a
composite key. But on the other hand, I find it monstrous when I see two
or more distinct names for colunms that do represent the same thing.

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 yet you have greatly increased your credibility with this post. I
still disagree with what you've said, but I can see you know more about
what you're talking about than it seemed before. Before, I thought you
were ignorant and uncurious. Now I see you are merely wrong. :-)

and that I have absolutely no interest at all about what you are thinking
of me.


That's very healthy. You shouldn't.

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.


I make no such confusion, and a quick glance at my earlier post will
confirm it.

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 10:56 PM.


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