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. Best Wishes, Chris Travers ------------------------------------------------------------------------- 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
