Re: Dreaming About Redesigning SQL
Date: 3 Nov 2003 09:10:47 GMT
Message-ID: <3552095.1067851333_at_dbforums.com>
Originally posted by Mike Preece
> > > > > in a person's phone number(s). More than one person might
> have a given
> > > > > phone number but we will usually obtain the phone
> number(s) for a
> > > > > known person, and only occasionally will we need to obtain
> the person
> > > > > or people assigned to a given phone number. In certain,
> more unusual,
> > > > > scenarios we will be "primarily" interested in the person
> or people
> > > > > assigned to a known phone number. In both scenarios one
> side or the
> > > > > other of the many:many relationship can be considered to
> be of
> > > > > "primary" importance. Do we agree on this or
> disagree?
> > > > I certainly agree that it may be true sometimes. Whether it
> is
> > > > *usually* so, I'm not so sure - I suppose because with my
> relational
> > > > background it isn't something I need to consider generally.
> The kinds
> > > > of "many to many" that spring to my mind right now seem
> generally to be
> > > > quite balanced: employee/project, employee/department,
> order/product.
> > > > Probably I am unconsciously avoiding thinking of counter
> examples ;o)
> > > > Anyway, for the sake of this discussion let's agree that in
> many cases,
> > > > we are primarily more interested in one side of the
> relationship than
> > > > the other: i.e. that we expect more queries to be made from
> one
> > > > direction than from the other.
> > > > Now, if I may go on a step, I would suggest that on the
> logical level,
> > > > relational does not favour the "primary" access path over
> the
> > > > "secondary", where as the MV model does (unless both access
> paths are
> > > > stored independently). But that does not mean that the
> primary access
> > > > path is penalised at all: on the contrary, it means that the
> secondary
> > > > access path is not penalised either. It's a win:win
> situation!
> > > I think we're understanding each other so far. Still in
> disagreement -
> > > but that's OK.
> here.)
>
> Assuming that the requirements dictate that the Person is of primary
> importance in the Persons:Phones relationship:
>
> Pick allows - as a single item (row):
>
> File: People
> ID: Person
> 001: First Name(s)
> 002: Family Name
> 003: Date of Birth
> 004: Phone number(s)
>
> Pick = Relational theory equivalent terms (roughly):
> File = Relational Table
> ID = Primary Key
> Attributes 001-004... within items = Columns in the table
>
> In the relational model, as in the Pick model, if everyone had just
> one unique phone number (or none at all), the phone number could be
> considered to be an attribute of the person in exactly the same way
> that their name or date of birth could be.
>
> change? I don't know the correct terminology, but would the list of
> phone numbers for a person have to be held separately in its own table
> and "referred to"?
>
> This doesn't have to happen in Pick. An attribute can be multivalued.
> If we want the data available for a Person, we look at the Person's
> item. All of the attributes of a Person are available - in the same
> "row". It doesn't matter whether they have single or multiple values.
>
Yes, in the relational model if you had originally allowed for only one phone per person by putting a phone_no column in the person table, and now wanted multiple phones per person, you would have to move the phone_no attribute to a separate table. However, this isn't something one has to do very often in my experience, because the process of designing a relational database forces one to ask and answer questions like "can a person have more than one phone?" up front. And clearly the answer is "yes". Of course, there is still a choice of solutions:
- Don't care, we just want ONE of the person's phone numbers. This solution will probably be regretted in the future.
- OK, let's allow (say) 3 columns: home_phone, work_phone, mobile_phone. Not an uncommon solution.
- Separate table. As many phones as you like!
Originally posted by Mike Preece
> Do you agree that a list of a person's phone numbers can and should be
> considered an attribute of the person - just as a single number is -
> provided an acceptable means exists to access all "rows" containing a
> given phone number?
No, I don't. Well, perhaps I am just being contrary! But if you have a
list of phone numbers for a person, you probably need further attributes
-- Posted via http://dbforums.comReceived on Mon Nov 03 2003 - 10:10:47 CET