Comments by Paul Maxwell 2023 (Top 2 by date)

Paul Maxwell 2023 9-Mar-23 7:27am View

Why not create a view in the database containing those joins? Then you just have one object to call and can rely on it behave consistently. Once it is created just treat it like a table and filter the data as you would normally. Don't be fooled into using "select *" though - that is false economy.

Paul Maxwell 2023 8-Mar-23 21:06pm View

Well, there are many enterprise level systems that rely on storing exchange rates in a database. So the basic concept of storing them in your db is OK IMHO. What I would suggest is that they change regularly, so you need some other columns e.g. "as at" (date/timestamp) and/or timestamp_from so that when you match to transaction you can get the most appropriate rate for when the conversion takes place. Some organisations might use "average rates" or "planning rates" so the frequency of changing the rates is reduced (from "spot rates") but even then you need a period of validity. Then on the transaction side of the conversion, you also need a timestamp for when the transaction happened - which I don't see in your sample data either - perhaps it is available at the "order level" instead of the table you have shown. AND there is no indication which currency has been used when storing the transaction.