Re: [SQL] Unique constraints for a list

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