HiI 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 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: - 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. 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. Another idea would be a compound key: (CustomerGroupId, CustomerId) where again CustomerId is strictly increasing. Customer Id would be unique and used as the key for any invoice. CustomerGroupId (rename it!) would be the same if the customer is the same, ie we use CustomerId is a pointer to a specific v ersion of of a Customer (who in turn is defined by CustomerGroupId). Probably there is a better choice of name for the first field
Could do. On the other hand I gain nothing by having a ton of edited records if nothing ever refers to them? 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? 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? 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! - 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) - 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 I'm just thinking out loud here, but perhaps someone else wants to kick these use cases around a bit more and see if something drops out? Ed W |