Answers intermingled below.... --- Bruno B B Magalh�es <[EMAIL PROTECTED]> wrote:
> Hi guys I need some help with two things... > > I have the following table: > > CREATE TABLE `telephones` ( > `contact_id` int(20) unsigned NOT NULL default '0', > `telephone_id` int(20) unsigned NOT NULL default '0', > `telephone_country_code` char(5) NOT NULL default '', > `telephone_area_code` char(5) NOT NULL default '', > `telephone_number` char(20) NOT NULL default '', > `telephone_extension` char(5) NOT NULL default '', > `telephone_primary` int(1) unsigned NOT NULL default '0', > `telephone_type_id` int(1) unsigned NOT NULL default '0', > `telephone_inserted` datetime NOT NULL default '0000-00-00 > 00:00:00', > `telephone_updated` datetime NOT NULL default '0000-00-00 > 00:00:00', > `telephone_deleted` datetime NOT NULL default '0000-00-00 > 00:00:00' > ) ENGINE=MyISAM DEFAULT CHARSET=latin1 > > As you can see I have a column called telephone_deleted, and I was > thinking about instead of deleting a record I would change the > telephone_delete with a valid date time when it happened. With that I > > think it would avoid loosing records as a mistake, as it would just > need to reset the delete date and time. But my question is it > scalable? Any other ideas, as I am using "IS NOT NULL" to find the > records that haven't been deleted. > What do you mean by "scalable"? If you mean "can I use an index to locate records that have or have not been deleted" the answer would be yes. > Another thing is how can I build a statistical analisys of > telephones, for example xx% belongs to country_code X and another xx% > > belongs to country_code Y, but here's the trick part: I would like it > > fetch it in a date range, for example what was the evolution between > > date X and date Y... I have this working now with a cronjob > performing a logging operation in a table like this which stores all > > statistics regarding every entity in the system: > > CREATE TABLE `flx_contacts_stats` ( > `stat_date` date NOT NULL default '0000-00-00', > `stat_entity` char(64) NOT NULL default '', > `stat_key` char(128) NOT NULL default '0', > `stat_value` int(10) unsigned NOT NULL default '0', > KEY `stat_date` (`stat_date`,`stat_entity`) > ) ENGINE=MyISAM DEFAULT CHARSET=latin1 > > For example with this kind of data: > 2005-12-04; phone_countrycodes; 55; 63 > 2005-12-04; phone_areacodes; 473, 32; 1 > 2005-12-04; phone_areacodes; 53, 32; 1 > 2005-12-04; phone_areacodes; 54, 32; 1 > 2005-12-04; phone_areacodes; 11, 55; 1 > 2005-12-04; phone_areacodes; 21, 55; 62 > > How can I do this on the fly without using any generic table to store > > stats? I suspect that storing stats this way is not practical in > terms of portability and that's not definitely a good practice. Or > this kind of data is necessarily stored separated? > If the data in the reports is truly static, then you GAIN performance by only making your server compute it once. Storing the results of statistical analysis is a commonly used practice when it comes to data warehousing and OLAP system design. In your case, it especially makes sense from a performance standpoint to query the smaller `flx_contact_stats` table rather than to recompute those values every time you need them. > > Thanks in advance for any kind of advice in this matter. > > Regards, > Bruno B B Magalhaes > Best Wishes! Shawn Green Database Administrator Unimin Corporation - Spruce Pine __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
