Re: Agility and Data Design (was: Dreaming About Redesigning SQL)

In article <Abmmb.22964$HS4.88856_at_attbi_s01>, Marshall Spight <mspight_at_dnai.com> writes
>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?

If you just want a list of phone numbers ... simple!

With the PERSON FILE, one of the fields is PHONE. A single person RECORD can contain a list of phone numbers (a second FIELD could be PHONE_TYPE if you wish).

Then by indexing on PHONE, you can find out all the people who have access to any particular phone. That's treating a phone number as an attribute of a person.

If you want to treat a phone number as an attribute of a building, you now have a problem with cell phones. If you want to treat a phone as an entity in its own right, then it's either just related to people, in which case it's pretty much the same as an attribute of a person, or it's related to both people and buildings, in which case you have the same problem in MV and relational.

But basically, we would solve the "many to many" in pretty much the same way as you would with relational. Except that rather than having a table containing only foreign keys to link entities, we simply put a list of the foreign keys in the entity's record.

Cheers,
Wol

-- 
Anthony W. Youngman - wol at thewolery dot demon dot co dot uk
Witches are curious by definition and inquisitive by nature. She moved in. "Let 
me through. I'm a nosey person.", she said, employing both elbows.
Maskerade : (c) 1995 Terry Pratchett