Re: Dreaming About Redesigning SQL
Date: Tue, 28 Oct 2003 06:59:19 -0500
Message-ID: <3530842.1067342359_at_dbforums.com>
Originally posted by Mike Preece
> Interesting. I come from a more practical background that one based on
> theory, so I hope you'll bear with me on this...
>
So do I, but I find that understanding the theory is helpful.
Originally posted by Mike Preece
> I consider indexes to be, if not exactly data, then at least a valid
>
Agreed. The important difference is that the index is maintained by the DBMS, it is not a case of "I have added a record to the table, so I better go update the index to match".
Originally posted by Mike Preece
> Phones don't have people, people have phones. In most real-world
> situations we'd have a Persons file, and one of the attributes would
> be a multivalued list of (>=0) phone numbers, possibly with an
> associated type, possibly with a defined delimited string pattern (as
> in +##-0###-###-####). We probably wouldn't have a person's phone
> number anywhere other than as a value on the item in the Person's
> file. If two people share the same number - then that phone number
> would be recorded twice, once against each person. If we have a need
> to get a list of possible callers from a particular phone number we
> would most probably index the file on phone number. Because we're more
> likely to want information about people than we are about their
> phones, we have direct hashed access to the Persons file to get, among
> other things, a list of their phone details, but have to go through an
> index to get a list of people with a particular phone number.
> File: People
> ID: Person
> 001: Phone number (multivalued)
> ...etc, with an index on attribute 1.
>
> If the requirements are different and primarily concern identifying
> the location and/or list of people related to a phone number, with
> caller-id for instance, that's different. Then we'd most likely have a
> Phones file with a Location ID for each phone. We'd probably not have
> the people's names or other details on each Phone item - it's more
> likely that we'd reference a Location first, and then reference the
> People recorded against the Location for more details. If we need the
> phone numbers for a location or person we would use an index or two
> (sound familiar).
> File: Phones
> ID: Phone number
> 001: LocationID
> ...etc., with an index on attribute 1.
> File: Locations
> ID: LocationID
> 001: PersonalID (multivalued)
> ...etc., with an index on attribute 1.
> File: People
> ID: PersonalID
> 001: Name
> ...etc.
>
> In both scenarios we have a many:many relation - but they're not
> equivalent. Can you describe a scenario in which it would be equally
> as important to get one part of a many:many relation as the other?
>
Yes, I agree that people and phones is not a "well balanced" example. Though it's worth pointing out that since the relational model avoids making such a value judgement, if it transpires that in the future we are very interested in a phone-centric view of the data, our database design is not biased against it.
-- Posted via http://dbforums.comReceived on Tue Oct 28 2003 - 12:59:19 CET