Re: unique values of profile in the whole system

On Wed, Jun 28, 2006 at 08:56:31 -0700,
deb(dot)vanni(at)tiscali(dot)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.