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
