Re: unique attributes in profile management system

"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