Re: O'Reilly interview with Date

Paul wrote:

> Kenneth Downs wrote:

>>>OK maybe I'm being dense here, but when you now specify your two tables,
>>>"examples" and "examples_xref", how does the system know which of the
>>>foreign key constraints to use to generate the join?
>> 
>> Foreign keys are distiguished by suffix and prefix, in the example above
>> on of them has the suffix "_TO", so when I specify a query I specify that
>> suffix and now it can identify the fk definition and from there the
>> columns.

Ah, this question gets to the heart of things.

First is the dogmatic answer. The dogmatic answer is that well-designed tables will always join by keys. The key (or keys) defines the only meaningful linking of the two tables in normal operations. Therefore you specify the key because that is how they join, it is actually unnatural and downright incorrect to specify columns.

So for me personally if a small tight group can control the table design, then you get well-behaved systems and we can afford, in those situations, to be dogmatic.

But SQL, in giving us the necessary flexibility to JOIN outside of a key, actually makes it seem through years of use that columns are the natural way to join tables, when in fact they are not, they are only an expedient.

>
> What if you have a non-standard join? For example start_date and
> end_date columns and you want to join where some date is between these
> two?

This is not actually non-standard. It only appears so because there is no real support for intervals and so we all code intervals manually. But if start_date and end_date columns were defined as an interval, then comparing the interval to a single date implies "between", just as a unique constraint on the interval implies "does not overlap", and the interval definition itself generates the constraint end_date >= start_date.

-- 
Kenneth Downs
Secure Data Software, Inc.
(Ken)nneth_at_(Sec)ure(Dat)a(.com)