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.
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?
Thanks in advance for any kind of advice in this matter.
Regards,
Bruno B B Magalhaes
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
