Home -> Usenet -> comp.databases.theory -> Re: Dreaming About Redesigning SQL

Re: Dreaming About Redesigning SQL

From: Steve Lancour <stevel_at_lancour.com>
Date: Wed, 29 Oct 2003 10:16:25 -0500
Message-ID: <M_GdnSiEV8OzRAKiRVn-ug_at_comcast.com> 






Bob Badour wrote:


> Steve Lancour <stevel_at_lancour.com> wrote in message news:<KcWdnaY9u-hd1wCiRVn-vw_at_comcast.com>...
> 




>>andrewst wrote:

>>

>>>Originally posted by Steve Lancour 


>>>

>>>

>>>

>>>>In Pick I'd do it with two files (tables):

>>>

>>>

>>>

>>>>Persons Table:

>>>

>>>

>>>

>>>>Marshall

>>>

>>>

>>>>Mike

>>>

>>>

>>>

>>>>Phones Table:

>>>

>>>

>>>

>>>>123-4567

>>>

>>>

>>>>234-5678

>>>

>>>

>>>>222-2222

>>>

>>>

>>>

>>>>So far, we've used the same logical structure.  Where in the

>>>>relational

>>>

>>>

>>>>model you'd use a third table to represent the intersections of

>>>>Persons

>>>

>>>

>>>>and Phones I would define a multi-valued attribute (column) in Persons

>>>

>>>

>>>>for Phones and in Phones for Persons:

>>>

>>>

>>>

>>>>Phones attribute (column) in Persons records:

>>>

>>>

>>>>Marshall  123-4567]234-5678

>>>

>>>

>>>>Mike      234-5678]222-2222

>>>

>>>

>>>

>>>>Persons attribute (column) in Phones records:

>>>

>>>

>>>>123-4567  Marshall

>>>

>>>

>>>>234-5678  Marshall]Mike

>>>

>>>

>>>>222-2222  Mike

>>>

>>>

>>>

>>>>Referential integrity is the responsibility of the developer.  In a

>>>

>>>

>>>>poorly-designed system there may be any number of programs updating

>>>

>>>

>>>>these records.  Each such program must enforce the rules and any rule

>>>

>>>

>>>>changes must be made separately in each.  The opportunity for mistakes

>>>

>>>

>>>>leading to corruption is undeniable.

>>>

>>>

>>>

>>>>In a well-designed system, only one Pick-Basic subroutine handles

>>>

>>>

>>>>updates and deletions to the Person/Phone relations for *all* input

>>>

>>>

>>>>sources (other Pick programs, web interface, batch load, etc.).  This

>>>

>>>

>>>>subroutine may be implemented as a trigger or as part of a business

>>>

>>>

>>>>rules layer "talking to" a variety of "front-end" applications.  If

>>>

>>>

>>>>desired, the two record updates may be enclosed in a transaction to

>>>

>>>

>>>>guarantee integrity.

>>>

>>>

>>>

>>>

>>>>In this example, the query to get Marshall's phone numbers would be:

>>>

>>>

>>>

>>>>select Persons "Marshall" Phones

>>>

>>>

>>>

>>>

>>>

>>>>To query for Persons using 234-5678:

>>>

>>>

>>>

>>>>select Phones "234-5678" Persons

>>>

>>>

>>>

>>>

>>>

>>>>In both cases, Pick's record-key hashing allows a "direct hit" on the

>>>

>>>

>>>>records requested without the need for an index or a table scan.

>>>

>>>

>>>

>>>>Steve Lancour 

>>>

>>>

>>>Right, well I'm not in the business of Pick-bashing, really.  But we

>>>could achieve the same "direct hit" performance in Oracle (the SQL

>>>DBMS I happen to be familiar with) using 2 hash clusters if we wanted

>>>to - provided we store the person/phone intersection data twice as you

>>>would in Pick.

>>>

>>>

>>>

>>>We wouldn't actually do that, of course, because 99.999% of the time

>>>ensuring data integrity is more important than shaving a CPU millisecond

>>>off the access time.  And as you admit, storing the same data twice

>>>separately runs an increased risk of corruption.

>>>

>>>

>>>

>>>So yes, we will add indexes (unless we are sure our tables will always

>>>be very small), and a typical keyed query may involve a few logical

>>>reads rather than 1.  If the query is performed a lot, the index

>>>entries are likely to be cached in the server memory, so that the

>>>number of physical reads may be fewer (zero if we are really lucky);

>>>if it isn't performed a lot, we probably won't notice the additional

>>>physical reads anyway.

>>>

>>>

>>>--

>>>Posted via http://dbforums.com

>>

>>

>>In my example, if I bracket the two updates inside a transaction I 

>>eliminate the possibility of corruption during the update.

> 
> 
> How do you eliminate the possibility of corruption due to flaws in
> your programming logic or another programmer's logic?





I can't *eliminate* the possibility of corruption in Pick any more than 
I could on any other system if someone with full DBA privileges makes a 
mistake.



As I said in an earlier post, I'd design the system so the updates to 
the Persons/Phones relationships are handled by only one Pick-Basic 
subroutine as part of a rules layer.



Whether I have programmers working in Pick-Basic, Java, VB, Delphi or 
any number of other enviornments, they would all update the Pick data by 
calling the same subroutines.  Only a subset of the development staff 
would have the ability to modify these rules programs with rigorous QA 
to keep them in line.  Also, since I've narrowed the focus of the update 
subroutines to one rule each they are relatively simple.



Consider this example:



subroutine UpdatePersonPhone (PersonKey, PhoneKey, ErrorMessage)
include CommonBlock ;* commonly-used variables and file references
ErrorMessage=""


begin work


   readu PersonRec from F.Persons,PersonKey else ;* sets a record lock
     ErrorMessage<-1>="Cannot read Person record ":PersonKey
   end


   readu PhoneRec from F.Phones,PhoneKey else ;* sets a record lock
     ErrorMessage<-1>="Cannot read Phone record ":PhoneKey
   end


   if ErrorMessage#"" then

     release F.Persons,PersonKey ;* release record lock
     release F.Phones,PhoneKey ;* release record lock
   end else
     locate(PhoneKey,PersonRec,2;PhonePos;'AR') else
       PersonRec=insert(PersonRec,2,PhonePos;PhoneKey)
     end
     locate(PersonKey,PhoneRec,2;PersonPos;'AL') else
       PhoneRec=insert(PhoneRec,2,PersonPos;PersonKey)
     end
     write PersonRec on F.Persons,PersonKey ;* write releases lock
     write PhoneRec on F.Phones,PhoneKey ;* write releases lock


   end


commit work


return ;* to calling program
Received on Wed Oct 29 2003 - 16:16:25 CET












Original text of this message