On 6/19/07, David Bandel <[EMAIL PROTECTED]> wrote: > 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.
Well, you have one issue. Primary keys cannot have NULL components. It is probably better to have a valid_to timestamp which defaults to infinity. At any given point in time you pick the one with the lowest timestamp after a given date as the one that was active at that time. Then you can add the valid_to to the primary key and make it work like magic. > > 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). If you see it as a relational set rather than a C structure, we don't need references at all. Just the set which contains enough information that programs can generate their own data structures as needed from the data. > 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
