Thread: Unique constraints for a list

Hi all,

o We have a table t1 which has a primary key id and other fields.
  (say, 10 rows with ids 1 to 10).

o We need to group rows (unordered, and arbritrary size) in t1 and
  assign a unique id to each group.  (e.g.: {1, 3, 4} is group 1, {1, 5}
  is group 2 etc.)

o A group shouldn't change its members.  (Group 1 above can't later be
  changed to {1, 3} or {1, 2, 3, 4}.)

o There can't be duplicate groups.  (We can't have a group 9 with
  members {1, 5} because group 2 already has this.

Of course, all these rules can be implemented in the application rather
than database.  But we prefer to move as much as constraints to the
database level.  (e.g. group members are preferably foreign keys
refering to t1(id).)

We have tried the most trivial approach to come up with a table t2,
which has gid, mid (group id and member id).  This allows us to use
foreign keys but uniqueness needs to be looked after at the application.

Then we thought of having a table t3 which has a gid (primary key) and
making gid and mid of t2 foreign keys of t3(gid) and t1(id)
respectively.  This partly solved some of the issues, but not
completely.

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.

Are there any other approaches we can take?  Or are we missing something
obvious here?

Thanks in advance.

    Anuradha

--

Debian GNU/Linux (kernel 2.6.0-test3)

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