Alan, I picked an easy to understand example. The point I was trying to make was that Mike had a question that was ambiguous - the best options really depended on what he needed to accomplish - audit logs or time aware data.
But, to be nit-picky, if I generate thousands of invoices per day (or more), does it make more sense (especially considering normalization rules) to have one date aware record or an additional field(s) in the invoice table showing the purchase price (and any other time sensitive data)? I do agree that, in a many cases, your approach should work just fine. Fletcher Fletcher Johnson [email protected] LinkedIn.com/in/FletcherJohnson twitter.com/fletcherJ strava.com/athletes/fletcherjohnson 408-946-0960 - work 408-781-2345 - cell -----Original Message----- From: ProFox [mailto:[email protected]] On Behalf Of Alan Bourke Sent: Wednesday, April 24, 2019 1:46 AM To: [email protected] Subject: Re: How best to do an audit trail of changes (EASILY WITHOUT THE NEED FOR A DBA) On Tue, 23 Apr 2019, at 5:04 PM, Fletcher Johnson wrote: > The second is much more critical. If the price of an item changes, > when an invoice is printed, it needs to show the price in effect when > it was printed. If I sell something today, the current price is > usually appropriate. But if I re-print an invoice from last month, I > need to know the price (and quite likely, other values) in effect at > that time. In other words, time is just one more critical data point > used to identify the correct data. > > The downside to the first is that, unless you re-write your code, the > solution requires either triggers or stored procs - both of which > require a DBA (in most work environments.) > > The downside to the second is that not only will it require a dba > (structure changes), In the case of re-printing an invoice, store the price at the time of initial creation in your invoice line details table and use that when reprinting. Or store a PDF of the emailed\printed invoice, and re-use that. -- Alan Bourke alanpbourke (at) fastmail (dot) fm [excessive quoting removed by server] _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[email protected] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
