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 » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Separate PK in Jxn Tbl?



 
 
Thread Tools Display Modes
  #221  
Old February 1st, 2008, 04:24 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:

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


The key words on that paragraph a design and tradeoff.
  #222  
Old February 1st, 2008, 04:35 PM 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?

David Cressey wrote:


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.


Access is way more likely than SQL Server to corrupt a primary key
field(s), especially when a large number of concurrent users are editing
under the same index value, perhaps while someone is also turning off
their computer without shutting down on a form bound to the same data.
It was a facetious consideration because that kind of corruption occurs
rarely in Access.

James A. Fortune

  #223  
Old February 1st, 2008, 06:02 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 31, 11:33 pm, "Roy Hann"
wrote:
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.


Ding ding ding ding ding ding! We have a winner!


Marshall
  #224  
Old February 1st, 2008, 06:08 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 Feb 1, 12:30 am, "Brian Selzer" wrote:
"Roy Hann" wrote in message

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.


Yes. In addition, if the client code knows what the database's
constraints
are, it can provide better user experience, better error messages,
etc.


Marshall
  #225  
Old February 1st, 2008, 06: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 31, 4:59 pm, "Brian Selzer" wrote:

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?


I think that application constraints and database constraints are
really
two entirely separate things. The fact that they may be structurally
identical obscures and confuses this point. (Hence Brian's entirely
reasonable rhetorical question above.)

What, indeed, is the point of having one application and not another
enforce a constraint, *if we view this from the perspective of the
requirements of the database* Clearly there is none. However
individual applications may have requirements that are best
implemented as constraints *within the application.* I call these
"application constraints" because they are specific to the
application. They are *not* integrity constraints, even if we
are using identical mechanisms (in different locations) for both.


Marshall
  #226  
Old February 1st, 2008, 07:52 PM 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 31, 4:59 pm, "Brian Selzer" wrote:

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?



I think that application constraints and database constraints are
really
two entirely separate things. The fact that they may be structurally
identical obscures and confuses this point. (Hence Brian's entirely
reasonable rhetorical question above.)

What, indeed, is the point of having one application and not another
enforce a constraint, *if we view this from the perspective of the
requirements of the database* Clearly there is none. However
individual applications may have requirements that are best
implemented as constraints *within the application.* I call these
"application constraints" because they are specific to the
application. They are *not* integrity constraints, even if we
are using identical mechanisms (in different locations) for both.


Marshall


I think you're on to something. Making a distinction between database
constraints and application constraints helps me clarify my thinking.
Being able to "reflect" database constraints to keep applications in
synch with changes sounds like a great idea.

James A. Fortune

  #227  
Old February 8th, 2008, 03:11 PM posted to comp.databases.ms-access,comp.databases.theory,microsoft.public.access,microsoft.public.access.tablesdbdesign
Gerry Hatrick[_2_]
external usenet poster
 
Posts: 2
Default Separate PK in Jxn Tbl?

"Frank Hamersley" wrote in message
...
Keith Wilby wrote:
"Frank Hamersley" wrote in message
...

provision of a menu option to reattach a data .mdb!


Is it just me or is that complete gibberish? Reattach?


Just you mate (at least I hope so or we are doomed).

Pray tell how do you distribute new versions of "code"?

Never had that concern? - always just hacked the live .mdb?

Gawd.


No, you have a development copy of the front end, there's no "reattaching"
of code, you just make a new version of the front end available - front end:
code and all other objects except the tables. Gawd, gibberish indeed.


 




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 07:52 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.