Message-ID: <20030829055056.GA13802@aratnaweera.virtusa.com>
From: Anuradha Ratnaweera <Aratnaweera@virtusa.com>
To: pgsql-general@postgresql.org, pgsql-sql@postgresql.org
Subject: Unique constraints for a list
Date: Fri, 29 Aug 2003 01:50:56 -0400
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"


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)