Re: abstraction of table relationships
Date: Fri, 08 Sep 2006 00:43:42 GMT
Message-ID: <213Mg.10781$9u.154966_at_ursa-nb00s0.nbnet.nb.ca>
David Cressey wrote:
> "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.
Marshall already ceded the above points.
> 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.
It's irrelevant actually. Either the values are equal or they are not. If the domains have no overlap, no values will be equal and the result will be empty.
As far as failure modes go, quickly determining that a result will have an empty set and returning that in its entirety is a lot better than the way SQL treats certain fairly obvious mistakes.
> 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.
The problem is one will generally have multiple paths and the shortest path is not always the intended path. See your own examples above regarding bills of materials and organization charts.
>>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.
Natural join is symmetric, associative and commutative. If you imagine some distinction among tables, you operate in the realm of fantasy.
> Meanwhile, I'm moving fast to stay a few steps ahead of the Spanish
> Insquisition, after using terms like "entity table" and "relationship
> table".
If only you were sufficiently clueful to recognize that objections to the terms have nothing to do with religion and everything to do with mathematical properties. Received on Fri Sep 08 2006 - 02:43:42 CEST