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 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 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 ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend