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

Re: For those interested in the CRM/Entity management

On 6/19/07, Ed W <..hidden..> wrote:


 Hi


 I was with you up until you said "linked list" which isn't really
meaningful in this context. More likely you would have a list of
records which would have a valid_to attribute describing when they
were no longer used.


 Well, I was thinking of linked list in the sense that you have an Company A
where the records describe the progression:

 A1 -> changed to -> A2 -> A3 -> etc


Ok.  Thinking of "progressive change set" works for me.



 It could be done based on the ID field being strictly monotonic and then
sorting in some way, but basically if we find an invoice then we can do the
following use cases on it:


id field, and a valid_to timestamp.  When the record changes, the id
stays the same and the valid_to timestamp changes.   This forms the
real primary key so we always have access to the full information.

However, there are two possible reasons for this.  Only one of them
seems to me to be optimal.

The first (suboptimal) reason is that one may want to store addresses
related to back invoices.  This is suboptimal because you have an
arbitrary constraint which says that you must tie the address on the
invoice to the customer record.  We don't do this anyway.  It is,
however, a good idea (probably in 1.4) to provide locations for
invoices separately from customers.

The second (optimal) reason is to have a record of every change made
to every customer or vendor for audit purposes later.  This way, if
someone wants to know who changed what when, that information is
retained in the database, and one can even build a picture of what
things looked like at any given point.    In this case, one should
probably not allow deleting of obsolete records even when nothing else
points to it because it could allow for the loss of information as to
who changed what when.


 - Find all the previous invoices to the same customer even though the name
may have changed (or other details)
 - For a given iteration of the customer record we can see what date it came
into existence and possibly when it was then changed to something else (eg
for A2 we just look at the creation date of A2 to see when we created it and
check for an A3 record to see when the next iteration was created.  No need
for explicit dates in the record itself perhaps?)

 So doubly linked list might make sense perhaps in this case in order to
traverse all the iterations of a particular customer.


The sorts of linking have nothing to do with how the data is stored in
the db.  If we build this sort of thing properly, we should have the
ability to do whatever we want with the data.


 Or alternatively we
could have a compound key such as (CustomerId, VersionNo), where VersionNo
is strictly increasing.  With the later condition in place one simply sorts
by VersionNo in order to get records in order.


I think valid_to is more descriptive.



 Could do.  On the other hand I gain nothing by having a ton of edited
records if nothing ever refers to them?


Because that way you can find out who changed what when, and possibly why.

If you have a ton of edited records with nothing referring to them, I
would think that might be a sign that something is wrong.


 I was thinking almost of a COW kind of scheme where edits are edits unless
something references the record in which case the record is branched to a
new record and the edits applied there.  That record can then be endlessly
edited until something references it, at which point it gets cloned, etc.

 Make sense?


I understand what you are saying, but I still disagree.  Instead I
think we are talking two different things.

1) Invoices need to be entirely self-contained.  They need to store
all information required to recreate and track them.  We may want to
add a lot more metadata (even if it looks like duplication) to the
invoice to make that possible.  That is the best way of tracking
customer state information at time of invoice.  This may allow us to
do other things as well.  For example:

Customer A agrees to buy something on behalf of Customer B.  We could
put whatever information relating to Customer B we want to put
relative to the invoice and that information may not reference
anything related to the current state of Customer A except the credit
account.

2)  If we want a full audit trail for customer/vendor, we should do
this for its own sake.



 However, To be honest.  Once you abstract out the address for a customer
and make the tax rules trigger based on the invoice/delivery details as well
as the customer status, then I'm not actually sure that "customer" records
will be likely to change that much anyway...  I guess in the case of custom
fields it's sensible to version the data, but mainly I should think it's
address/contact details which get updated through time?


Note that I think that in 1.4, some things may be added into the
invoice model.  For example, tax portions of the transaction should be
a part of the record.  I also think that billing information should be
also attached directly to the invoice as independant of the customer
record.  I also expect that customer and vendor will continue to be
broken out and merged (credit accounts, bank accounts, and the like).


 Here are some use cases (specific to *my* needs!) so that we can be a bit
more concrete about this:

 - Customer A makes initial contact and wants something in a hurry.  EU
shipping address, but no vat details so we charge sales tax (VAT) and ship
the item
 - Customer A comes back next week and we have more time to sort out the
paperwork and they give me their VAT details and this time we can zero rate
the item for tax purposes.  Currently means creating customer "A (no vat)"
and remembering which customer to use forever afterwards (no way to hide
dead entry)
 - Customer A moves down the road to their new premises.  Now I need to
create a new "A2 (no vat)" record.  It's starting to get hard to name these
in a way that people don't object on the top of their invoices!!
 - Customer "A2 (no vat)", orders something else, but with a shipping
address which is now local to me and incurs sales tax (VAT).  Now I need a
4th record for this customer where the vat option is selected again...  Now
very hard to remember which one to use!


Ok.  1.3's db structure supports multiple addresses.  Unfortunately
this makes tax issues more problematic and really pushes us to get
good ship-to-based tax support included.


 - Customer B emails me their order and I have the address as a nice block.
Would ideally like to "cut-n-paste" it into the app and have it guess the
various parts of the address at least roughly.  Most times this will break,
but a best efforts guess saves time cutting and pasting.
 - Often I need to paste the address out of the app in order to make
something like a shipping label.  Would be nice to be able to customise
reports more to make shipping labels and also perhaps have a simpler way to
get the address out of the system with a quick cut-n-paste (currently I use
the print to html report and get it from there)


Shipping label printing is something I have thought about a great
deal.  It is not too hard to deal with.  Submit a feature request.  As
it is not on the road map, I don't know when it will happen.  But at
least it will be out there.


 - Customer C is really one account (XYZ plc).  However, I have two guys
working there who regularly ring me up and both have permission to make
orders on that account.  Really I want only one "customer" entry to show
here because when one rings up on behalf of the other I need to be able to
see all the orders from both of them because one will be out of the office
and the other will be buying airtime for the guy out of the office.
 - This is one of the motivations towards having a kind of "company" model
where a company is named on the invoice, but it can have multiple contacts
against that company.  Not quite sure how to keep this simple, but probably
where you have one company and three different kinds of relationship with
them then you enter this as three companies with the same name, and this
model is more modelling a single account with a company than the entire
relationship with that company?  Legally the entity buying the product is
normally the company and not the person making the order


1.3's data model supports this.  We don't have all the coding in place
yet to make it work, however.

Best Wishes,
Chris Travers