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