Ticket #1332930 (Feature Requests)

Michel Moreira drungrin at gmail.com
Fri Aug 11 20:28:06 CEST 2006


My idea of the db structure. Sorry about the other message.

2006/8/11, Martin Marques <martin at bugs.unl.edu.ar>:
> On Thu, 10 Aug 2006, Michel Moreira wrote:
>
> > I dont think that saving the all the field data in one vcard string is
> > a good idea.
> >
> > How about searching my contacts that live on some city, some info that
> > is in this vcard string?
> >
> > It isnt good to add a field to each contact info that is available...
> >
> > I think that can be created 2 tables, one with the contact info type
> > and one that has an 1-n relationship between the contacts table and
> > tis contact info type table, where u can put the info.
> >
> > Then u can search freely withouth performance issues and store and
> > extends all the contact info without having too many empty fields.
>
> This is a good idea. can we through some ideas on DB structure?
>
>
> --
>  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
> ---------------------------------------------------------
>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: erd.jpg
Type: image/jpeg
Size: 35323 bytes
Desc: not available
URL: <http://lists.roundcube.net/pipermail/dev/attachments/20060811/1222e250/attachment.jpg>
-------------- next part --------------
/*
Created		11/8/2006
Modified	11/8/2006
Project		RounCube
Model		Contacts
Company		
Author		Michel Moreira <drungrin at gmail.com>
Version		
Database	mySQL 5 
*/


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;








More information about the Dev mailing list