Re: Dreaming About Redesigning SQL

"andrewst" <member14183_at_dbforums.com> wrote in message news:3552095.1067851333_at_dbforums.com...
>
> Originally posted by Mike Preece
>
> > andrewst <member14183_at_dbforums.com> wrote in message
> > news:<3544856.1067597724_at_dbforums.com>...
>
> > > Originally posted by Mike Preece
>
> > > > andrewst <member14183_at_dbforums.com> wrote in message
>
> > > > news:<3540467.1067517546_at_dbforums.com>...
>
> > > > > Originally posted by Mike Preece
>
> > [snip]
>
> > > > > > Disregarding physical implementation issues for the
> > moment, can we
>
> > > > > > agree on what it is we disagree about on a logical
> > level?
>
> > > > > > To my mind, one side of a many:many relationship will
> > *usually* have
>
> > > > > > more importance than the other. This is illustrated in
> > the
>
> > > > > > people:phones relationship. Usually, we will be
> > "primarily" interested
>
> > > > > > 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.
>
> > > > Point of agreement: "In many cases one side of a many:many
> >
>
> > > > relationship can be considered to be of primary importance.
> > The
>
> > > > relational model attaches no more importance to one side than
> > the
>
> > > > other. The Pick model *allows* multiple secondary values to be
> > grouped
>
> > > > according to the primary value in their relationship, although
> > it
>
> > > > doesn't require them to be."
>
> > > > Fair? I think I'm simply restating what you've already said -
> > although
>
> > > > I want to be perfectly clear on this before we go on to your
> > further
>
> > > > point about optimisation v penalisation in the logical models.
> >
>
> > > Fair. Let's move on!
>
> >
>
> > Right! ;o)
>
> >
>
> > Oh, just before we do, to be 100% accurate, I think perhaps I should
>
> > have said: "The Pick model allows attributes that are empty, single,
>
> > multi or sub-multi values". (Still trying to be as clear as I can
>
> > 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.

In the relational model it could also be a single relation valued attribute.

> > If it becomes necessary to record more than one phone number against a
>
> > person, does the relational model dictate that the logical schema must
>
> > change?

Not if it is a relation valued attribute.

> > 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"?

Reference is the correct terminology, you got that right. Using a relation valued attribute means the relation is just another attribute and does not require any references.

> > 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.

A relation valued attribute need not be in a separate relvar. I suggest a little more study is in order for you. Received on Mon Nov 03 2003 - 13:39:21 CET