Re: Dreaming About Redesigning SQL

"mikepreece" <member31023_at_dbforums.com> wrote in message news:3523727.1067143572_at_dbforums.com...
>
> Originally posted by Marshall Spight
>
>
> > Um, can I ask a question? How do you handle what the RDBMS guys
> > call a "many-to-many" relationship? That is, let's say we have people
> > and phone numbers, and a single person may have arbitrarily many
> > phone numbers, and a single phone number may be shared by
> > arbitrarily many people. For example, I have a land line at my
> > house that's associated with me, my wife, and my two kids. I
> > also have a cell phone that's associated just with me, and my
> > wife also has a cell phone. And I have a work phone: just for
> > me. My wife and I share a fax number, too. There's also a
> > main switchboard number at work that you can use to reach
> > me, but also everyone in my building. Get it?
>
> > How can you have "the data within the same item" when
> > there's no way to pick one vs. the other?
>
> Sure you can ask questions. I might well ask some of my own. It's one of
> the reasons for participating right?

Right.

> I guess I should start the reply by saying yes, we *can* have "the
> data within the same item" - but we don't have to. Just because we
> have multi- and sub- values available to us doesn't mean we have to
> use them. Therefore, any data structure you care to come up with for
> an SQL-relational database could be applied in the exact same form on
> a Pick database.
>
> In your example, we could have:
>
> File: People
> ID: PersonalID
> 001: Phone Numbers (multivalued)
>
> ..and any other data relating primarily to the person
>
>
> and:
>
> 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
>
> or we could have: [snip]

Okay, I'm not 100% sure I understand, because your notation is new to me. But if I read it correctly, you have a list (multivalue?) of phone numbers for each person and a list of persons for each phone number, is that correct?

If it is correct, then there is a potential problem, illustrated by this dataset:

File: Persons
ID: Marshall
PhoneNumbers: 123-4567, 234-5678
ID: Mike
PhoneNumbers: 222-2222

File: PhoneNumbers
ID: 222-2222
Persons: Marshall

See? The cross-table ids don't match. In the Persons file, 222-2222 belong to Mike; in the PhoneNumbers file, it belong to Marshall.

Is this possble in Pick, and if so, what, if anything, can be done about it? (If it's not possible, what did I get wrong?)

thanks,

Marshall Received on Sun Oct 26 2003 - 08:36:14 CST