Re: A question for Mr. Celko

"Paul" <paul_at_test.com> wrote in message news:41029242$0$25120$ed2619ec_at_ptn-nntp-reader01.plus.net...
> Marshall Spight wrote:
> >> What it buys you is the ability to stick with first-order logic I
> >> think. Once you've jumped to higher-order logic things are a lot
> >> more complex and some nice results no longer hold etc.
> >
> > I used to agree that this is a jump to a higher order, but I don't
> > think that any more. I don't see anything about RVAs that's
> > incompatible with FOL.
>
> Do all values in the RVA column have to have the same columns?
> i.e. is there just one RVA type or a different type for each relation
> that has a different column setup?

Why are you trying to make it so complicated? If you spent as much effort trying to make it easy as you spend trying to make it hard, you'd be done now.

All attribute values are of the same type, just the same as any other attribute.

> In first-order logic your variables can only be values, not predicates.
> If you have RVAs that the RDBMS "understands" then you are going to have
> variables that are relation-valued ie. predicates.
>
> Now this isn't a problem if you are just storing the relations as an
> "encoding" that is invisible to the RDBMS, but once you let the RDBMS
> see the structure of your RVAs directly it jumps from being a value to
> being a predicate.

Again, you're just making it hard for no reason I can discern.

> >> From an engineering perspective, suppose you have a RVA as opposed
> >> to a separate relation with a foreign-key constraint. Then a bit
> >> later you find you need to add to your database another relation
> >> that needs to be JOINed to the relation inside the other one.
> >>
> >> You'd have to change over to using the separate-table method. So
> >> RVAs lose you the flexibility of being able to have your "inner" or
> >> lookup relation JOIN to several other relations in the future.
> >
> > I'm saying there is no "change over". The two ways of thinking about
> > the two relations are *views* onto the *same data*. Both views can be
> > present at once; there's no need to choose between one way of
> > thinking about it vs. the other.
>
> OK then, how does a view created in this way differ logically from a
> standard view created by merely JOINing the two tables?

What makes you think I think there should be a logical difference? Because in fact I *don't* think there should be a logical difference.

> e.g. tuples using RVA:
>
> (1, 'Fred', {('a', 'apple'), ('b', 'banana')})
> (2, 'Bill', {('c', 'cherry')})
>
> vs. tuples from a view of persons JOIN fruit:
>
> (1, 'Fred', 'a', 'apple')
> (1, 'Fred', 'b', 'banana')
> (2, 'Bill', 'c', 'cherry')
>
> where persons is
>
> (1, 'Fred')
> (2, 'Bill')
>
> and fruit is:
>
> ('a', 'apple', 1)
> ('b', 'banana', 1)
> ('c', 'cherry', 2)
>
> (Each fruit can only be had by one person, bit of a bad example here!
> but you get the idea: persons:fruit is one:many).
>
> The point I'm making is what does the RVA-view give you that the
> standard view doesn't?
>
> Could you give a concrete example of where you think a RVA would be useful?

Let me expand your persons table to include (3, 'Joe')
and I'll leave your fruit table the same. Joe is fruitless.

Now I want to know what fruit everyone had. The right answer is

(1, 'Fred', {('a', 'apple'), ('b', 'banana')})
(2, 'Bill', {('c', 'cherry')})
(3, 'Joe' {} )

Can't do that with vanilla join. You need to introduce the evil outer join, which means you need nulls now, unless you have RVAs. Death to nulls; long live empty sets!

On the other hand, I'm not sure what operation I *did* just introduce. It's not a plain inner join, at least not as it is commonly described. OTOH, maybe I didn't introduce an operator; maybe that view is there implicitly because of the foreign key from fruit to persons.

Compare to your persons JOIN fruit table. It has nothing to let us know about Joe, and it is denormalized.

> I'm thinking for example of an invoice table that stores invoice lines
> in a RVA in each row Pick-stylee, but I think this might not be the kind
> of application you have in mind.

No, I think that's pretty much what I'm talking about. It's not actually a very big deal; it's mostly just for convenience.

> Because the problem in this case would be you are taking your single
> logical relation-value and splitting it up into multiple smaller
> relation-values, one for each invoice.
>
> You're also losing the invoice-line relvar and kind of incorporating it
> within the invoice relvar.

Not losing. Two views of the same data. The invoice-line relvar is still there.

Marshall Received on Sat Jul 24 2004 - 23:14:21 CEST