FinanceFerret/2.0/sql/Transactions

About

• Version: This is pretty much the Access version, with a minor tweak for multiuser support (WhoEntered).
• History:
  • 2009-08-11 Added doReuse field so permanently void records (like voided checks) can be distinguished from those which should be recycled (why do I obsessively do this?)
  • 2009-12-09 Added ConfNum to keep confirmation numbers separate from check numbers

Fields

• AmountOrig: not sure if this is actually being used; I think it was a sort of "back up" for one of the balance numbers, but it might be superceded by the Immed/Stated balance fields. Look at the data... but I know I haven't looked at it directly in years. See if the code does anything with it.
• ToVerify: also never used so far; it might be better just to have a separate table, since this flag will only ever be set temporarily.
• IsApprox: this is for attempting to budget ahead -- you know your electric bill is coming in two weeks, but you don't yet know the total; this lets you enter an approximate amount to deduct from your available funds while still keeping track of the fact that you don't actually know the amount
• CalcBalance: one tricky issue here is that the balance should only be calculated based on the bank's sorting order, but the transactions can be displayed in lots of different sortings. Should there be an index field to show the order of all transactions at the time of the last balance, to make recalculation faster? Or would it not even have that effect?

Future

• Change terminology from source/destination to local/remote -- local is the account whose transactions you're looking at, and remote is the other one:
  • ID_Acct -> ID_Local
  • ID_Dest -> ID_Remote
• ...or possibly it's not necessary to represent double-entry accounting with two actual records, if we can generate both views

SQL

<section begin=sql /><mysql>DROP TABLE IF EXISTS `Transactions`; CREATE TABLE `Transactions` (

 `ID` int(4) NOT NULL auto_increment,
 `ID_Type`      varchar(50) default NULL COMMENT "[Trx Types].ID",
 `ID_Acct`      int(4)      default NULL COMMENT "Accounts.ID - ID of account in which this transaction originates",
 `ID_Dest`      int(4)      default NULL COMMENT "Accounts.ID - ID of destination account",
 `ID_Equity`    int(4)      default NULL COMMENT "Accounts.ID - ID of equity account (if any)",
 `ID_TrxMirror` int(4)      default NULL COMMENT "Transactions.ID -- transaction of which this one is a mirror",
 `ID_TrxDup`    int(4)      default NULL COMMENT "[Trx Entered].ID -- transaction of which this one is a duplicate."
 `CheckNum`     varchar(50) default NULL COMMENT "our check # or other identifying text unique to this transaction within the current account (optional)",
 `ConfNum`      VARCHAR(50)  DEFAULT NULL COMMENT "confirmation number received from other party",
 `Amount`       DECIMAL(9,2) default NULL COMMENT "Amount added to ID_Acct account",
 `AmountOrig`   DECIMAL(9,2) default NULL COMMENT "Amount in check register, when bank disagrees",
 `DateAction`   datetime     default NULL COMMENT "Date action was taken (e.g. date on the check)",
 `DateEffective` datetime    default NULL COMMENT "Date as recorded by financial institution (on statement)",
 `WhenEntered`  datetime     default NULL COMMENT "When this transaction was entered into the system",
 `WhenChanged`  datetime     default NULL COMMENT "When this transaction was last edited",
 `WhoEntered`   varchar(63)  default NULL COMMENT "[user]@machine of who entered the transaction (see log for further edits)",
 `Accounted`    tinyint(1)   default NULL COMMENT "TRUE = assume transaction is accounted for by institution (use only if effective date is not available)",
 `Voided`       tinyint(1)   default FALSE COMMENT "transaction voided",
 `doReuse`      tinyint(1)   default FALSE COMMENT "there is no need to keep this information; reuse the record",
 `ToVerify`     tinyint(1)   default NULL COMMENT "transaction needs to be verified -- created automatically",
 `IsApprox`     tinyint(1)   default NULL COMMENT "exact amount of transaction is not known yet",
 `SortEntry`    varchar(50)  default NULL COMMENT "transaction order as entered into checkbook register",
 `SortInst`     varchar(50)  default NULL COMMENT "Order shown by financial institution (on statement)",
 `BalanceImmed` DECIMAL(9,2) default NULL COMMENT "Balance as given by financial institution at time of transaction (customarily includes this transaction in the figuring, but ok if otherwise)",
 `BalanceStated` DECIMAL(9,2) default NULL COMMENT "Balance after this transaction as given by financial institution on regular statement",
 `BalanceEquity` DECIMAL(9,2) DEFAULT NULL COMMENT "equity balance for ID_Acct with respect to ID_Equity",
 `UseBalanceEffective` tinyint(1) default NULL COMMENT "TRUE = restart balance calculations using BalanceEffective; FALSE = continue calculating using calculated balance",
 `UseBalanceEquityEff` BOOL   default FALSE COMMENT "TRUE = reset BalanceEquity to 0 regardless of calculations",
 `Descr`        varchar(255)  default NULL COMMENT "text describing the transaction -- similar to memo field on check",
 `CalcBalance`  DECIMAL(9,2)  default NULL COMMENT "balance as calculated by FinanceFerret",
 PRIMARY KEY  (`ID`),
 KEY `Acct` (`ID_Acct`)

) ENGINE=MyISAM;</mysql> <section end=sql /> from the same data.