From: Richard Huxton <dev@archonet.com>
To: Anuradha Ratnaweera <Aratnaweera@virtusa.com>,
	pgsql-general@postgresql.org, pgsql-sql@postgresql.org
Subject: Re: [SQL] Unique constraints for a list
Date: Fri, 29 Aug 2003 09:52:33 +0100
User-Agent: KMail/1.5
References: <20030829055056.GA13802@aratnaweera.virtusa.com>
In-Reply-To: <20030829055056.GA13802@aratnaweera.virtusa.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200308290952.33833.dev@archonet.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