Re: For those interested in the CRM/Entity management

["Chris Travers" <..hidden..>]

On 6/21/07, Stroller <..hidden..> wrote:
> On 20 Jun 2007, at 22:57, Ed W wrote:
> >
> > I think we are all agreed that snapshotting the invoice is a
> > requirement.  But:
> >
> > - lets assume that most invoices contain the same address time and
> > time again,
> > - hence we start thinking about normalising that address record
> > rather than duplicating it endlessly
>
> This makes perfect sense when you're a database programmer, but not
> if you're an accountant.
>
> Paper invoices have the address printed at the top of each one.
> Likewise invoices in Ledger should contain the whole address in each
> one, IMO.

Actually, the way it will probably work is that there is a location
table which includes all address info, a table handling a many-many
relationship of locations to contacts (you could have one business
customer with two addresses and two retail customers with the same
address).  Then there would probably be a one to many relationship
between addresses and invoices.

Addresses would be intended to be unchanged once entered.  I.e. if you
move, the old address record remains in the db, we add a new one, and
we tie them together properly.

Imagine what happens if when start supporting multiple shipto's.  How
many addresses do we add to the invoice record?  This is best handled
relationally (especially, since not all shipto information may even
appear on the invoice).

> I see what you're trying to do, and if you're a database programmer
> or administrator you might think I'm crazy for suggesting this but
> IMO it's better to have duplicate data. When you normalise you make
> things more complicated and introduce the risk of a record becoming
> changed (linked to an different entity) after its been posted.

1.3 provides most of the groundwork to fix this problem.

> I
> appreciate that good a programmer will endeavour to ensure that this
> never happens, but nevertheless from a certain perspective duplicate
> data in posted invoices _is_ "correct" - the address belongs
> _separately_ to separate records.

Agreed that this is a concern, though I still disagree with the
solution.  I would instead favor David Tangye's suggestion that we
store printable documents in the db as such along with all relevant
relational data.

> Let's consider a "normalised" paper trail.

Paper trail == documents.  Documents should contain all necessary data
in the db to reconstruct all relevant business informaton related to
the invoice.  However, for a paperless paper trail, we also need to
store the document.  I don't see a way around this.

> We print out an invoice to
> send to the customer - it has their address on it, the details of
> items & services sold and a total amount owing. The logical thing to
> so is not keep a copy of the whole invoice but to simply record
> customer number, item numbers & amount in a ledger. But the tax man
> does not allow us to do this - he requires us to keep a whole copy of
> the invoice, wasting more paper and consuming much more space in our
> filing cabinets. Whether we produce it with carbon paper, a
> photocopier or by printing it out twice on our laser printer, our
> copy of the invoice has to be exactly the same as the one we sent out
> (not just the relational data required to produce an exact copy).

Ok.  Suppose your logo changes.  Suppose someone changes a
template....  These would not be reflected in the new reprinted
version and thus it would not be an exact copy.  The only way to do
this is to store a representation of the printed page in the db
(probably as either PDF or Postscript).

While it might be possible to also keep a history of the templates,
logos, etc in the db (and we might want to consider that), we cannot
guarantee that the snapshot is accurate without generating the invoice
as a document and putting that back in the db (we can reasonably
guarantee that with appropriate controls).

> Likewise Ledger should keep a whole copy of the invoice, and not to
> to take short cuts (however efficient they may seem).

Normalization is not just about efficiency.  It is also about ensuring
that the data model can work with any other needs as well, even those
we haven't thought of yet.

Hope this makes sense,
Chris Travers