Re: For those interested in the CRM/Entity management

[Charley Tiggs <..hidden..>]

Ed W wrote:
> Joshua D. Drake wrote:
> > Here is the current model:
> > http://ledger-smb.svn.sourceforge.net/viewvc/ledger-smb/trunk/sql/Pg-database.sql?view=markup&pathrev=1244
> >
> >   
>
> Just shooting from the hip here:
>
> - Would be useful to be able to "expire" a contact completely so that 
> they are no longer active (or do we expire them per class?)

Seconded.

> - Duplicate company names are quite common, ditto for people names

Very common among my clients.

> - Normalisation is often good, but it's also painful sometimes.  I need 
> to cut and paste addresses in and out of the system and right now it 
> needs loads of work to paste into each field.  Not everyone will need 
> addresses and names so normalised (I have no better suggestion as to 
> what to do though..)
> - Multiple email addresses and tel numbers are possible.  However, also 
> useful to mark one or more as "primary"

Seconded.

> - Custom field information, especially to allow integration with other 
> systems.  Eg my customers get an account code from us on another system, 
> we also track the SIM codes for various purchases and it's useful to 
> have that info easy to find.  Could add a generic "foreign key" field 
> which can be updated with a key from another system to assist in tying 
> two systems together?

This would be nice.

> - "Company" is often useful to abstract (but makes the model very 
> complicated).  eg for suppliers I may have contact with multiple people 
> from that business. 
> - Arab names can follow a different convention to western names.
> - Might want to loose middlenames as a field?  In fact for most purposes 
> even breaking out forename and family name is overkill?  In practice you 
> sometimes get the "Henderson Chapman Curruthers Smythe"s of this world 
> and without prompting you don't know if the first name is a double 
> barrel or not, same with the last one, etc.  Also I get a lot of "John 
> and Jane Smiths" where you want to be able to search on both husband and 
> wife names because you don't know who will call.

I don't care so much about losing the middle name field.  I do, though, 
think that first and last names should be broken up into their own 
fields as well as a company field being added.  If you're going to put 
addresses all into one table, I have multiple situations where this 
would make it nearly impossible to identify a ship to address.  I have 
multiple instances of one company having 15 ship to locations.  The only 
real way to determine which location to ship to, sometimes, is the 
contact name.  Reporting sorted by last name is often needed by my 
clients as well.  If you only have a name field with a format of 
"company first_name last_name", how do I identify the last_name, 
particularly as it relates to overseas clients?

> - How will we handle "James Smith" of "Acme industries" where we want to 
> easily search on either name?
> - Plenty of generic "Notes" fields solve a lot of problems...  
> Especially if they are all easily searchable.  Lots of times its ok just 
> to stuff things in there rather than having a more structured system to 
> capture the data

Agreed, but only up to a point.  Right now, in our system, notes capture 
any miscellaneous data that doesn't fit well within the current schema 
and we ended up creating codes for all of it and me building an external 
search engine for it.  Entering the contact name? "Contact: fn=Charley 
mn=L ln=Tiggs co=Tigglet Enterprises".  Makes it somewhat easier to 
build a search engine around the notes but there's lots of fluff before 
and after it at times that complicates finding the info and using it for 
reporting.  Not to mention that folks who don't have very organized 
mines not entering the data correctly to begin with, which leads to 
creating support requests.

> Actually the whole area of "how do we build an external system onto 
> this" is worth thinking over.  It's not sensible to try and make LSMB be 
> all things to all people, but equally if I am going to (say) build a 
> website shop that hooks into the data here then I probably need some 
> strong ways to tie into the data easily.
>
> One thought might be to have a structured format for custom extra 
> fields.  The idea being to allow people to extend the customer data 
> fields, but in return they have to use certain naming conventions for 
> fields and tables (Goldmine used to require you to have fields like 
> CUSTOM_XXX, etc).  These extra fields could then be dynamically added to 
> various views in the LSMB system and allow limited ability to extend the 
> system without major programming.  I'm thinking here of the case that I 
> want to add say a "Customer Number", or "internal employee id", or 
> "employee tax code" or similar.
>
> A simple prospect tracker and todo list would also be *hugely* useful.  
> A simple model might be an additional table "customer-contact" which has 
> fields like date of contact, notes of meeting, follow up date, notes of 
> further work required, value of work. 
>
> The above would allow for example a very simple system to be built which 
> would mean that whenever my (asterisk) phone rings that the system will 
> open up a summary view for that customer with their name, details, notes 
> of previous calls, and a summary of invoices to that customer.  If it's 
> an unknown number then I can easily tap in their contact details while 
> we talk and if they want to buy something then I have everything ready 
> to go to knock out a sales order. 
>
> Anyway, just thinking outloud
>
> Looks promising

Agreed.  Looks promising.

Charley