On Mon, 14 Aug 2006 18:19:55 +0100, Thomas Mangin thomas.mangin@exa-networks.co.uk wrote:
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');
IMHO this is a better approch. The problem is, as Charles mentioned, any kind of data would go in a VARCHAR(255) field, making it dificult to sort by dates or so. I think that if a data type is a date, it should go in a date field, and the same for integer, string, etc.
--
Lic. Martín Marqués | SELECT 'mmarques' || Centro de Telemática | '@' || 'unl.edu.ar'; Universidad Nacional | DBA, Programador, del Litoral | Administrador