Message-ID: <41AC3C7E.10201@archonet.com>
Date: Tue, 30 Nov 2004 09:25:18 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla Thunderbird 0.9 (X11/20041103)
MIME-Version: 1.0
To: Johan Henselmans <johan@netsense.nl>
Cc: pgsql-sql@postgresql.org
Subject: Re: grouping a many to many relation set
References: <EA79F36B-4244-11D9-97F6-000D9328C5BC@netsense.nl>
In-Reply-To: <EA79F36B-4244-11D9-97F6-000D9328C5BC@netsense.nl>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Johan Henselmans wrote:
> Hi, I am having a problem grouping a many to many relationship with 
> payments and receipts, where a payment can be for multiple receipts, and 
> a receipt can have multiple payments. I got a list of records that are 
> involved in such relations, but now I don't know how to group them so 
> that all payments and rececipts belonging to the same group are properly 
> grouped. Here's the list:
> 
> 
>  bankbookdetid | receiptid
> ---------------+-----------
>            147 |        25
>            157 |        25
>            157 |       622
> 
>            321 |       100
>            332 |       101
>            332 |       100
...

I think what's missing here is the explicit statement of which group 
these belong in. Without a value to sort/group by, there's nothing for 
your queries to "get a grip on".

So - add a "group_id" column to the bank-book and receipt tables. Create 
a sequence to generate group id's on demand.

Then you'll want a set of triggers that keeps the group details up to 
date. Of course, groups can shift as you add more records - particularly 
in the case of two groups merging when you add a "linking" row.

Maybe someone smarter than me can come up with a non-procedural 
solution. Personally, I've got a nagging feeling that this sort of 
"connectedness" problem is NP, so scaling could be a problem for you.

--
   Richard Huxton
   Archonet Ltd