Re: When to store data that could be derived

On 2019-03-24 2:41 PM, Peter J. Holzer wrote:
> On 2019-03-24 10:05:02 +0200, Frank wrote:

Many thanks to Peter et al for their valuable insights. I have learned a 
lot.

 > So the important part here is not whether data is added, but whether
 > data is changed. Sure, new transactions are added all the time. But is
 > it expected that the data used to derive amount_cust and amount_local
 > (e.g. the exchange rate) is changed retroactively, and if it is should
 > the computed amount change? (I'm a bit worried about the join with the
 > customers table here - what happens when a customer moves their
 > headquarters to a country with a different currency?)

I think I have got both of those covered. I store the exchange rates in 
physical columns on the transaction, so the compute expressions will 
always return the same values. I have separate tables for 
'organisations' (O) and for 'customers' (C). C has a foreign key 
reference to O, and most static data such as addresses and contact 
details are stored on O. So if a customer moved, I would create a new C 
record with the new currency, and flag the old C record as inactive. 
They can happily co-exist, so receipts can be processed against the old 
C record until it is paid up.

I have started doing some volume tests, and at this stage, for the kind 
of volumes I am concerned about, it looks as if performance is a non-issue.

I generated about 22000 invoices and 22000 receipts, over 12 customers 
and 6 months. Invoices and receipts are stored in separate tables, and a 
VIEW presents them as a single table.

Using the VIEW, I selected all transactions for a given customer for a 
given month. It returned 620 rows and (on my slow desktop computer) it 
took 20ms. I can live with that.

I will generate some higher volumes overnight, and see if it makes a big 
difference. If you do not hear from me, you can consider it 'problem 
solved' :-)

Again, thanks to all.

Frank