Re: Dreaming About Redesigning SQL

Jonathan Leffler <jleffler_at_earthlink.net> wrote in message news:<Jy0qb.549$Z25.266_at_newsread4.news.pas.earthlink.net>...
> Mike Preece wrote:
> > andrewst <member14183_at_dbforums.com> wrote:
> > >First of all, as Bob pointed out yesterday, the relational model does
> > >allow for relation-valued attributes, so that all the phone numbers for
> > >a person could be stored within the single person record. However, that
> > >would not be the "traditional" relational approach to this.
> > Why not? Is there something in relational theory that says that would
> > be wrong in some way?
> There are two reasons why RVA (relation-valued attributes) are not
> used in the traditional approach.
> One is the horribly pragmatic point that neither SQL DBMS nor any
> RDBMS (possibly excepting Alphora) actually implement support for
> RVAs, so it is difficult to use in practice what only exists in theory
> (that sounds familiar - c.d.p should be happy with that). The main
> reason that RVAs have not been implemented is that they were only
> recognized as valid in the last 10 years or so, and SQL has been
> standardized rather longer than that (17 years or so).

Why were RVAs intoduced (or accepted) into relational theory in the firs..., I mean, 10 years ago?

Why haven't any of the enormous SQL-relational DBMS companies implemented support for them? I would have thought 10 years would have been long enough and it's not as though their R&D is under-resouced exactly.

> The other reason, which might apply even if RVAs were available for
> use, is that it introduces an asymmetry between the tables

Yes. That's what we've just been discussing isn't it? I thought we had agreed that there *is* an asymmetry in the Persons:Phones relationship - "so that all the phone numbers for a person could be stored within the single person record".

> whereas the
> traditional solution preserves that

...false...

> symmetry. Polya said it best in
> his "How to Solve It!" book - "Try to treat symmetrically what is
> symmetrical, and do not destroy wantonly any natural symmetry".

I guess he wouldn't have been in favour of people having things arse about face then.

> In
> this case, there is a considerable bias towards the view that you
> don't usually need to know who owns a given phone number (but you do
> need to know the phone numbers of a given person).

Yes.

> The traditional
> relational solution treats those queries substantially the same.

Substantially? Why is that word in there?

> Using an RVA, the code for one query is considerably different from
> the other.

Interestingly enough, in Pick they would be:

List Persons Name with PhoneNumber "12345" and
List Persons "Mike" PhoneNumber

and would run extremely efficiently regardless of whether Mike's got one or a dozen phones or how many of his numbers are shared by other people.

> So, if you were sufficiently confident that the RVA design was not
> over-complicating your queries, there is no reason not to use it if
> your DBMS supports them.

Big IF apparently.

> If your DBMS does not support RVAs, then you
> can't use an approach which depends on them.

Go directly to square one. Do not pass go. If you see Bob on the way give him a good kicking.

> And one of the beauties
> of the relational model (which PICK might also claim - I'm not sure)
> is that if you need to change your mind (so that RVAs are not a good
> idea after all), then you can probably change the database too without
> suspending the entire system.

Well gee it sure doesn't look beautiful to me - but then I'm not beholding.

Let's look at the evidence:

We agree that the person is more relevant in the persons to phones relationship.
We agree that the relational model ignores that relevance. Someone says we can use RVAs.
Someone else says they don't actually exist - and that they would complicate things too much.

What? We're trying to get someone's phone number here guys!

It's simple. Pick does it simply. Someone said earlier in this thread that "perfection" is important to relational database people. Is it really? Received on Thu Nov 06 2003 - 11:36:37 CET