Re: O'Reilly interview with Date

Kenneth Downs wrote:
> 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. One example might
> be a job-based workflow system, where all invoices and production orders
> are tied together through a job control system. Both Sales Orders and
> Purchase Orders are children of the JOBS table, so each of them contains
> the JOB_NO column, but they are not related to each other by a key. So you
> can join them, but to what purpose? You get a meaningless jumble of Sales
> order and purchase order properties. A more proper relationship between
> these two tables would be a UNION, which might give the meaningful result
> of showing activity over time. In fact there is probably another thesis in
> here that two children of a single parent relate as UNIONs.

For this semantics it might not make sense, but it other cases it could be a valid thing to do.

For example, suppose you have a table of products.

Then another "child" table that gives the available colours for each product (assume the range or colours is different for each product).

And another similar table for the available sizes.

Now you might want to return a list of all available (colour,size) combinations for each product code. Assuming the product code is part of the primary key of the two child tables, you could avoid a join to the main table by joining the two directly.

Hmm maybe the optimizer would be able to do this internally though.

Paul. Received on Sun Aug 14 2005 - 11:58:30 CEST