Re: grouping a many to many relation set

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