Skip site navigation (1) Skip section navigation (2)

Re: overcoming a recursive relationship in a sql statement

From: Steve Crawford <scrawford(at)pinpointresearch(dot)com>
To: brooks(dot)glenn(at)comcast(dot)net
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: overcoming a recursive relationship in a sql statement
Date: 2012-02-01 23:49:06
Message-ID: 4F29CF72.6040403@pinpointresearch.com (view raw or flat)
Thread:
Lists: pgsql-admin
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

In response to

pgsql-admin by date

Next:From: Mario SplivaloDate: 2012-02-02 07:36:35
Subject: ssl3 errors in replication.
Previous:From: Craig JamesDate: 2012-02-01 23:37:27
Subject: Re: overcoming a recursive relationship in a sql statement

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group