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
  #211  
Old February 1st, 2008, 10: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?

"Jon Heggland" wrote in message
...
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.


You don't think I was clever enough to come up with the idea all by myself
do you? :-)

I admire what Dataphor set out to do. But having been forced to adopt SQL
against my will a long time ago, I am under no illusion that anything better
than SQL will ever catch on. The best hope is that embedded SQL might be
less stupid in future.

Roy


  #212  
Old February 1st, 2008, 10:57 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:
"Jon Heggland" wrote in message
...
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.


You don't think I was clever enough to come up with the idea all by myself
do you? :-)


Why not? It's a pretty simple idea, really.

I admire what Dataphor set out to do. But having been forced to adopt SQL
against my will a long time ago, I am under no illusion that anything better
than SQL will ever catch on.


Well, Dataphor databases are typically built on top of SQL databases.
Just tell management that Dataphor is really just a presentation layer
on top of SQL.

The best hope is that embedded SQL might be less stupid in future.


If that was my best hope, i think I would quit being a database engineer.
--
Jon
  #213  
Old February 1st, 2008, 11:02 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?

"Jon Heggland" wrote in message
...
Quoth Roy Hann:
The best hope is that embedded SQL might be less stupid in future.


If that was my best hope, i think I would quit being a database engineer.


I admire your optimism. Can we agree to meet again in this very place 20
years from now to discuss how it went? :-)

Roy


  #214  
Old February 1st, 2008, 03:13 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
news

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


I think that if an application does some checking, it will also do some
enforcing.

I think you might have been aiming at something like the following:

The DBMS should always enforce the constraints that it can enforce, rather
than relying on applications to refrain from writing data that violates the
constraints.

Note that the above is silent on what applications should or should not do
regarding constraints.


  #215  
Old February 1st, 2008, 03:55 PM posted to comp.databases.ms-access,comp.databases.theory,microsoft.public.access,microsoft.public.access.tablesdbdesign
salad
external usenet poster
 
Posts: 47
Default Separate PK in Jxn Tbl?

-CELKO- wrote:

Thank you. You made my point. I was only wrong on the non-English
speaking programmers. It doesn't appear to be a famous failure, more
like a internal problem exacerbated by management.

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.


Obviously a dedicated person that takes some initiative.

2) Management sees the app and wants to deploy it all over the
company. Hey, why design anything new when we have it already?


Dilbert's pointy headed boss comes to the rescue.

3) ACCESS programmer claims it will deploy and and management believes
him.


The programmer is correct.

4) It does not scale, it does not interface with mainframe apps,
external apps, etc. It has no documentation, etc.


Management decides unwisely to not spend money to upgrade it or redefine
it to meet goals. Programmer has a life and a job and doesn't have time
to write documentation. Mgt is too cheap to provide him with a
technical writer or someone to do bug testing. This is obviously not an
important project.

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.


Programmer skill level may come into play. Feature creep may come into
play. Management thought on project is nil. Something for nothing and
the chicks for free.

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

You get what you pay for.

I can't fault the programmer. He made something to make his job easier.
Mgt glommed onto it but wasn't willing to invest in it.

I asked before, are you on the committee that oversees the project? If
so, look in the mirror.
  #216  
Old February 1st, 2008, 04:16 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:wOFoj.5442$4f.4907@trndny06...

"Brian Selzer" wrote in message
news

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


I think that if an application does some checking, it will also do some
enforcing.

I think you might have been aiming at something like the following:

The DBMS should always enforce the constraints that it can enforce, rather
than relying on applications to refrain from writing data that violates
the
constraints.

Note that the above is silent on what applications should or should not do
regarding constraints.


Yes.



  #217  
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.
  #218  
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

  #219  
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
  #220  
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
 




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 12:31 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.