Обсуждение: unique attributes in profile management system
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 francesco.formenti@tvblob.com
"Francesco Formenti - TVBLOB S.r.l." <francesco.formenti@tvblob.com> writes:
> 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
I think you need to refactor your schema. You want to have one table
that is clearly the "defining" table for profiles, and then put a unique
constraint on that table's ID column, and probably foreign key
constraints on other tables that mention profile IDs.
It's not real clear to me why you're bothering with a separation between
tbl_data_type and tbl_data, either ...
regards, tom lane
Tom Lane wrote: >"Francesco Formenti - TVBLOB S.r.l." <francesco.formenti@tvblob.com> writes: > > >>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 >> >> > >I think you need to refactor your schema. You want to have one table >that is clearly the "defining" table for profiles, and then put a unique >constraint on that table's ID column, and probably foreign key >constraints on other tables that mention profile IDs. > >It's not real clear to me why you're bothering with a separation between >tbl_data_type and tbl_data, either ... > > regards, tom lane > > > > Hi Tom, thank you for your response. Unfortunately, I'm not sure to understand properly your advice. In the complete schema, we already have constraints on columns, but those constraints do not help us solving the problem. In fact, the (interesting part of the) complete schema is something like: CREATE TABLE public.tbl_user ( guid integer PRIMARY KEY, username varchar(25) UNIQUE NOT NULL, password varchar(25), status integer NOT NULL REFERENCES tbl_user_status (status_id) ON DELETE RESTRICT, timestamp_create timestamptz DEFAULT now(), timestamp_update timestamptz DEFAULT now() ) WITHOUT OIDS; -- *** tbl_profile *** CREATE TABLE public.tbl_profile ( profile_id int4 PRIMARY KEY, guid int4 UNIQUE NOT NULL REFERENCES tbl_user (guid) ON DELETE RESTRICT, timestamp_create timestamp with time zone NOT NULL DEFAULT now() ) WITHOUT OIDS; -- *** tbl_data_type *** CREATE TABLE public.tbl_data_type ( data_type_id int4 PRIMARY KEY, xml_name varchar(50) UNIQUE NOT NULL, ord int4 NOT NULL DEFAULT 0 ) WITHOUT OIDS; -- *** tbl_data *** CREATE TABLE public.tbl_data ( data_id serial PRIMARY KEY, profile_id int4 NOT NULL REFERENCES tbl_profile ON DELETE RESTRICT, data_type_id int4 NOT NULL REFERENCES tbl_data_type ON DELETE RESTRICT, value varchar(300), timestamp_create timestamp with time zone NOT NULL DEFAULT now(), timestamp_update timestamp with time zone DEFAULT now() ) WITHOUT OIDS; I can put any kind of value in tbl_data (names, birthdates, addresses, ...). The problem arises when the data must be unique, referring to its data_type: how can I atomically check that the SELECT * FROM put_new_data_into_tbl_data(my_profile_id, data_type_id_that_needs_uniqueness, my_new_value_that_must_be_unique) is really putting a unique value for the specified data_type_id? About all of the defined data_type_id refer to non-unique values; however, I have a certain data_type_id (e.g., having an id of "10") that wants all its related values being unique; so, if I select all value(s) from tbl_data where data_type_id=10, all those values should satisfy the unique condition (all those values should be different). The problem is I don't know how to atomically check this condition, when I make an insertion like this one in the tbl_data. We separated tbl_data from tbl_data_type because we can easily check if the data_type is allowed or not. We use something like a 2xN matrix to insert new data into the profiles, where the first row of the matrix contains the names of the data_types, where the second row stores the values associated. This helps us to keep the profiles flexible, containing (if needed) only a part of the whole data_type allowed. 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 francesco.formenti@tvblob.com
On Mon, Jun 26, 2006 at 04:03:11PM +0200, Francesco Formenti - TVBLOB S.r.l. wrote: > Tom Lane wrote: > > >"Francesco Formenti - TVBLOB S.r.l." <francesco.formenti@tvblob.com> > >writes: > > > >>we are developing a system for profile management. The simplified schema > >>is composed by three tables: You need more tables, and to do this, you need to summon courage when deciding in advance what you'll store and what you won't. It's OK, though. It's not much courage, and you'll feel better having made a bold move. :) > >> * 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 This is the OTLT mistake, described in some detail here: <http://www.dbazine.com/ofinterest/oi-articles/celko22>. It has a name. You need to fix it :) Cheers, D -- David Fetter <david@fetter.org> http://fetter.org/ phone: +1 415 235 3778 AIM: dfetter666 Skype: davidfetter Remember to vote!