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

Re: Tracking Customers

> 
>> 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
> 
> Hmmm.....  Wonder if this is safe.  Suppose I have retail customers.
> Two share the same name.  How would this work?  Would it not be better
> (and less redundant) to use id as the primary key since that is what
> we use it as anyway?

Hmm... yeah id is a problem too though. This reminds me of the
discussion I had just last week in a class I was teaching. How do you
define a natural primary key on a table that is just names... ;)



>> type_id refers to a company_type column such as vendor,customer,lead,etc...
> 
> Ouch.  This is going to break one of the things I was hoping to
> accomplish (i.e. being able to set up a supplier as a customer without
> multiplying records, which would become impossible if the name was the
> primary key).  Better to use a pivot table.

Well if we remove the name as the primary key this would work fine.

> 
> One might also add a GIFI field so that income or expense (as
> appropriate for the type) might be automatically posted with a certain
> GIFI attached (I know some locales require something like this).

Color me stupid, what is GIFI?

>> status_id refers to a company_status table, active, inactive, dead
> 
> Would it be better to have a start and end date so that one can
> essentially say "no more invoices after today" while still
> back-entering old invoices?

I don't think they have to do with each other. This is more about
sorting than anything else. I want to see people that I consider inactive...

>> referred_by_id refers to another company or person from which the
>> company was referred by (thus relationship)
> 
> Good idea.  All for that.
>> created date the record was created
>>
>> revision fairly optional but used if we want to track all changes to the
>> record
> 
> I would suggest letting revision control go for now unless there are a
> number of people who want/need it.  Revision control on databases
> isn't that hard to bolt on with triggers and archive tables.
>> tgrm_index used for fast partial word searching instead of ~ or ILIKE
>>

Yeah that is true.

> I would add a few more fields:
> 
> Tax ID (FEIN or SSN for those in the US), something of the SIC type or
> an equivalent.

To me this is secondary information. We can certainly store it and in
fact.... woot, good job. We can have:

company:
----------------+--------------------------+--------------------------------------------------------------
 id             | integer                  | not null default
nextval('company_id_seq'::regclass)
 name           | character varying(150)   | not null
 tax_id         | character varying(10)    | not null
 tax_id_type    | smallint		   | not null
 main_phone
 main_fax
 main_url
 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)
 tgrm_index     | tsvector                 |

primary key (name,tax_id)
tax_id_type will be a lookup table to ssn/ein or other (for non US)

> 
> As I think about it, it might be useful to have a phone field too for
> main corporate numbers.  But this could be added as a custom field.

Actually I thought about that, that is how CMD is now. We have a main
phone and main fax in the main record. Added above, plus main url. Is
primary better?

> 
> comments field (text)

Umpf, I don't like this one. This belongs in a comments table that links
to the record so we can have unlimited searchable comments.


> 
>> company_alias
> 
> I like this idea.  I would add a text comments field.

See comment above about comments.


> 
> Here are a few more possibities (note that custom fields can always be
> added, so I figure I am just stating the basic ones).

Alot of the below really belong in their own tables.. addresses table
for example, that has an address_type of primary, AP, shipping etc...

Joshua D. Drake


> 
> id (primary key)   SERIAL
> surname VARCHAR(16)
> given_name VARCHAR(16)
> address1 VARCHAR(32)
> address2 VARCHAR(32)
> city VARCHAR(16)
> state VARCHAR(16)
> zipcode VARCHAR (16)
> type_id INT references contact_type_id (i.e. billing, support,
> customer_contact, shipping, returns, etc).
> BIC text
> IBAN text
> start_date
> end_date
> email
> telephone
> email
> backup_contact_id INT references contacts (id) (i.e. who to contact
> when this individual is unavailable).
> comments (text)
> 
> I don't think I am up to do much regarding appointments or generic
> TODO items.  The former has a number of fun corner cases which are
> difficult to solve (and we have to determine what is desirable
> behavior which may vary from customer to customer), and the latter is
> going to take some time to hash out the exact requirements.  There is
> also some question that I am sure will be discussed whether these
> should be core or add-on modules.
> 
> Hope this helps.
> Chris Travers
> 
> -------------------------------------------------------------------------
> Take Surveys. Earn Cash. Influence the Future of IT
> Join SourceForge.net's Techsay panel and you'll get the chance to share your
> opinions on IT & business topics through brief surveys - and earn cash
> http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
> _______________________________________________
> Ledger-smb-devel mailing list
> ..hidden..
> https://lists.sourceforge.net/lists/listinfo/ledger-smb-devel
> 


-- 

      === 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/