[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Proposed design for exchangerate and currency tables for 1.5

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__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.