[AccessD] 2 quick questions

<rant>

You raise an excellent question, JC. But I am allegedly one of the few
listers who has read virtually all the books (Codd, Date, Pascal, etc.) and
although I understand their arguments about intelligent PKs, I still tend
(and tend to practise) Autonumbers (that's Access) or Identities (that's SQL
Server) or Sequences (that's Oracle). Despite Codd, Date and Pascal, I drift
to the Identity Key solution.

Another irritating point. PKs should never change: what you need instead is
PITA archicture, and that acronym doesn't mean what you might guess first.
It means Point In Time Architecture, and what that subject concerns (for
more details, see my article on Simple-Talk) is the IMO central flaw of
relational theory: any update or delete by definition destroy history. That
might be fine for numerous countries I could name, but it doesn't do for the
real world. For example, most women (and relatively few men) choose to
change their names upon entry into a marriage contract. The person, the
Social Insurance Number -- oops, that's CDN, I think in USA it's SSN)
remains the same.

The point of PITA is to never update a row, but instead replicate it, with a
pointer to the original row. Take the case of, and not intending even the
slightest sleight, Elizabeth Taylor, who had in the course of her life
approximately eight husbands. I have far fewer wives, but this is neither
about her nor me; it's about changing life-states.

Let us assume that Ms. Taylor's unique ID is her SSN, and that mine is my
SIN, and that Gustav's unique ID is (oops, I confess my ignorance about
Danish IDs, but you get the point).

Given these facts, one could a) argue for an Autonumber; or b) argue for an
Intelligent and also unique key called SSN or SIN or (Gustav, please jump in
and supply the Danish equivalent, and also Martin Breen and all you other
beloved listers).

The point I trying to make here is that in Canada I have a unique
identifier, called SIN. I've had it since I was about 16 and I'm now 63.6
and it has remained the same throughout that time period. During these
years, I have been in several relationships, some common-law and two actual
marriages. Currently I live alone, and have neither interest in or likely
prospect of Yet Another.

Here's where we arrive at the nitty-gritty. From 1995 until 2001 I was
married to a lovely person with whose name I shall not bother you. Prior to
that, about ten years earlier, I was married to another person. Between
these events, I shared accommodations with someone I did not marry.
Subsequent to these events, I have concluded that solitude is the best
approach for my end-of-life strategy.

Throughout these stages, my SIN has remained the same. My address and my
wife/partner has changed. And this is the fundamental problem with the
Codd/Date/Pascal approach to relational theory. PITA is the only solution
that I can conceive. Of course, that could change tomorrow, should I happen
upon a better idea.

This is the bottom line: no row shall ever be update, but rather copied to
the same table and given both a new PK and also a value in the column
ParentPK which identifies the row from which it was copied. This enables you
to change surnames, addresses, phone numbers, etc, while also retaining all
the data which applies to you in 1991. That's how you do it. It's called
PITA, and it requires the addiiton of a couple of columns in every table in
your DB (call them DateEntered and ParentPK for example). Given this
scenario, it becomes easy to deliver a result comprising the, say,
pharmaceutical purchased of anyone or everyone from July to August 1999.
It's a no-brainer because no information has been destroyed.

As this rather long missive might indicate, this is a sensitive point with
me. But I have worked in a few environments where this was critical. Drug
stoes, tire dispensers, and even soup companies: in these situations, you
need to know what was sold to whom and when, whether or not their surnames
and addresses have changed.

</rant>

On Thu, Jun 2, 2011 at 11:12 AM, jwcolby <jwcolby at colbyconsulting.com>wrote:

> The real question in my mind is why harass this list with endless arguments
> about this mostly theoretical stuff?
>
> Is there a *practical* reason for bringing this up over and over and
> over...
>
>
> John W. Colby
> www.ColbyConsulting.com
>
> On 6/2/2011 10:37 AM, Drew Wutka wrote:
>
>> Actually, Jim, that's not entirely true.  I must admit I haven't read
>> any of his books.  But before I posted in the thread, I did do some
>> reading up on Codd.  I had read about him and his theories before, but
>> just did a little refresher.
>>
>> He worked for IBM, and IBM didn't implement his 'theories', in fact,
>> they practically ignored them until similar concepts came out from
>> competitors. The point this 'debate' here seems to be missing is that
>> his ideas are THEORIES, not law.  The REAL reason that they are not
>> implemented is WHY should they be?  What advantage occurs with a system
>> that perfectly implements his laws?  Without a commercial reason to
>> implement them. Have at it.
>>
>> Drew
>>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>