Re: Dreaming About Redesigning SQL
Date: Wed, 05 Nov 2003 09:36:04 -0500
Message-ID: <3562363.1068042964_at_dbforums.com>
>
> > If minimising disk space is an issue, a DBMS
> > like Oracle offers strategies for doing so, such as
> clustering
> > (physically storing the phone numbers with the person record -
> without
> > changing the logical 2 table design). Clustering also achieves
> the
> > "person together with all phone data in one hit"
> requirement.
>
> Hmmm... I think I'd like to understand the distinction here a little
> better. Perhaps if it's good enough for Oracle then maybe we could say
> that maintaining the many:many relationship and data associated with
> each phone in the Persons file using multivalues also retains the
> "logical two table design". Doesn't seem quite kosher to me though.
>
No, it wouldn't be kosher in Pick, because apparently for Pick the
logical and physical model are the same thing. Using the MV approach
you have 1 logical and 1 physical table. Whereas using a cluster means
that we have 2 logical tables and 1 physical "cluster" which contains a
combination of data from both logical tables. There is no way you can
or would need to know that the data was clustered when using SQL; it
appears to be in 2 separate tables - it IS in 2 separate tables, which
are clustered. What this means is that if I want to select person
"Mike" and all his phone numbers, I will query 2 logical tables ("select
.. from person, person_phone where ..."), but that physically all the
data will be read from 1 database "block" at once.
>
> > there
> > are "index-organized tables" for which person_phones is a
> > candidate;
>
> Without knowing exactly what they are I would guess they're similar to
> likely
> Pick indexes based on correlatives.
>
Without knowing what THOSE are I couldn't say ;o)
An index-organised table is a table that is, er, organised like a (btree) index. This is a physical implementation suitable for tables with only a few columns, and means that instead of doing an index lookup followed by a table lookup, we do a an index lookup and find the data we need right there in the index(-organised table).
>
> > there are hash join algorithms used when the person and
> phone
> > data for many people is required e.g. by a report or batch
> process.
>
> I don't think we'd need this so much in Pick.
>
Maybe not. Question: when you read the person record for 1 person, does Pick actually only physically read into memory the data for that 1 person, or does it read a "block" of records that include the record for that person? Hash joins would be useful in the latter case.
>
> > But the idea that the relational approach is a
> > "she'll be all right" approach is utterly absurd! I urge you to
> educate
> > yourself about it and understand why. Unlike Pick, the
> relational model
> > is not a product, it is a theory on which products can be based
> (wholly
> > or partially); a theory whose purpose is to find the BEST way to
> store,
> > manipulate and retrieve data.
>
> How about if I tone it down and say instead it's an "any colour so
> long as it's black" approach? I have to say again that I understand
> that relational theory does an excellent job at determining the
> optimum database design for *related* data - *BUT* it ignores
> *relevance*. I think it is absurd to do so.
>
>
> On reflection, we seem to have drifted off the "logical" differences
> into "physical implementation" issues. Can we return to the
> differences in the logical models. My argument is that Pick is more
> flexible in adapting to real world requirements - *and* that it is
>
OK. So is your argument based on the example where we originally designed for 1 phone per person and now want many phones per person?
If so, I will grant you that on the "flexibility" count. But I have to point out in mitigation that, since relational is less flexible in this regard, a decent database analyst/designer will take pains to get this kind of detail right first time. A lot of up-front analysis consists of confirming with users "so, each person can only ever have 1 phone, is that always true", etc. Sometimes he/she will still get it wrong (or is misinformed), and yes there is a price to be paid. On the other hand, if we have designed for many:many, we will never have to re-design or re-code if we decide that phones are more "relevant" than people after all. Swings and roundabouts?
I don't know where efficiency comes into it though?
-- Posted via http://dbforums.comReceived on Wed Nov 05 2003 - 15:36:04 CET