Tracking Customers

["Joshua D. Drake" <..hidden..>]

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













-- 

      === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
             http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/