Ticket #1332930 (Feature Requests)

Thomas Mangin thomas.mangin at exa-networks.co.uk
Mon Aug 14 19:19:55 CEST 2006


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 at home');
insert into contact (1,1,1,'work','him at work');
insert into contact (1,2,1,'home','her at home');
insert into contact (1,2,1,'work','her at 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');




More information about the Dev mailing list