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
  #201  
Old February 1st, 2008, 12:53 AM posted to comp.databases.ms-access, comp.databases.theory,microsoft.public.access, microsoft.public.access.tablesdbdesign
-CELKO-
external usenet poster
 
Posts: 26
Default Separate PK in Jxn Tbl?

It is a good classic screw up, with blame for everyone!

1) ACCESS programmer builds desktop app on his own that looks good for
his immediate needs.
2) Management sees the app and wants to deploy it all over the
company. Hey, why design anything new when we have it already?
3) ACCESS programmer claims it will deploy and and management believes
him.
4) It does not scale, it does not interface with mainframe apps,
external apps, etc. It has no documentation, etc.
5) ACCESS programmer now has a career being the only guy who can keep
the sinking boat up. Never mind how many times a week it has to be re-
booted or how much data is lost.
6) Neither the programmer nor management will scream for help and ask
for a budget. Management would look stupid; programmer would lose his
job and power





  #202  
Old February 1st, 2008, 01:59 AM 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?


"James A. Fortune" wrote in message
...
David Cressey wrote:

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 don't have broad enough experience to dispute your argument. I
understand that people who specialize in SQL and deal with more complex
situations than most develop practices that make use of their more
intimate knowledge of SQL. However, I can't just take their word about
their decisions. I have to understand how those choices apply to what I'm
doing. Without making light of their potential contribution, I avoid the
specious argument that because a large company or IT department does
things a certain way or spends more money on the problem makes their
solution inherently correct. Plus, the complexity of the problems they
face often argue against their use in Access. Few Access developers have
the luxury to hire or supervise a full-time SQL developer. If using
multiple field natural keys causes a problem(s), a full-time SQL developer
has time to work with the SQL until the problem is solved. SQL is only
part of our job.

It has been nice to see posters in microsoft.public.access such as Jamie
Collins and Ken Sheridan, who seem to have a lot of standard SQL
experience, branch off into other issues that Access programmers face. I
think their understanding of those issues can help us differentiate
between purely SQL issues and Microsoft implementation issues. We all
agree that Microsoft has made questionable design decisions in Access, but
the possibility of inclusion of unbound forms or of AutoNumber primary
keys might not be part of that list, as some have suggested. I have
enough experience to say that using unbound forms in Access and using
artificial keys did not cause any problems when scaling an Access
application up to an ASP solution using SQL Server. For something more
complicated perhaps there are subtle issues that arise that merit our
attention.

Table level constraints also raise an issue. At the table level, there is
no VBA code run to ensure that any constraints (e.g., on the natural keys
themselves) are enforced. Thus, a set of natural keys is sufficient to
specify the key constraint. Any artificial key becomes superfluous. The
possibility of separate applications using the same table, brought up by
David Fenton, brings up the interesting possibility that the applications
have separate, possibly disparate constraints. In disparate constraint
situations not all of the constraints can be at the table level, although
they can be specified at the query level in each application rather than
via code.


Constraints should always be checked by the DBMS, not by applications. If
you have two separate applications that manipulate the same table, and one
enforces one constraint while another enforces another, then all you need to
do to bypass one constraint is to use the other application! What, then, is
the point of even having the constraint?

Maybe many Access programmers prefer a single key to limit the number of
fields that get corrupted :-). Perhaps an enumeration of sticky
situations encountered by those using multiple field natural keys would
help us understand why some avoid them. Maybe better solutions to those
sticky problems would encourage developers to stay with natural keys more
often.


In my opinion, the only sound reason for using artificial keys is when the
values of a natural key do not permanently identify individuals in the
Universe of Discourse--that is, the micro-world that the database is
supposed to be a model of--and when there is a demonstrable requirement for
permanent identification. Any other use adds complexity for complexities
sake--in other words, for no particularly good reason.

James A. Fortune



  #203  
Old February 1st, 2008, 03:52 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?

In my opinion, the only sound reason for using artificial keys is when the
values of a natural key do not permanently identify individuals in the
Universe of Discourse--that is, the micro-world that the database is
supposed to be a model of--and when there is a demonstrable requirement
for permanent identification. Any other use adds complexity for
complexities sake--in other words, for no particularly good reason.


Even in the very few cases were you think that a natural key will be a true
natural key, ie. it will be permanent and totally managed at 100% (which
mean no data entry error of any kind, no change of value because of identity
theft, etc., etc.); the privacy issues will forbid their use in many cases.
Many countries now forbid by law to ask for something such as your social
security number when you don't need it and when you need it, to use it as a
key instead of storing it confidentially (ie., by encrypting it).

