On 6/19/07, Ed W <[EMAIL PROTECTED]> 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 ------------------------------------------------------------------------- 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
