Re: Tracking Customers

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

> The tax id would seem to be the natural primary key, but I don't know
> if it is legal to require that all customers and vendors provide it.
> And it probably is not practical either.  So we are left with "there
> is a natural primary key but we may not know what it is."  Which is
> why I fall back to id.

I think I like the idea that we came up with on irc of:

name,address_id

>>
>>>> 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.
> 
> Do we want separate company records for AR vs AP?  Doesn't that lead
> us back around to the idea that we currently need multiple records for
> this (and this is an annoyance at least to me who does have customers
> among my suppliers).

Well the type_id is more of a primary_type_id versus an only_type_id.
Think categorization or filtering.. But we could push into a multi table
and have some logic that allows things like customer/vendor but not
customer/lead.

> I had another thought too.  I wonder whether we want to start
> requiring tsearch as a prerequisite of our application at this time or
> not.  I agree it is useful, but we are either going to want to go for
> it all out or not at all.

This is something that I think should just be required, tsearch plus
pg_tgrm. It comes with stock postgresql and if you are using a package,
it is usually called postgresql-contrib.

> 
> On the positive side, it may make searching much easier and more powerful.
> 
> On the negative side, it may make installation more difficult.
> 
> I guess the questions which come to mind are:
> 1)  How important is searching these likely to be at the moment?  I
> agree it is potentially very useful.  Especially for businesses like
> my own.

Once we add crm, it becomes vital.

> 2)  How easily can it be bolted on?  With the current ORM, I don't
> think that adding tsearch later would be hard.  Essentially you can
> define a stored procedure like:

> contact_full_search(in_search) returns setof contacts....
> 

Exactly.

> And this will be automatically picked up in the ORM for the contact
> class as a full_search method using $self->{search} as its only
> argument.
> 
> Right now, I am leaning toward requiring tsearch, but I am not yet
> altogether comfortable with adding too many dependencies without more
> feedback about this.

Normally I would agree, but this isn't really an external dependency
(like some of the perl ones).

> I would probably just leave it as "phone" and "fax" because these are
> the inbound lines for the companies while the contacts can all have
> their own phone and fax numbers as well.  But I am open to suggestions
> too :-).

sure

> 
>>> 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.
> 
> Fair enough.
>>
>>>> company_alias
>>> I like this idea.  I would add a text comments field.
>> See comment above about comments.
>>
> Ok.  Fair enough.

> Do we want people/names to be associated with addresses?  I have no

The option to, yes.

> problem with breaking off addresses, names, phone numbers, and emails
> into separate tables, but in many, most, or possibly all of these
> cases, there may be a single named contact behind it.  For example, it
> is entirely possible  for one individual at a company to have three
> telephone numbers, two mailing addresses, three email addresses, and
> so forth.  So I suppose we should probably break this into contacts,
> addresses, phone_numbers, addresses, email_addresses.
> 
> I suppose that once we go to an AJAX-based interface, we could
> accomodate that case fairly easily.  However in the mean time, it is
> going to be difficult to balance data flexibility with workflow.  So
> in order to avoid data management issues, I am going to suggest that
> for the time being, we enforce a 1:1 relationship between contacts,
> addresses, phone_numbers, addresses, and email_addresses.  This should
> cut down on bugs until we get to the point where we can remove this
> (albeit arbitrary) restriction without interfering too much with
> workflow.

That seems a bit of a bear. I would prefer to have a primary address
which displays per the workflow issue. The other addresses you can grab
if you need.

J



> 
> Best Wishes,
> 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/