On 3/25/19 8:15 AM, Frank wrote:
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' :-)
It would be interesting to see what the query planner tries to do with this:
WHERE
CASE
WHEN a.tran_type = 'ar_rec' THEN y.posted
WHEN a.tran_type = 'cb_rec' THEN w.posted
END = '1'
--
Angular momentum makes the world go 'round.
