Re: O'Reilly interview with Date

Kenneth Downs wrote:
>
> This leaves all of the rest of the columns in the universe. I would
> contend that each of these columns has exactly one table where this column
> is the unique key. If this is true, then that means that the column's
> presence in all other tables is as a foreign key either to its own table or
> to some intermediate table where it is part of a compound key.

We also have the case of a column that is both primary key and foreign key. I don't think that conflicts with what you're saying, though.

> To disprove the idea, you'd have to have an example of a JOIN that produced
> true results between two tables where both tables contained the same column
> but where the tables were not joined by a foreign key.

What about a self join?

> > Is it OK to define equality in a non-symmetrical and non-transitive way
> > like this, with different types?
>
> I would ask the question, does it produce correct results? Or perhaps,
> results that are consistent and predictable, to which the answer is yes.

Consistent and predictable is not a sufficiently strong criteria. You're breaking some properties of equality when you do this. For one thing, it's weirdly typed now. I would expect that you can only join two columns when they have the same type, but you're proposing that one can join when two column have the same type or when one is an interval type and the other is a scalar of that interval.

> > I agree a lot of non-standard joins are due to badly-designed schemas,
> > but I can think of examples where you might want to define a join with a
> > "greater than" clause - for example when joining a table to itself to
> > get some kind of ranking number. (How many other salespersons have total
> > sales greater than each salesperson?) I can't see how things can be
> > designed to get around this.
>
> Hmmm, I actually need an answer to this myself.

You can do everything with equijoin. If you want to know all the pairs of people (A, B) where A is taller than B, then

select A.name, B.name from A, B where A.height > B.height;

Note that the > test is a restrict, not a join type. Here we're equijoining on zero attributes.

I am fairly convinced that you will run into some severe bad properties by changing the definition of equality. However, I don't see anything that would argue against declaring a foreign non-equality relationship. Why not simply say you have a thing similar to a FK relationship, but with the operation being "within interval" instead of "equal to?"

Marshall Received on Sun Aug 14 2005 - 06:46:57 CEST