Hi everyone,
Well I don´t have a question, but instead a proposition that I would
to discuss with people to find what are they doing and why.
For example I have developed and still developing a big and largely
used CRM platform. And since it´s primary version the system has
growth in a way to incorporate many, many, many functions that our
users have asked, but as we are exploring new ways for the next
version, a total ground-up release, I was thinking about how people,
and consequently systems, categorizes their "entities". Let me be
more clear: For example today we have generic contact, which must
have a entity type (person, industry or government), a gender (none,
male, female), an economic sector (primary, secondary, tertiary,
quaternary), and industry type (agriculture, robotics, real-estate,
etc), an occupation (lawyer, engineer, farmer, etc) and so on, we
have today almost 10 "types" also including statuses, and types
(internal, prospect, client, partner, supplier, etc)... Well you got
the picture. :)
So all those tables look something like this:
CREATE TABLE `contacts_types`
(
`contact_type_id` int(5) unsigned NOT NULL auto_increment,
`contact_type_name` char(40) NOT NULL default '',
`contact_type_sequence` int(5) unsigned NOT NULL default '0',
`contact_type_inserted_by` int(40) unsigned NOT NULL default '0',
`contact_type_inserted_at` datetime default NULL,
`contact_type_updated_by` int(40) unsigned NOT NULL default '0',
`contact_type_updated_at` datetime default NULL,
`contact_type_deleted_by` int(40) unsigned NOT NULL default '0',
`contact_type_deleted_at` datetime default NULL,
PRIMARY KEY (`contact_type_id`),
KEY `INDEX`
(`contact_type_inserted_by`,`contact_type_inserted_at`,`contact_type_upd
ated_by`,`contact_type_updated_at`,`contact_type_deleted_by`,`contact_ty
pe_deleted_at`)
)
ENGINE=MyISAM DEFAULT CHARSET=latin1
And course the many-to-many table:
CREATE TABLE `contacts_to_contacts_types `
(
`contact_id` int(40) unsigned NOT NULL auto_increment,
`contact_type_id` int(5) unsigned NOT NULL auto_increment,
KEY `INDEX` (`contact_id `,`contact_type_id `)
)
ENGINE=MyISAM DEFAULT CHARSET=latin1
As you can imagine, the amount of tables has growth out of control
(as we have a lot of entities as contacts, emails, addresses,
telephones, projects, tasks, interactions, etc... etc..etc), well not
quite but is getting there, as our clients more and more ask for one
more "categorization", but for each new "category" new need to create
another, to hold chronological statistics for it (Well, it´s a CRM
not an fancy address book) and also the business logic to insert
every day new data, just like this one:
CREATE TABLE `contacts_types_statistics`
(
`contact_type_id` int(5) unsigned NOT NULL default '0',
`contact_type_at` date default NULL,
`contact_type_count` int(5) unsigned NOT NULL default '0',
KEY `INDEX` (`contact_type_id`,`contact_type_at`)
)
ENGINE=MyISAM DEFAULT CHARSET=latin1
Course every business has it´s own needs, but most of then have
common needs, as then all interact with customers and suppliers.
Last night I was thinking about a sort of new approach for this:
Instead of having many tables to specific "categorizations", I would
have only one, maybe to, called labels and labels groups, based on
the idea of a paper on the desk in which you attach some stickers
(that yellow small papers where you can write "categories"). So you
would have labels groups, and the labels itself.. or you could have
only labels, with parents and children labels. And course another to
hold statistical data.
Anything that I´ve just said make any sense? :)
Is anybody who is a large scale CRM developer interested in
exchanging thoughts?
Best Regards to you all,
Bruno B B Magalhães
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