Even MS had problem with this in the past: the first version of the
algorithm for generating a GUIG was using the NIC number as part of its
algorithm. They had to remove it later and now, the generation of any GUIG
is totally random. The older algorithm is still available in Windows under
another name but MS warns that anyone using it might be doing so illegally
under the laws of their countries.

The same argument apply to things like RFID: how many of you would like to
see big corporation to know which medicaments you are bringing home or your
spouse to know that you have just bought a box of condom?

Using natural keys is like building a castle of cards: they might look
impressive but it doesn't take to much to bring them down.

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


"Brian Selzer" wrote in message
news

"James A. Fortune" wrote in message
...
David Cressey wrote:

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 don't have broad enough experience to dispute your argument. I
understand that people who specialize in SQL and deal with more complex
situations than most develop practices that make use of their more
intimate knowledge of SQL. However, I can't just take their word about
their decisions. I have to understand how those choices apply to what
I'm doing. Without making light of their potential contribution, I avoid
the specious argument that because a large company or IT department does
things a certain way or spends more money on the problem makes their
solution inherently correct. Plus, the complexity of the problems they
face often argue against their use in Access. Few Access developers have
the luxury to hire or supervise a full-time SQL developer. If using
multiple field natural keys causes a problem(s), a full-time SQL
developer has time to work with the SQL until the problem is solved. SQL
is only part of our job.

It has been nice to see posters in microsoft.public.access such as Jamie
Collins and Ken Sheridan, who seem to have a lot of standard SQL
experience, branch off into other issues that Access programmers face. I
think their understanding of those issues can help us differentiate
between purely SQL issues and Microsoft implementation issues. We all
agree that Microsoft has made questionable design decisions in Access,
but the possibility of inclusion of unbound forms or of AutoNumber
primary keys might not be part of that list, as some have suggested. I
have enough experience to say that using unbound forms in Access and
using artificial keys did not cause any problems when scaling an Access
application up to an ASP solution using SQL Server. For something more
complicated perhaps there are subtle issues that arise that merit our
attention.

Table level constraints also raise an issue. At the table level, there
is no VBA code run to ensure that any constraints (e.g., on the natural
keys themselves) are enforced. Thus, a set of natural keys is sufficient
to specify the key constraint. Any artificial key becomes superfluous.
The possibility of separate applications using the same table, brought up
by David Fenton, brings up the interesting possibility that the
applications have separate, possibly disparate constraints. In disparate
constraint situations not all of the constraints can be at the table
level, although they can be specified at the query level in each
application rather than via code.


Constraints should always be checked by the DBMS, not by applications. If
you have two separate applications that manipulate the same table, and one
enforces one constraint while another enforces another, then all you need
to do to bypass one constraint is to use the other application! What,
then, is the point of even having the constraint?

Maybe many Access programmers prefer a single key to limit the number of
fields that get corrupted :-). Perhaps an enumeration of sticky
situations encountered by those using multiple field natural keys would
help us understand why some avoid them. Maybe better solutions to those
sticky problems would encourage developers to stay with natural keys more
often.


In my opinion, the only sound reason for using artificial keys is when the
values of a natural key do not permanently identify individuals in the
Universe of Discourse--that is, the micro-world that the database is
supposed to be a model of--and when there is a demonstrable requirement
for permanent identification. Any other use adds complexity for
complexities sake--in other words, for no particularly good reason.

James A. Fortune





  #204  
Old February 1st, 2008, 06:20 AM posted to comp.databases.ms-access,comp.databases.theory,microsoft.public.access,microsoft.public.access.tablesdbdesign
Tony Toews [MVP]
external usenet poster
 
Posts: 3,776
Default Separate PK in Jxn Tbl?

-CELKO- wrote:

It is a good classic screw up, with blame for everyone!

1) ACCESS programmer builds desktop app on his own that looks good for
his immediate needs.
2) Management sees the app and wants to deploy it all over the
company. Hey, why design anything new when we have it already?
3) ACCESS programmer claims it will deploy and and management believes
him.
4) It does not scale, it does not interface with mainframe apps,
external apps, etc. It has no documentation, etc.
5) ACCESS programmer now has a career being the only guy who can keep
the sinking boat up. Never mind how many times a week it has to be re-
booted or how much data is lost.
6) Neither the programmer nor management will scream for help and ask
for a budget. Management would look stupid; programmer would lose his
job and power


