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

Re: Tracking Customers

Hi;

On 3/13/07, Joshua D. Drake <..hidden..> wrote:


>
>> 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... ;)



Understood.  Unfortunately, since we add contacts, this becomes
difficult.  For example, imagine that there are two companies called
"Joe's Consulting" and one is located in Alabama and the other is
located in New Hampshire.  Ideally, one would have a primary key being
something like the name, the address, the city,  and the state.
Unfortunately the companies table only has one of these in it.

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.



>> 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).

I believe I have another solution to this too:  One could move the
type_id to the contact table.  This way you could have separate
contact records for the separate aspects.  I am still mulling this
over though.  I am wondering if it isn't a good idea to have AR and AP
account info on the company table.


>
> 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?


General Index of Financial Information.   Basically it is an overlay
on the chart of accounts, allowing you to essentially post data into
two different accounting structures simultaneously.  Canada invented
it....

Think of GIFI as being like a "tax account category."  But you can use
it for custom reports as well.

Also SQL-Ledger 2.7 (according to their feature site) gives account
links for income, expense, ar, and ap to the customers and vendors.  I
am not sure if twe want this or not.


>> 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...


Ok, maybe both fields might be helpful.



>> 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 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.

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.
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....

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.


> 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)


The only issue we will run into here is that few of us (if any)
collect tax id's from all our customers and suppliers.


>
> 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?


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 :-).



>
> 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.


>
> 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...


Do we want people/names to be associated with addresses?  I have no
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.

Best Wishes,
Chris Travers