Re: unique attributes in profile management system

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(dot)formenti(at)tvblob(dot)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(at)fetter(dot)org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!