You're structure has redundant info in it. When I say redundancy, I saying contacts.contacts_id should just be contacts.id (in table-column writing).
Also, you should use standard data types, not like using tinyint. Boolean should be used there. OK, just checked that MySQL STILL doesn't have a boolean data type. Is a 8bit data type the best way to deal with this?
Let's also add the INSERTs to the contact_info_type table (those are application specific data).
On Fri, 11 Aug 2006, Michel Moreira wrote:
Create table contacts ( contact_id Int NOT NULL, changed Datetime, del Tinyint, name Varchar(128), email Varchar(128), firstname Varchar(128), user_id Int, Primary Key (contact_id)) ENGINE = MyISAM;
Create table contact_info_type ( contact_info_type_id Int NOT NULL, display_label Varchar(128), Primary Key (contact_info_type_id)) ENGINE = MyISAM;
Create table contact_info ( contact_id Int NOT NULL, contact_info_type_id Int NOT NULL, value Varchar(128), contact_info_id Int NOT NULL, Primary Key (contact_info_id)) ENGINE = MyISAM;
Alter table contact_info add Foreign Key (contact_id) references contacts (contact_id) on delete restrict on update restrict; Alter table contact_info add Foreign Key (contact_info_type_id) references contact_info_type (contact_info_type_id) on delete restrict on update restrict;
/* I think that the contact_info_type can hold the type of contact info, like organization, telephones, mother name :D and the contact_info has the ones that belongs to the contact. */
You mean, contact_info has the contact information. That looks good.
-- 21:50:04 up 2 days, 9:07, 0 users, load average: 0.92, 0.37, 0.18
Lic. Martín Marqués | SELECT 'mmarques' || Centro de Telemática | '@' || 'unl.edu.ar'; Universidad Nacional | DBA, Programador, del Litoral | Administrador