Hi all,
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 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
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 the value of this parameter
must belong only to this profile.
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,
Francesco
--
Francesco Formenti - TVBLOB S.r.l.
Software Engineer
Via G. Paisiello, 9 20131 Milano, Italia
-----------------------------------------
Phone +39 02 36562440
Fax +39 02 20408347
Web Site http://www.tvblob.com
E-mail [EMAIL PROTECTED]
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
