Re: grouping a many to many relation set

Richard Huxton wrote:
> 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
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
>               http://www.postgresql.org/docs/faqs/FAQ.html
> 
Thanks for the reply. Adding a group_id column would defeat the whole 
purpose of the relational model. I do not want to add a grouping 
beforehand. The grouping should take place according to certain 
criteria, in this case: group all the records that have at least one of 
two attributes in common. I am surprised that I haven't found any 
reference to such a n:m grouping, while googling. All I found was a 
description of the problem on can get

Johan.