Date: Mon, 3 Jul 2006 11:06:43 -0500
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
Message-ID: <20060703160643.GA29299@wolff.to>
Mail-Followup-To: Bruno Wolff III <bruno@wolff.to>, deb.vanni@tiscali.it,
	pgsql-sql@postgresql.org
References: <1151510191.273338.208060@y41g2000cwy.googlegroups.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <1151510191.273338.208060@y41g2000cwy.googlegroups.com>
User-Agent: Mutt/1.4.2.1i

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.