From: Johan Henselmans <johan@netsense.nl>
To: pgsql-sql@postgresql.org
Subject: Re: grouping a many to many relation set
Date: Wed, 01 Dec 2004 06:57:54 +0100
Message-ID: <cojmh2$2iht$1@news.hub.org> (raw)
In-Reply-To: <41AC3C7E.10201@archonet.com>
References: <EA79F36B-4244-11D9-97F6-000D9328C5BC@netsense.nl>
<41AC3C7E.10201@archonet.com>
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.
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: johan@netsense.nl
Subject: Re: grouping a many to many relation set
In-Reply-To: <cojmh2$2iht$1@news.hub.org>
* 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