Re: Dreaming About Redesigning SQL

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.

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

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? Received on Sat Nov 01 2003 - 06:41:12 CET