Re: overcoming a recursive relationship in a sql statement

On 02/01/2012 03:27 PM, brooks(dot)glenn(at)comcast(dot)net wrote:
> Hello,
>
> ...
>
I'm not sure I fully understand the problem and may, therefore,
oversimplify but I'll take a stab.

It sounds like you have organizations and organizations can form
partnerships with one another. My initial approach would be to have a
table of organizations (id plus any necessary data) and a relationships
table to handle the many-to-many issue. The minimum would be two columns:
org1_id, org2_id

Naturally you would want to add whatever constraints are appropriate to
model your allowed relationships.

You could then, if you desire, add a field for relationship type
(competitor, partner, ally, etc.). Depending on the nature of your base
problem, you could even add other tables such as project and include
that data in the relationships table. This would allow two companies to
be competitors on one project but partners on another - a not unusual
situation. You could also include valid dates for relationships or
whatever else you deem necessary.

Given the appropriate recursive query, you could build a full tree of
partnerships for a given organization/project.

Cheers,
Steve