Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id C7552420B41 for ; Wed, 1 Feb 2012 19:49:19 -0400 (AST) Received: from cerberus.pinpointresearch.com ([66.7.238.130] helo=polaris.pinpointresearch.com) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1RsjvT-0005yS-8s for pgsql-admin@postgresql.org; Wed, 01 Feb 2012 23:49:20 +0000 Received: from betelgeuse.pinpointresearch.com (betelgeuse.pinpointresearch.com [192.168.1.179]) by polaris.pinpointresearch.com (Postfix) with ESMTP id 4DF0EE00EC84; Wed, 1 Feb 2012 15:49:06 -0800 (PST) Message-ID: <4F29CF72.6040403@pinpointresearch.com> Date: Wed, 01 Feb 2012 15:49:06 -0800 From: Steve Crawford User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.9.2.24) Gecko/20111109 Lightning/1.0b2 Thunderbird/3.1.16 MIME-Version: 1.0 To: brooks.glenn@comcast.net CC: pgsql-admin@postgresql.org Subject: Re: overcoming a recursive relationship in a sql statement References: <835571162.336754.1328138827494.JavaMail.root@sz0007a.emeryville.ca.mail.comcast.net> In-Reply-To: <835571162.336754.1328138827494.JavaMail.root@sz0007a.emeryville.ca.mail.comcast.net> Content-Type: multipart/alternative; boundary="------------010501090904060508010903" X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201202/4 X-Sequence-Number: 39064 This is a multi-part message in MIME format. --------------010501090904060508010903 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit 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 --------------010501090904060508010903 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 7bit 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

--------------010501090904060508010903--