Bruno--
I am not an expert, but...
I would think most contact systems probably have separate tables for
organizations and individuals. And usually additional tables to allow
multiple addresses and telephones to be linked to the organizations and
individuals.
Yours seems rather novel in having a single central "entity" table that
combines all those.
I think that's what's causing your problem: each different type of
entity has its own set of attributes, which would ordinarily be columns
in that entity-type's own table. But you are applying attributes to
entities largely by linking 'type' records to them, hence you are adding
tables rather than columns.
I would suggest one of two approaches:
1. Have separate tables for organizations and individuals. You then must
track three or four types of relationships:
organization-to-organization
individual-to-individual
organization-to-individual
(individual-to-organization)
or
2. Keep your single entity table (with rows for both organizations and
individuals), add tables for organizations and individuals, and link
each entity row to a row in either the organizations or individuals
table. All your relationships can then be between rows in the entity table.
I'd better stop there. What do you think?
--John
Bruno B B Magalhães wrote:
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_updated_by`,`contact_type_updated_at`,`contact_type_deleted_by`,`contact_type_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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
