Re: Dreaming About Redesigning SQL

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:

1. Marshall is associated with phone numbers: 123-4567, 234-5678
2. 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