Обсуждение: unique values of profile in the whole system

Hi all,
I have a problem to solve and I don't have a long experience with
database, please help me!!!
We are developing a system for profile management. The simplified
schema
is composed by three tables:
   * tbl_user : the users table; contains the unique id of the users     and the profile id (only one profile for each
user),and some     other information   * 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
allthe 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
 
We don't know exactly which are all data_types so we separate data and
data_type to keep profile flexible.

The problem involves the management of the values of the profile that
must be unique.

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.

The first solution:
Using access exclusive lock inside of the stored procedure mentioned
before. Since stored procedures make a local copy of the data, each
stored procedure accessing to the tbl_data for updating the unique_id
would have its own copy of data; so, a different locking strategy
should
not have the desired effect. However, I think that locks should be
avoided if possible. Furthermore, the unique_id should have a slow
update rate, so it should not be a big problem, but the exclusive lock
would affect the whole system, even the research (SELECT) on tbl_data.

The second solution:
Using a support table to take advantage of the UNIQUE constraint. I'd
have a fourth table, named "tbl_unique_id_support", storing the  the
unique_id(s) with the unique constraint. With this, if a new unique_id
is proposed, I should try to add it to the support table; if the
operation fails, the id already exists, so it cannot be added to the
tbl_data table. Otherwise, I can safely add it to the tbl_data. Pro:
get
rid of lock. Con: more memory is required for support table. The system

is less flexible, because for each "unique_id"-kind of data, I should
have a support table dedicated.

Please, do you have any suggestion about that?

Thank you
regards,
Debora

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.