Re: Dreaming About Redesigning SQL

andrewst <member14183_at_dbforums.com> wrote in message news:<3540467.1067517546_at_dbforums.com>...
> Originally posted by Mike Preece
> > andrewst <member14183_at_dbforums.com> wrote in message
> > news:<3535528.1067427042_at_dbforums.com>...
> > > Originally posted by Mike Preece
> > > > andrewst <member14183_at_dbforums.com> wrote in message
> > > > news:<3530842.1067342359_at_dbforums.com>...
> > > > > Originally posted by Mike Preece
> > > > > > In both scenarios we have a many:many relation - but they're not
> > > > > > equivalent.
> > > > > 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.
> > > > Interesting way of putting it. The Pick model itself allows
> > > > optimisation in the database design according to the importance placed
> > > > on relationships between various data by the user - and is itself
> > > > flexible enough to be easily adaptable to changing requirements,
> > > > whereas the relational take on it is that it ain't exactly perfect but
> > > > it's near as needs be and it'll be near as needs be forever more
> > > > regardless of the requirements so long as the data stays essentially
> > > > the same.
> > > > I think this might be an important point and one that is easily
> > > > overlooked. Each requirement must be treated on its merits. If the
> > > > requirements change in future then the system must be adaptable to
> > > > those changing requirements. Downunder, where I'm from, there used to
> > > > be a "She'll be right!" mentality - still is to a degree. If the tool
> > > > wasn't quite right for the job, or a job wasn't done strictly
> > > > according to requirements - near enough was good enough. "Who cares?
> > > > if it gets the job done? Might not be spot on but near enough's good
> > > > enough!". Is that the mentality we, as database "experts" should be
> > > > adopting?
> > > I don't agree with your analysis of the relational approach. The
> > > relational approach to storing the data is not sub-optimal or "near as
> > > needs be". rather it is "as good as it is possible to be". If you want
> > > to optimize for access via a specific path, you can add appropriate
> > > indexes, or even physically cluster the data. That happens at the
> > > physical level, without altering the logical database design and
> > > therefore without requiring ANY changes to applications that use the
> > > data - not a single change, not even a recompile. Relational purists
> > > may be accused of many things, but settling for less than perfection is
> > > not one of them!
> > > I am not claiming that Pick is bad, I'm sure it is perfectly capable of
> > > supporting robust and well-performing databases. But I do believe that
> > > the relational model is built on a better foundation, and should be the
> > > bedrock of future DBMS development.
> > 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. Received on Fri Oct 31 2003 - 00:51:45 CET