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.
http://www.efficito.com/learn_more.shtml
------------------------------------------------------------------------------
WatchGuard Dimension instantly turns raw network data into actionable
security intelligence. It gives you real-time visual feedback on key
security issues and trends. Skip the complicated setup - simply import
a virtual appliance and go from zero to informed in seconds.
http://pubads.g.doubleclick.net/gampad/clk?id=123612991&iu=/4140/ostg.clktrk
_______________________________________________
Ledger-smb-devel mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/ledger-smb-devel
