[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: Tracking Customers



Joshua D. Drake wrote:
Hello,

One of the things we have decided to try and do for (likley) 1.3 is to
have some basic customer relationship management. I have been thinking
alot on this and have come up with some rough ideas on a model.

company:
----------------+--------------------------+--------------------------------------------------------------
 id             | integer                  | not null default
nextval('company_id_seq'::regclass)
 name           | character varying(150)   | not null
 type_id        | integer                  | not null
 status_id      | integer                  | not null
 created        | timestamp with time zone | not null
 referred_by_id | integer                  |
nextval('company_initial_id_seq'::regclass)
 revision       | integer                  | not null
 tgrm_index     | tsvector                 |


id is just an easy way to manipulate the record

name is the primary key

type_id refers to a company_type column such as vendor,customer,lead,etc...

status_id refers to a company_status table, active, inactive, dead

referred_by_id refers to another company or person from which the
company was referred by (thus relationship)

created date the record was created

revision fairly optional but used if we want to track all changes to the
record

tgrm_index used for fast partial word searching instead of ~ or ILIKE

company_alias
-----------------------+--------------------------+--------------------------------------------------------------------
 id                    | integer                  | not null default
nextval('company_alias_id_seq'::regclass)
 company_id            | integer                  | not null
 company_alias         | text                     | not null
 description           | text                     | not null
 created               | timestamp with time zone | not null
 revision              | integer                  | not null

company_id refers to the company record

company_alias is an alias for the company. This comes from my own
experience where a customer will call and have three different names. I
know them as foo.com, accounting knows them as Acme Corp. That kind of
thing.

description is a short note about the company


I have a ton more but thought I would throw this out there including
people/contacts, addresses etc..

Joshua D. Drake


Would it be worth considering how SugarCRM or vtiger deal with relationships as these seem to be the more popular of the OSS CRMS and there have been numerous requests for integration tips over the years of SL.

Just a thought :)

W