FinanceFerret

Hi, you have a great site! I also recommend this site: http://www.0ggjiqmtj6.com iekhw , thanks!

Tables

Transactions

<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 "check # or other identifying text unique to this transaction within the current account (optional)",
 `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 NULL COMMENT "transaction voided",
 `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>

This is pretty much the Access version, with a minor tweak for multiuser support (WhoEntered).

• 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 changes:

• 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 from the same data.

Trx Types

Transaction types

<mysql>DROP TABLE IF EXISTS `Trx Types`; CREATE TABLE `Trx Types` (

 `Code` varchar(50) NOT NULL            COMMENT "identifying abbreviation",
 `Descr` varchar(50) default NULL       COMMENT "description",
 `IsDebit` BOOL NOT NULL DEFAULT FALSE  COMMENT "TRUE = debits from source account; FALSE = credits to source account",
 `IsEquity` BOOL NOT NULL DEFAULT FALSE COMMENT "TRUE = affects equity only; FALSE = regular transaction",
 PRIMARY KEY  (`Code`)

) ENGINE=MyISAM DEFAULT;</mysql>

• The name of this table should be changed later because "Types" is a keyword and confuses the syntax display parser
• Transaction amounts are generally entered as positive numbers; the direction in which the amount is moving is determined by the transaction type.

Event Log

Copied, with only slight changes, from VbzCart tables. This version is suitable for re-use in other projects, but perhaps should be renamed EventLog or Events because the MySQL syntax highlighter thinks "log" is a keyword.

<mysql>DROP TABLE IF EXISTS `Event Log`; CREATE TABLE `Event Log` (

   ID INT NOT NULL AUTO_INCREMENT COMMENT "log line identifier",
   EvWhen DATETIME NOT NULL COMMENT "when the event started",
   EvWhere varchar(255) COMMENT "where in the code the event happened (suitable for filtering)",
   Params varchar(255) COMMENT "any relevant parameters",
   Descr varchar(255) COMMENT "description of event",
   Code INT DEFAULT NULL COMMENT "numeric event code unique to location (EvWhere)",
   AppUser varchar(127) COMMENT "application username, for when we have a user-security system",
   SysUser varchar(127) COMMENT "who logged into the operating system (username)",
   Machine varchar(64) COMMENT "network name of machine from which the event was initiated, if applicable",
   isError BOOL COMMENT "FALSE = this is just a message or normal event; TRUE = there is a problem to fix (error)",
   isSevere BOOL COMMENT "TRUE = important enough to send email to admin immediately",
   Notes varchar(255) DEFAULT NULL COMMENT "manually-entered notes",
   PRIMARY KEY (`ID`)
) ENGINE = MYISAM;</mysql>

• Have to use "Ev" prefix because When and Where are keywords.