From: Richard Huxton <dev@archonet.com>
To: Johan Henselmans <johan@netsense.nl>
Cc: pgsql-sql@postgresql.org
Subject: Re: grouping a many to many relation set
Date: Tue, 30 Nov 2004 09:25:18 +0000
Message-ID: <41AC3C7E.10201@archonet.com> (raw)
In-Reply-To: <EA79F36B-4244-11D9-97F6-000D9328C5BC@netsense.nl>
References: <EA79F36B-4244-11D9-97F6-000D9328C5BC@netsense.nl>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, johan@netsense.nl
Subject: Re: grouping a many to many relation set
In-Reply-To: <41AC3C7E.10201@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox