Re: abstraction of table relationships

"Marshall" <marshall.spight_at_gmail.com> wrote in message news:1157605072.761613.23760_at_p79g2000cwp.googlegroups.com...

[major snip]

> In fact, the better solution to this problem is making better
> use of natural join. Schema design can and should be
> done with natural join in mind. Specifically, if there is
> any kind of key relationship between two tables, then
> the keys should have the same names, and the table
> should not have other, non-relating attribute names in
> common. This is actually quite trivial to do. (It's not
> possible in *every* case but it is certainly possible in
> a strong majority.)
>

I disagree with the use of common names to establish linkage between keys.

Specifically, a reflexive relationship, where an FK relates to a PK in the same table.

Example: An employees table with two columns, namely, employee_id and supervisor_id.

Another problem is the parts explosion table, where two FKs both relate to the same PK.

Example: a part_usage table with two columns: component_part_id, assembly_part_id
These two columns both relate to the column part_id in the parts table. They can't both have the same name.

A slightly better plan is to use named user defined domains in column definitions. Then one can adopt the rule that a natural join using two columns is not meaningful unless the two columns are based on the same domain.
This helps, but it still doesn't completely address the problem you were raising in your OP.

I would venture to suggest that referential integrity constraints, or something somewhat like them, would provide the information for a "natural join pathfinder" to build a pathway based only on the iformation provided in your examples below.

> At that point, the books per author query becomes:
>
> Select * from Books natural join Authorship natural join
> Persons where PersonId = 'Steven King';
>
> Quite simple. And the editors query is:
>
> Select * from Books natural join Editorship natural join
> Persons where PersonId = 'George R. R. Martin';
>
> In fact, the above queries make quite clear what the inter-table
> roles and relationships are, whereas omitting those relationships
> leaves this question quite unclear.
>

I'm not sure about this. it seems to me that, in your example, you are taking advantage of the fact that Books and Persons are "entity tables" while Editorship and Authorship are "relationship tables". I claim there are cases that are not as obvious as your examples.

Meanwhile, I'm moving fast to stay a few steps ahead of the Spanish Insquisition, after using terms like "entity table" and "relationship table". Received on Fri Sep 08 2006 - 02:30:09 CEST