Thomas,

I like it - it captures my thoughts, with the enhancement of referential integrity to the field type (I would suggest a different name other than "contact_type" since we're not putting a type on the contact itself; perhaps contact_field_type or something similar). I wouldn't put the cascade delete on the type constraint, though (you don't want to lose the ability to enter phone numbers just because you deleted your last phone number... ). The contact_field_type contents could be localizable, but that would require some indirection within the database access code. If we want it to be REALLY normalized (4NF?), the (owner_id,contact_id) tuple could be separated from the field information via another key, but I wouldn't advocate that unless there's a significant performance problem.

Here's a slightly enhanced version:

create table contact (
    owner_id int not null,
    contact_id int not null,
    field_type_id int not null,
    field_property varchar(50) default null,
    field_value varchar(50) default null,
    primary key (owner_id,contact_id,field_type_id)
);


create table contact_field_type (
    type_id int not null,
    name varchar(20),
    primary key (type_id)
);

alter table contact add foreign key (type_id_fk) references contact_field_type (type_id);

insert into contact_type values (1,'email');
insert into contact_type values (2,'phone');

insert into contact (1,1,1,'home','him@home');
insert into contact (1,1,1,'work','him@work');
insert into contact (1,2,1,'home','her@home');
insert into contact (1,2,1,'work','her@work');

insert into contact (1,1,2,'home','+44 12744 000000')
insert into contact (1,1,2,'work','+44 12744 111111')
insert into contact (1,1,2,'fax' ,'+44 12744 222222')

On Mon, 14 Aug 2006 18:19:55 +0100, Thomas Mangin wrote:

Hello,

To make sure that everyone gets what Eric propose I put in in SQL (and
added a variation as it could be improved.

[ ALL SQL is just for demontration and may simply not work ]

create table contact (
    ownwer_id int not null,
    contact_id int not null,
    type_id int not null,
    key varchar(20) default null,
    value varchar(20) default null,
    primary key (owner_id,contact_id,type_id)
);


create table contact_type (
    type_id int not null,
    name varchar(20),
    primary key (type_id)
);

alter table contact add foreign key (type_id) references contact_type
(type_id) on update cascade on delete cascade;


insert into contact_type values (1,'email');
insert into contact_type values (1,'phone');

insert into contact (1,1,1,'home','him@home');
insert into contact (1,1,1,'work','him@work');
insert into contact (1,2,1,'home','her@home');
insert into contact (1,2,1,'work','her@work');

insert into contact (1,1,2,'home','+44 12744 000000')
insert into contact (1,1,2,'work','+44 12744 111111')
insert into contact (1,1,2,'fax' ,'+44 12744 222222')




you could as well do something like :

# information stored about a person

create table contact_info (
   contact_id int not null,
   contact_type int not null,
   /* more contact fields like before ??? */
   value text(255),
   primary key (contact_id,contact_type)
)

# Associate a the information with a roundcube user.

create table contact (
   contact_id serial not null,
   owner_id int not null,
   contact_name text not null,
   primary_key (contact_id)
)

# Type of information stored

create table types (
   type_id int not null,
   type_name text not null,
   primary key (type_id)
)

alter table contact_info add foreign key (contact_id) references contact
(contact_id) on delete cascade on update cascade
alter table contact_info add foreign key (contact_type) references types
(type_id) on update cascade on delete cascade

# Pseudo inserts to make sense of the tables.

insert into contact (owner_id, contact_name) values (CONTACT_DAVID,
'Thomas Mangin'); # creates contact_id CONTACT_THOMAS
insert into contact_info (contact_id, contact_type, value) values
(CONTACT_THOMAS, TYPE_TELNO, '+44 1274 000000');