The requirements here are:
1. Store currency rates (from one currency to another)
2. Set up to allow configurable per transaction deviation from rates above
3. Would be managed by PGObject::Simple::Role-based classes.
So here is the schema:
CREATE TABLE currencies (
id serial not null unique,
symbol text primary key,
allowed_variance numeric not null default 0.15, -- 15% default
is_default bool not null default false
); -- allowed variance would not be used until we switch over
-- to new code for financial stuff.
CREATE UNIQUE INDEX only_one_default_currency_idx ON currencies(is_default)
WHERE is_default;
-- assuming 200 currencies x 200 currencies per day, this still gives us
-- 150 years before rollover. Since the exchange_rate table here is intended
-- to hold current rather than historical information, at that point either old
-- data can be purged or the entry_id column can be made a bigint.
CREATE TABLE exchange_rate (
entry_id serial not null unique,
buy_curr int not null references currencies(id),
sell_curr int not references currencies(id),
rate numeric not null
);
Here are the functional interfaces. Their functions should be clear. Note that we'd need to decide whether buy_curr and sell_curr should refer to bank posted rates, or whether they refer to what we are doing (since that would reverse the labels).
CREATE OR REPLACE FUNCTION currency__list()
CREATE OR REPLACE FUNCTION currency__get(in_symbol text)
CREATE OR REPLACE FUNCTION currency__save(in_id int, in_symbol text, in_allowed_variance numeric, in_is_default bool)
CREATE OR REPLACE FUNCTION exchangerate__get(in_sell_curr text, in_buy_curr text, in_post_date date)
CREATE OR REPLACE FUNCTION exchangerate__set(in_sell_curr text, in_buy_curr text, in_post_date date, in_rate numeric)
CREATE OR REPLACE FUNCTION exchangerate__list_on_day(in_post_date date)
--
Best Wishes,
Chris Travers
Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in.