Re: Dreaming About Redesigning SQL

Originally posted by Mike Preece

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

Fair. Let's move on!

--
Posted via http://dbforums.com