[AccessD] 2 quick questions

I like the concept of PITA.  The implementation could range from works fine to massive database 
bloat.  I think for narrow tables that change occasionally it would work great.  For wide tables 
that change a lot it would be problematic.

John W. Colby
www.ColbyConsulting.com

On 6/2/2011 12:55 PM, Arthur Fuller wrote:
> <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
>>