Re: grouping a many to many relation set - Mailing list pgsql-sql

From Richard Huxton
Subject Re: grouping a many to many relation set
Date
Msg-id 41AC3C7E.10201@archonet.com
Whole thread Raw
In response to grouping a many to many relation set  (Johan Henselmans <johan@netsense.nl>)
Responses Re: grouping a many to many relation set
List pgsql-sql
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


pgsql-sql by date:

Previous
From: Andreas Kretschmer
Date:
Subject: Re: [despammed] session-wide autocommit off
Next
From: Richard Huxton
Date:
Subject: Re: session-wide autocommit off