FinanceFerret/2.0/sql/trx part

Fields

• isXfer: TRUE if money is transferred, FALSE if ID_Acct is an equity account
• Identifier: a number unique to ID_Acct+trx.ID_Type, e.g. check number or payment confirmation number

Rules

• The sum of all isXfer=TRUE parts in a Transaction must be zero.
• The sum of all positive isXfer=TRUE parts in a Transaction must be ≤trx.Amount
• The sum of all negative isXfer=TRUE parts in a Transaction must be ≤trx.Amount

To Do

Currently trying to decide if trx.Amount should even exist; maybe the Amount should be dynamically calculated by summing trx_parts.

SQL

CREATE TABLE `trx_part` (
  `ID`           INT(4)           NOT NULL auto_increment,
  `ID_Trx`       INT(4)           NOT NULL,
  `ID_Acct`      INT(4)           NOT NULL,
  `isXfer`       TINYINT(1)       NOT NULL COMMENT "TRUE if money is actually transferret",
  `Amount`       DECIMAL(9,2) DEFAULT NULL COMMENT "amount of money added to ID_Acct account",
  `Identifier`   VARCHAR(255) DEFAULT NULL COMMENT "account-specific transfer ID",
  PRIMARY KEY  (`ID`),
  KEY `Acct` (`ID_Trx`)
) ENGINE=InnoDB;