From: Anuradha Ratnaweera <Aratnaweera@virtusa.com>
To: pgsql-general@postgresql.org
To: pgsql-sql@postgresql.org
Subject: Unique constraints for a list
Date: Fri, 29 Aug 2003 01:50:56 -0400
Message-ID: <20030829055056.GA13802@aratnaweera.virtusa.com> (raw)
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)
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: Aratnaweera@virtusa.com, pgsql-general@postgresql.org
Subject: Re: Unique constraints for a list
In-Reply-To: <20030829055056.GA13802@aratnaweera.virtusa.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