From: Steve Crawford <scrawford@pinpointresearch.com>
To: brooks.glenn@comcast.net
Cc: pgsql-admin@postgresql.org
Subject: Re: overcoming a recursive relationship in a sql statement
Date: Wed, 01 Feb 2012 15:49:06 -0800
Message-ID: <4F29CF72.6040403@pinpointresearch.com> (raw)
In-Reply-To: <835571162.336754.1328138827494.JavaMail.root@sz0007a.emeryville.ca.mail.comcast.net>
References: <835571162.336754.1328138827494.JavaMail.root@sz0007a.emeryville.ca.mail.comcast.net>
On 02/01/2012 03:27 PM, brooks.glenn@comcast.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: scrawford@pinpointresearch.com, brooks.glenn@comcast.net
Subject: Re: overcoming a recursive relationship in a sql statement
In-Reply-To: <4F29CF72.6040403@pinpointresearch.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox