Re: Separate PK in Jxn Tbl?

"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)> wrote in message news:eBuh%231HZIHA.4684_at_TK2MSFTNGP06.phx.gbl...
>> 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" <brian_at_selzer-software.com> wrote in message
> news:p9uoj.5279$0o7.473_at_newssvr13.news.prodigy.net...
>>
>> "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.
>>>
>>
>> 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 - 09:43:45 CET