Re: unique attributes in profile management system

Tom Lane wrote:

>"Francesco Formenti - TVBLOB S.r.l." <francesco(dot)formenti(at)tvblob(dot)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(dot)formenti(at)tvblob(dot)com