Re: Dreaming About Redesigning SQL

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.