Re: Separate PK in Jxn Tbl?

From: Brian Selzer <brian_at_selzer-software.com>
Date: Fri, 01 Feb 2008 00:59:01 GMT
Message-ID: <p9uoj.5279$0o7.473_at_newssvr13.news.prodigy.net>


[Quoted] "James A. Fortune" <MPAPoster_at_FortuneJames.com> wrote in message news:%23vttc7EZIHA.220_at_TK2MSFTNGP04.phx.gbl...
> 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.
>

[Quoted] 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
> MPAPoster_at_FortuneJames.com
Received on Fri Feb 01 2008 - 01:59:01 CET

Original text of this message