Re: Dreaming About Redesigning SQL
Date: Fri, 07 Nov 2003 09:58:01 -0500
Message-ID: <3FABB2F9.4070808_at_quest.with.a.w.net>
andrewst wrote:
> Originally posted by Mike Preece
>>>>Why were RVAs intoduced (or accepted) into relational theory >>> >> in the
>
>>>>firs..., I mean, 10 years ago? >>[snip]
> As I have said before, the fact that RVAs are acceptable doesn't mean
> they should be used to model many:many relationships. I would only use
> an RVA if I considered the RVA data to be some kind of "blob" of data I
> had no more intention of interrogating in the database than I would a
> bitmap image. And Date and Darwen were responding to the "demand" for
> complex data types from the OO camp, not trying to replicate Pick. They
> say RVAs are "legal", they don't say they should be used instead of
> normalisation.
When you put "demand" in quotes, you are implying that it is somehow to
be belittled. Perhaps D & D were responding to a legitimate issue in
modeling data which could be best served by RVA. I agree they were not
replicating Pick, tho' they were probably aware of the mv model well
before this (see previous posts), so maybe it was an influence.
>
[part about query syntax snipped]
>>It's logical to store phone numbers against a person. It's also more >>efficient.
>
> .. in Pick. It is not more efficient in an RDBMS. But anyway my point
> was that you are saying that the query by phone would also be "extremely
> efficient". But presumably not AS efficient as the query by name, since
> you say that is "more efficient".
> So, you have:
> Query By Efficiency
> Name X
> Phone Y
> where X > Y
>
> Whereas in relational we have:
> Query By Efficiency
> Name X
> Phone Y
> and we can choose whether X = Y, X > Y or X < Y according to decisions
> made at the PHYSICAL level, which do not require any change at the
> LOGICAL level, i.e. the SQL for a given query remains unchanged.
What I understand you to say is that in relational design, you can post-facto change the physical data layout (for efficiency reasons?) and not have to change the overlying app. Yes, this is not possible in Pick.
However, in my experience changing the overlying query in Pick is typically very simple and, given good design, one should not have to change the underlying physical design often, if ever. In Pick (and I suspect equally so in relational) there's pretty much no problem in extracting different views even if the original design didn't explicitly take those into account.
All this seems to me much arguing at the angels-on-a-pinhead level. Pick allows easy access to data, albeit sometimes procedurally, so if someone wants a new view of the data, it's typically a straightforward process. You can, of course, always paint yourself into a corner, but in practise it rarely happens if you follow basic rules of design. So the argument about syntax equivalence and access methodolgy and even logical/phyiscal independence are simply good mental (I was going to use the m-word, but) excercises.
> You of course will contend that the Pick X is better than the RDBMS X,
> and I will disagree, but we can't really test that here can we?
> However, I will say that in Oracle (e.g.) it is perfectly possible that
> the query to list "Mike" and all his phone numbers can be achieved with
> 1 disk read, provided the data is clustered and fits into 1 block
> (typically 4K or 8K); or 0 disk reads if we read the same block earlier
> and it is still in the cache.
>
Agreed, the number of disk reads is not the issue. Access to the data in
a reasonable fashion is.
>
>
>>It just so happens that the physical implementation is a "natural" one >>in Pick. If you decide it's logical to record phone numbers against a >>person - then that's what you do in Pick and that's how it's stored >>physically. I'm sorry if you folks have a hard time accepting that >>that makes sense. Maybe it's because it's nigh on impossible to do in >>relational without using RVAs
>
> It just so happens that Pick has no concept of logical/physical
> distinction. That is Pick's handicap, not relational's. Sorry if you
> have a hard time understanding that.
No problem, agreed, with the caveat that the handicap is not much of one.
> You have NO CHOICE over logical
> design: it HAS to correspond to the physical design you select for
> performance reasons;
Yes problem: this is absolutely not true. Logical design is just that in Pick-logical-and it relates to, but is not corrspondent to, physical. Are you saying that by having to use the name of a Table (a "physical design element") there's a "correspondence" between physical and logical? If not, Can "logical design" be completely ignorant of the physical? I don't, of course mean where the data is laid out or anything like that, but how it is organized.
we have choice: our logical design is optimised
> for querying, our physical design is optimised for performance.
> Forget RVAs.
Too bad, RVAs are the point of convergence between our views.
> No, because those are 2 different LOGICAL designs.
Are they not different physical designs if you're accessing different
tables to get the same information?
>
>>Would the statement be identical whether the data is coming from the
>>PersonsPhones table or from RVAs in the Persons file?
Chandru Murthi Received on Fri Nov 07 2003 - 15:58:01 CET