Re: unique attributes in profile management system

"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