From: Bruno Wolff III <bruno@wolff.to>
To: deb.vanni@tiscali.it
Cc: pgsql-sql@postgresql.org
Subject: Re: unique values of profile in the whole system
Date: Mon, 3 Jul 2006 11:06:43 -0500
Message-ID: <20060703160643.GA29299@wolff.to> (raw)
In-Reply-To: <1151510191.273338.208060@y41g2000cwy.googlegroups.com>
References: <1151510191.273338.208060@y41g2000cwy.googlegroups.com>
On Wed, Jun 28, 2006 at 08:56:31 -0700,
deb.vanni@tiscali.it wrote:
> * tbl_data_type : contains the data type of the profile, their id
> and their names. E.g.: id=1, data type name="last name"; id=2,
> data type name="address", and so on
> * tbl_data : the data of all the profiles of the system; it has
> three columns: the id of the profile the data belongs to (linked
> to the tbl_user), the data type id (linked to tbl_data_type) and
> the value of the data. E.g.: profile=1, data_type_1=1,
> value="Smith", and so on
> Suppose we have a data type named "unique_id", which value should be
> stored in tbl_data. The value must be unique in the whole system, so
> the
> profiles store only one "unique_id", and I have to able to identify a
> profile by this value(that's why must be unique!).
> Generating such a unique id it's not a problem, using e.g. a sequence.
> The problem is the user can change this value accessing to the proper
> stored procedure, and the system should check that the value chosen do
> not violate the requirement of uniqueness.
>
> I have only two solutions, I'd be glad to hear from you if they are
> correct, or if you have already encountered similar problems and you
> can
> point me to some useful document.
You could add a flag to tbl_data that indicates whether or not the data should
be unique. Then you can create a partial index where this flag is true
over the combination of type id and value.
For referential integrity of the flag, you can add the same flag to the
tbl_data_type table and make a unique index over the id type and the flag
and then make the reference from tbl_data to tbl_data_type use the type id
and flag as a foreign key instead of just type id.
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: bruno@wolff.to, deb.vanni@tiscali.it
Subject: Re: unique values of profile in the whole system
In-Reply-To: <20060703160643.GA29299@wolff.to>
* 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