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');