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