From: Richard Huxton <dev@archonet.com>
To: Anuradha Ratnaweera <Aratnaweera@virtusa.com>
To: pgsql-general@postgresql.org
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] Unique constraints for a list
Date: Fri, 29 Aug 2003 09:52:33 +0100
Message-ID: <200308290952.33833.dev@archonet.com> (raw)
In-Reply-To: <20030829055056.GA13802@aratnaweera.virtusa.com>
References: <20030829055056.GA13802@aratnaweera.virtusa.com>
On Friday 29 August 2003 06:50, Anuradha Ratnaweera wrote:
[snipped description of groups consisting of unique lists of members, i.e.
group A contains (1,2,3) so B can't contain the same]
I was looking at something almost identical a few days ago.
> Then we added a unique string field to t3 which is a comma seperated and
> sorted list of group members. But when this is done, the DB is no
> longer normalized.
I looked at this, but I was planning to use this "member_key" as an addition
to the two tables and just keep it up to date with triggers. In your case add
the comma-separated-list to t1 but keep t2. This seemed to work OK speed-wise
and was no less normalised than having a "group_size" field in t1 that
triggers kept up to date.
> Are there any other approaches we can take? Or are we missing something
> obvious here?
The other approach I looked at was to build a new group in a test table, join
its members to the real table and compare the number of matches to the size
of both groups (for this a pre-calculated size came in useful).
If the number of matches equalled the size of both our new list and an
existing list then they were duplicates. The query wasn't as bad as I first
feared, but it definitely helped to build it up one step at a time.
Testing showed that the second method was too slow for my particular need
(bursty updates) but it might work 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, Aratnaweera@virtusa.com, pgsql-general@postgresql.org
Subject: Re: [SQL] Unique constraints for a list
In-Reply-To: <200308290952.33833.dev@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