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','[EMAIL PROTECTED]');
insert into contact (1,1,1,'work','[EMAIL PROTECTED]');
insert into contact (1,2,1,'home','[EMAIL PROTECTED]');
insert into contact (1,2,1,'work','[EMAIL PROTECTED]');
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');
