Re: Dreaming About Redesigning SQL

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

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

> > > Mike.

> >

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

>

> Mike.

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!

--
Posted via http://dbforums.com