Re: grouping a many to many relation set

On 2-dec-04, at 3:45, Michael Fuhr wrote:

> On Wed, Dec 01, 2004 at 06:57:54AM +0100, Johan Henselmans wrote:
>> Richard Huxton wrote:
>>
>>> 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.
>>
>> 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.
>
> How is an application going to know which records belong to which
> groups without a group ID? Or is a group ID acceptable as long as
> it's not part of the data, but rather generated by the query or
> function that does the grouping?
>
>> 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.
>
> What about chains like this:
>
> bankbookdetid | receiptid
> ---------------+-----------
> 100 | 1
> 100 | 2
> 101 | 2
> 101 | 3
> 102 | 3
> 102 | 4
>
> Should 1 be grouped with 2, 3, and 4 since 1 has an attribute in
> common with 2, 2 has an attribute in common with 3, and 3 has an
> attribute in common with 4? Or doesn't your model permit this
> situation?
>
> --
> Michael Fuhr
> http://www.fuhr.org/~mfuhr/
>

That is possible indeed. This was the original situation. The trick I
use is that from that collection I select the records of which both
attribues have a count > 1. But that leads to the situation that some
attributes still appear several times in the resulting set. In the
above case the resulting set would be

100 | 2
101 | 2
101 | 3
102 | 3

So the group would end up to be all the records that have 2 and 3 or
100, 101 and 102 as part.

In the mean time, breaking my head about it, I think I have found a
solution: from the resuling set I once again select the record
attributes that have a common attribute count > 1 (In your example that
would be bankbookdetid = 101, and receiptid 2 and 3).

Then we reduce the resulting set to:
101 | 2
101 | 3

Now I can add a group id , which is the attribute of which the count is
> 1 (in this case 101), to make sure we don't mix up groupid because
bankbookdetid and receiptid might have the same number, so we add
3000000 to receiptid groups, and 400000000 to bankbookdetid groups.

That is the final set, from where one could go back to the previous set
(get all the records that have 101 or 2 and 3 in their respective
attributes),
that would result in

100 | 2 | 400000101
101 | 2 | 400000101
101 | 3 | 400000101
102 | 3 | 400000101

and from there one would go back to ask all the records that have a
part in these records, so you could go back to

---------------+-----------
100 | 1 | 400000101
100 | 2 | 400000101
101 | 2 | 400000101
101 | 3 | 400000101
102 | 3 | 400000101
102 | 4 | 400000101

Now it is possible to group.

What do you think of the idea?

-johan