On 6/19/07, Chris Travers <[EMAIL PROTECTED]> wrote:
> On 6/19/07, Ed W <[EMAIL PROTECTED]> wrote:
>
> >
> > One other issue which occured to me which is significant to think about
> > right now is as follows:
> >
> > - Invoices really should have delivery and billing addresses locked down
>
> Only caveat is "billing address" may be somewhat ambiguous in this case :-).
>
> Agreed on shipping invoices. Maybe we should store the address that
> the invoice was originally billed to also (which my be separate from
> the address the current statement with the invoiced amount may be
> sent to).
>
> > - As it's an address these should possibly be normalised out of the
> > invoice record itself
>
> Agreed.
>
> > - Customers may move or update their address details through time -
> > however, for record keeping purposes we need to know how we really
> > invoiced and at what address 20 years ago, even if they are no longer
> > there now
>
> Agreed.
>
> > - Customers may have multiple (regular) delivery addresses and tax codes
> > may be different for each address
>
> We need to tackle the tax issue. My own thinking is that this is
> something that is better handled by tax rules. 300 tax check boxes
> next to each location may be a problem.
>
> >
> > So one possibility is that whenever a customer contact record is edited
> > then we make a copy of the record, mark the old one obselete, update the
> > new record and use a linked list structure to chain together all the the
> > changed customer details (so that we have an audit of the old changed
> > addresses).
>
> 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.
>
> > Optionally we could allow deleting of these linked records
> > if no invoice/order/quote ever referenced that iteration of the
> > customer, eg change a customer twice in quick succession without
> > invoicing them, then we might only keep the latest change and not both
> > changes
>
> If we really want the audit trail, we don't want to allow deleting of
> these records.
>
>
> > Address should probably be nomalised out of the entity in order to allow
> > having multiple addresses per entity (eg two delivery addresses)
>
> We already do this in 1.3.
>
> >
> > How does this sound? We probably don't want this to get
> > overcomplicated, hence the suggestion just to duplicate records when
> > they are changed and hide the old records so that they don't normally
> > show up (but are available to be referenced by old invoices)
>
> This could get complicated. I think there are some ways of doing
> this, but may require some variation from what you suggest. For
> example, adding a valid_to timestamp with a default of infinity, and
> adding that to primary keys. Also, a lot of our indexes would need to
> become partial indexes.
>
> Ideally, we would use some sort of table partitioning to keep active
> records together as well.
>
Think about this:
we'd be dealing with possibly a many to many, so again, joined through
a second table to the third that has addresses and two extra fields,
one indicating start date, one end date, and if the end date is NULL,
it is current/active.
For company name change, a field with a back reference pointing to the
row for the previous name (assuming we don't also need a forward
reference).
Ciao,
David A. Bandel
--
Focus on the dream, not the competition.
- Nemesis Air Racing Team motto
-------------------------------------------------------------------------
This SF.net email is sponsored by DB2 Express
Download DB2 Express C - the FREE version of DB2 express and take
control of your XML. No limits. Just data. Click to get it now.
http://sourceforge.net/powerbar/db2/
_______________________________________________
Ledger-smb-devel mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/ledger-smb-devel