That sounds quite typical for some Access applications. But consider just where was
IT in all this? D*cking around on their Oracle database that will save the company.
Or this app didn't fit in the IT priorities. We'll create this fabulous system.
Uhh, when? Sometime this century.

It's better than Excel. Although not by much in such incapable hands as you
describe. And that I've seen myself in the past.

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/
  #205  
Old February 1st, 2008, 08:33 AM 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

Constraints should always be checked by the DBMS, not by applications.


I agree very heartily with the first part of this statement, for the reasons
you give below. I disagree with the second part (as stated). There is no
reason why applications shouldn't also test what constraints they can. The
problem is that they should not have hand-coded re-implementations of the
constraints because those will get out of sync with the database over time.
What would be very nice is if one day it were possible for applications to
download the relevant constraints at run-time, the way they presently
download other metda-data.

If you have two separate applications that manipulate the same table, and
one enforces one constraint while another enforces another, then all you
need to do to bypass one constraint is to use the other application!
What, then, is the point of even having the constraint?


Roy


  #206  
Old February 1st, 2008, 09:08 AM 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
...
Sylvain Lafontaine wrote:
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?


I didn't say overkill doesn't work, did I :-)? I think that until we
delineate the true trade-offs between natural keys and artificial keys,
if any, you should design your schemas/schemata as you deem best. If it
turns out that there are situations where each has advantages then those
situations should determine the correctness of the schema.

I'll go further than that. For most design problems there is more than one
acceptable solution. This is particularly true of schema design. Design
trade-offs will help determine which of two possible designs is better in
any given situation.



  #207  
Old February 1st, 2008, 09:15 AM 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
...
David Cressey wrote:

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 don't have broad enough experience to dispute your argument. I
understand that people who specialize in SQL and deal with more complex
situations than most develop practices that make use of their more
intimate knowledge of SQL. However, I can't just take their word about
their decisions. I have to understand how those choices apply to what
I'm doing.


I agree, absolutely. What I was arguing against was the dismissal without
evaluation of what theorists have to offer.



Without making light of their potential contribution, I
avoid the specious argument that because a large company or IT
department does things a certain way or spends more money on the problem
makes their solution inherently correct. Plus, the complexity of the
problems they face often argue against their use in Access. Few Access
developers have the luxury to hire or supervise a full-time SQL
developer. If using multiple field natural keys causes a problem(s), a
full-time SQL developer has time to work with the SQL until the problem
is solved. SQL is only part of our job.


SQL isn't the silver bullet, either.

Maybe many Access programmers prefer a single key to limit the number of
fields that get corrupted :-).


In that case, I believe they are wrong.


  #208  
Old February 1st, 2008, 09:30 AM 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?


"Roy Hann" wrote in message
...
"Brian Selzer" wrote in message
news

Constraints should always be checked by the DBMS, not by applications.


I agree very heartily with the first part of this statement, for the
reasons you give below. I disagree with the second part (as stated).
There is no reason why applications shouldn't also test what constraints
they can. The problem is that they should not have hand-coded
re-implementations of the constraints because those will get out of sync
with the database over time. What would be very nice is if one day it were
possible for applications to download the relevant constraints at
run-time, the way they presently download other metda-data.


That's a good point. I should have said instead, "Constraints should always
be /enforced/ by the DBMS, not by applications." It is often a good thing
for an application to do some checking because it can reduce the number of
round-trips, and maybe even some transaction rollbacks.

If you have two separate applications that manipulate the same table, and
one enforces one constraint while another enforces another, then all you
need to do to bypass one constraint is to use the other application!
What, then, is the point of even having the constraint?


Roy



  #209  
Old February 1st, 2008, 09:43 AM 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?


"Sylvain Lafontaine" sylvain aei ca (fill the blanks, no spam please)
wrote in message ...
In my opinion, the only sound reason for using artificial keys is when
the values of a natural key do not permanently identify individuals in
the Universe of Discourse--that is, the micro-world that the database is
supposed to be a model of--and when there is a demonstrable requirement
for permanent identification. Any other use adds complexity for
complexities sake--in other words, for no particularly good reason.


Even in the very few cases were you think that a natural key will be a
true natural key, ie. it will be permanent and totally managed at 100%
(which mean no data entry error of any kind, no change of value because of
identity theft, etc., etc.); the privacy issues will forbid their use in
many cases. Many countries now forbid by law to ask for something such as
your social security number when you don't need it and when you need it,
to use it as a key instead of storing it confidentially (ie., by
encrypting it).


