Re: Dreaming About Redesigning SQL
Date: Mon, 27 Oct 2003 09:51:32 -0500
Message-ID: <57ydnf60E9LHrQCiRVn-jA_at_comcast.com>
andrewst wrote:
> Originally posted by Mike Preece
>
>
>>"Marshall Spight" <mspight_at_dnai.com> wrote in message >>news:<UZ3nb.30451$ao4.58659_at_attbi_s51>...
>
>
>>>I would have a persons table, with a primary key, and a
>
>
>>>phone numbers table with a primary key (possibly the phone
>
>
>>>number itself) and another table with two foreign keys:
>
>
>>>person id and phone id.
>
>
>
>>>Marshall
>
>
>
>>Oh. You mean like an index? We could just have a single file:
>
>
>
>> File Phones
>
>
>> ID: Phone Number
>
>
>> 001: Phone type ('C'ell, 'H'ome, 'F'ax, 'W'ork, 'D'irect or
>> whatever)
>
>
>> 002: PersonalIDs (multivalued)
>
>
>> ..and any other data relating primarily to the phone
>
>
>
>>and index it on PersonalID.
>
>
>
>>I think it's more or less the same - or are b-tree indexes not >>allowed?
>
>
>
>>Mike.
>
>
> No, Marshall's point has nothing whatever to do with indexes, it is all
> about data. Suppose you have the following "rules":
>
> 1) A person may be associated many phone numbers
>
> 2) A phone number may be associated with many persons
>
>
>
> For example:
>
> a) Marshall is associated with phone numbers: 123-4567, 234-5678
>
> b) Mike is associated with phone numbers: 222-2222, 234-5678
>
>
>
> i.e. you both have 2 phone numbers, one of which you share.
>
>
>
> The relational model for this would have the 3 tables Marshall
> described, with data like this:
>
>
>
> Persons table:
>
> Marshall
>
> Mike
>
>
>
> Phones table:
>
> 123-4567
>
> 234-5678
>
> 222-2222
>
>
>
> Person_phones table:
>
> Marshall, 123-4567
>
> Marshall, 234-5678
>
> Mike, 234-5678
>
> Mike, 222-2222
>
>
>
> Then to find out Marshall's phone numbers you would query
> something like:
>
>
>
> select phone_no from person_phones
>
> where person_name = 'Marshall';
>
>
>
> And to find out who is on 234-5678 you would query something like:
>
>
>
> select person_name from person_phones
>
> where phone_no = '234-5678';
>
>
>
> See? The person/phone data is not stored "with" the person, nor "with"
> the phone, and certainly not recorded twice independently. And the
> answers to both queries are guaranteed to be consistent, because they
> query the same data.
>
>
>
> There may be indexes defined on these tables to aid performance, but
> these are irrelevant to the point, and in any case are maintained by
> the DBMS so there is no possibility of the index becoming out of sync
> with the data.
>
>
> --
> Posted via http://dbforums.com
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 Received on Mon Oct 27 2003 - 15:51:32 CET