I need a table to store the state of a financial transaction. The state of this transaction can be roughly modelled by this class.
class FinancialTransaction
{
Integer txId,
Money oldLimit,
Money newLimit,
Money oldBalance,
Money newBalance,
Date txDate
}
class Money
{
Currency curr,
BigDecimal amount
}
My initial design of the schema looks like this:
CREATE TABLE tx
(
txId bigint(20) unsigned NOT NULL,
oldlimit_currency varchar(3) NULL,
oldlimit_amount decimal(7,5) default 0.00,
newlimit_currency varchar(3) NULL,
newlimit_amount decimal(7,5) default 0.00,
----snipped----
PRIMARY KEY (txId)
)
Two things worry me:
- Each transaction occurs based on one Currency. I haven't thought far enough as to whether I might need to support transactions that may happen in multiple currencies. Assuming that it doesn't change; then isn't it more space-efficient to just maintain one currency column ? Will I regret this simplistic solution ?
- Since each Money item is a value object, should I instead save all Money objects into a separate Money table and have the original table use moneyIds as foreign keys to the Money table ?
That is,
CREATE TABLE tx
(
txId bigint(20) unsigned NOT NULL,
oldlimit_money_id int NOT NULL,
newlimit_money_id int NOT NULL,
----snipped----
PRIMARY KEY (txId),
FOREIGN KEY (oldlimit_money_id) REFERENCES MONEY(id) ON DELETE NO ACTION ON UPDATE NO ACTION,
FOREIGN KEY (newlimit_money_id) REFERENCES MONEY(id) ON DELETE NO ACTION ON UPDATE NO ACTION
)
Are there alternative designs ?
Thanks lazyweb.