So, where does it say that there can only be one natural key on a table?

Even MS had problem with this in the past: the first version of the
algorithm for generating a GUIG was using the NIC number as part of its
algorithm. They had to remove it later and now, the generation of any
GUIG is totally random. The older algorithm is still available in Windows
under another name but MS warns that anyone using it might be doing so
illegally under the laws of their countries.

The same argument apply to things like RFID: how many of you would like to
see big corporation to know which medicaments you are bringing home or
your spouse to know that you have just bought a box of condom?


Why should it be a problem for my spouse to know that I just bought a box of
condoms? More often than not she is the one who lets me know that we need
them. Oh! Wait! I get it! That must be something those of you who can't
keep it in your pants worry about.

Using natural keys is like building a castle of cards: they might look
impressive but it doesn't take to much to bring them down.


Even with artificial keys, you still need natural ones. Without them, you
can have multiple values for a key that mean exactly the same thing, but
with no way to determine which, thereby rendering the key useless.

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


"Brian Selzer" wrote in message
news

"James A. Fortune" wrote in message
...
David Cressey wrote:

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 don't have broad enough experience to dispute your argument. I
understand that people who specialize in SQL and deal with more complex
situations than most develop practices that make use of their more
intimate knowledge of SQL. However, I can't just take their word about
their decisions. I have to understand how those choices apply to what
I'm doing. Without making light of their potential contribution, I
avoid the specious argument that because a large company or IT
department does things a certain way or spends more money on the problem
makes their solution inherently correct. Plus, the complexity of the
problems they face often argue against their use in Access. Few Access
developers have the luxury to hire or supervise a full-time SQL
developer. If using multiple field natural keys causes a problem(s), a
full-time SQL developer has time to work with the SQL until the problem
is solved. SQL is only part of our job.

It has been nice to see posters in microsoft.public.access such as Jamie
Collins and Ken Sheridan, who seem to have a lot of standard SQL
experience, branch off into other issues that Access programmers face. I
think their understanding of those issues can help us differentiate
between purely SQL issues and Microsoft implementation issues. We all
agree that Microsoft has made questionable design decisions in Access,
but the possibility of inclusion of unbound forms or of AutoNumber
primary keys might not be part of that list, as some have suggested. I
have enough experience to say that using unbound forms in Access and
using artificial keys did not cause any problems when scaling an Access
application up to an ASP solution using SQL Server. For something more
complicated perhaps there are subtle issues that arise that merit our
attention.

Table level constraints also raise an issue. At the table level, there
is no VBA code run to ensure that any constraints (e.g., on the natural
keys themselves) are enforced. Thus, a set of natural keys is
sufficient to specify the key constraint. Any artificial key becomes
superfluous. The possibility of separate applications using the same
table, brought up by David Fenton, brings up the interesting possibility
that the applications have separate, possibly disparate constraints. In
disparate constraint situations not all of the constraints can be at the
table level, although they can be specified at the query level in each
application rather than via code.


Constraints should always be checked by the DBMS, not by applications.
If you have two separate applications that manipulate the same table, and
one enforces one constraint while another enforces another, then all you
need to do to bypass one constraint is to use the other application!
What, then, is the point of even having the constraint?

Maybe many Access programmers prefer a single key to limit the number of
fields that get corrupted :-). Perhaps an enumeration of sticky
situations encountered by those using multiple field natural keys would
help us understand why some avoid them. Maybe better solutions to those
sticky problems would encourage developers to stay with natural keys
more often.


In my opinion, the only sound reason for using artificial keys is when
the values of a natural key do not permanently identify individuals in
the Universe of Discourse--that is, the micro-world that the database is
supposed to be a model of--and when there is a demonstrable requirement
for permanent identification. Any other use adds complexity for
complexities sake--in other words, for no particularly good reason.

James A. Fortune







  #210  
Old February 1st, 2008, 09:44 AM posted to comp.databases.ms-access,comp.databases.theory,microsoft.public.access,microsoft.public.access.tablesdbdesign,microsoft.public.sqlserver
Jon Heggland
external usenet poster
 
Posts: 6
Default Separate PK in Jxn Tbl?

Quoth Roy Hann:
What would be very nice is if one day it were possible for applications to
download the relevant constraints at run-time, the way they presently
download other metda-data.


Dataphor does this.
--
Jon
 




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 05:21 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.