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
  #121  
Old January 28th, 2008, 11:01 AM posted to comp.databases.ms-access, comp.databases.theory,microsoft.public.access, microsoft.public.access.tablesdbdesign,microsoft.public.sqlserver
Jamie Collins
external usenet poster
 
Posts: 1,705
Default Separate PK in Jxn Tbl?

On Jan 27, 6:30 pm, Marshall wrote:
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.


Funny but I've actually seen this in the Access groups as an answer to
the alluded to question, "How do you prevent data corruption to your
'Access' data by users connecting via Excel?" (a common enough
scenario) i.e. put it in the staff handbook and make doing so a
disciplinary matter. Yes, I know: door...horse...bolted.

Jamie.

--

  #122  
Old January 28th, 2008, 11:16 AM posted to comp.databases.ms-access, comp.databases.theory,microsoft.public.access, microsoft.public.access.tablesdbdesign,microsoft.public.sqlserver
Jamie Collins
external usenet poster
 
Posts: 1,705
Default Separate PK in Jxn Tbl?

On Jan 28, 3:38 am, "Tony Toews [MVP]" wrote:
I'd very much like to see a business rules layer implemented that worked well within
Access. However I rather much doubt we'll ever see it given that Microsoft is
focusing their attentions on the office information worker rather than the developer.


I saw your earlier post on this and wondered if you were being
genuine. Now that I see you are...

Seriously, have you tried doing this kind of thing in Visual Studio
2005 i.e. using the .NET framework? I was helping a work colleague
(non-dev) recently who was using the free version of VS to do a simple
club subscriptions app, with data bound controls, navigation bar, etc
i.e. at a glance very reminiscent of Access Forms (except the look and
feel was much nicer g). I've also made initial investigations into
third party frameworks (i.e. extending .NET framework) to bind
business rules' logic to controls. If you believe that MS are not
evolving Access in the developer's interests (and I think you may well
be correct) and at the same time are making .NET Windows Forms
development more accessible (pun intended), maybe it's time to re-
evaluate your personal direction...

Jamie.

--

  #123  
Old January 28th, 2008, 11:37 AM posted to comp.databases.ms-access, comp.databases.theory,microsoft.public.access, microsoft.public.access.tablesdbdesign,microsoft.public.sqlserver
Jamie Collins
external usenet poster
 
Posts: 1,705
Default Separate PK in Jxn Tbl?

On Jan 27, 9:59 pm, "Sylvain Lafontaine" sylvain aei ca (fill the
blanks, no spam please) wrote:
« But many of them seem to write as if contents as determined by address ».

The content is not determined by the address and in fact, for those who are
using surrogate keys, the exact value of an address inside the database has
zero importance.


For you maybe.

In a recent thread on this subject, Tony Toews Access MVP qualified
that he liked using incremental autonumbers (rather than random)
because they where easier to type (WHERE ID = -2001736589 may
encourage typos) and easier to drop into conversation ("Hello Tony?
I'm seeing a problem with the record where the ID is -2001736589...").
Did I mention that I sincerely appreciate Tony's honesty?

Also consider the amount of posts we see in the Access groups asking
to reseeding autonumbers, gaps in sequences, etc. Wrong mental model,
perhaps, but the mentality certainly exists.

Jamie.

--

  #124  
Old January 28th, 2008, 02:56 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?


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

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.


This is a specific example of a syndrome that I described more generally:
the mismanagement of natural keys.

If a key is "natural", it is managed, if at all, beyond the scope of the
DBMS. It might be managed at the application layer, or it might be managed
by people, or it might indeed be unmanaged data, like sunspots.

When it is managed by other people, it is subject to mismanagement.
Changing values that ought to be immutable is one of many ways that natural
keys can be mismanaged.

As I said before, I prefer to use natural keys where ever possible. If
that's not possible due to mismanagement of the natural keys, I'll use
synthetic keys.



  #125  
Old January 28th, 2008, 03:01 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?


"Rob" wrote in message
...
On Jan 22, 1:26 pm, "Neil" wrote:

(quote)
I don't claim to know more about db theory than the cdt regulars, or
to have more experience than the many practioners who have contributed
to this thread.

I do know that the debate over relationship representations has been
going on for years, neither side giving any ground. Some aspects of it
are never addressed. For example, making the two foreign keys in a
junction table a composite PRIMARY key prohibits NULL values from
either key, but declaring a separate surrogate primary key together
with a UNIQUE constraint on the two-foreign-key-composite-key does
allow NULL values for either (or both!). This can be useful for
representing unrelated entity tuples, either childless parents or
orphans.

(unquote)

In the case of a junction table, this point is moot.

If both FK values are NULL, the entire row of the junction table can be
omitted with no loss of information.

Childless parents will exist in the table that contains parents, where the
id of the parent is not an FK, but a PK. Same pattern for orphans. The two
tables can even be the same table.



  #127  
Old January 28th, 2008, 03:07 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?


"-CELKO-" wrote in message
...
Does a bottle of Gin have a serial no? I am too poor to buy 2 at a time

to see if there is a different number Ah!

In the US, you get a UPC/EAN code and then a batch number with booze.
The closest thing to a serial number is your cash register receipt.


This is the "cat food problem" in a nutshell. (Or, in this discussion, the
"gin bottle" problem.)
The problem has been known as the "cat food" problem for years and years.

Items on a cash register tape represent entities with no identity at the
individual level, only at the batch or product level. You therefore can't
tell whether two bottles of gin were checked out, or whether one bottle was
scanned twice.



  #128  
Old January 28th, 2008, 03:30 PM 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?

"David Cressey" wrote in message
news:%9lnj.5893$cm6.4751@trndny05...

When it is managed by other people, it is subject to mismanagement.
Changing values that ought to be immutable is one of many ways that
natural
keys can be mismanaged.

As I said before, I prefer to use natural keys where ever possible. If
that's not possible due to mismanagement of the natural keys, I'll use
synthetic keys.


I think you may be asking too much of a natural key. I always took "stable"
to mean *almost* never changes ratherer than meaning absolutely immutable
(which I take to mean "intrinsically incapable of changing"). But whatever
one thinks it should mean, one always has to accommodate the possibility
that a key value will be mis-typed during manual entry by a user, and
therefore that it will have to be corrected. Mismanagement is, as you
rightly say, a fact of life.

But that's not usually a sufficiently good reason to introduce a synthetic
key. I have found it possible to tolerate quite a lot of updates to
supposedly stable keys using ON UPDATE CASCADE, with imperceptible
response-time costs. (One can of course measure the costs, but that's not
what counts.) A key would have to be really unstable to justify using a
synthetic key IMO.

Roy


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

David Cressey wrote:
"David W. Fenton" wrote in message
.89...

wrote in

m:

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?


What if there's more than one application built on top of the
database?


I believe this is a moot point when it comes to MS Access. The app and the
database are all stored together in Access. There is, by definition, only
one Access.

Access regulars, feel free to correct this if it's wrong.


While awkward, slow and kludgy, it is possible to attach an Access app
to an external database.
 




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:23 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.