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 FletcherSJohnson@Yahoo.com LinkedIn.com/in/FletcherJohnson twitter.com/fletcherJ strava.com/athletes/fletcherjohnson 408-946-0960 - work 408-781-2345 - cell
-----Original Message----- From: ProFox [mailto:profox-bounces@leafe.com] On Behalf Of Alan Bourke Sent: Wednesday, April 24, 2019 1:46 AM To: profoxtech@leafe.com 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.