FinanceFerret/v3

SQL

Accounts

CREATE TABLE
  `Account` (
    `ID`          int(11) NOT NULL AUTO_INCREMENT,
    `ID_Parent`   int(11) DEFAULT NULL,
    `ID_Owner`    int(11) DEFAULT NULL,
    `Name`        varchar(127) DEFAULT NULL,
    `isTxactable` tinyint(1) DEFAULT NULL,
    `Abbr`        varchar(63) DEFAULT NULL,
    PRIMARY KEY (`ID`)
  ) ENGINE = InnoDB

Owners

CREATE TABLE
  `Owner` (
    `ID` int(11) NOT NULL AUTO_INCREMENT,
    `ID_Parent` int(11) DEFAULT NULL,
    `Name` varchar(127) NOT NULL,
    `Abbr` varchar(15) DEFAULT NULL,
    PRIMARY KEY (`ID`)
  ) ENGINE = InnoDB

Transactions

Transactions are recorded in (so far) two parallel tables. "Actual" refers to the transactions as the user becomes aware of them, and "Stated" refers to the transactions as the other entity (most commonly a bank, but could be any business or individual) has recorded them. The dates and descriptions may differ. Amounts may be split differently, but the totals for any given Tuple should be the same.

Actual

(formerly "Trxact")

CREATE TABLE
  `TrxActual` (
    `ID` int(11) NOT NULL AUTO_INCREMENT,
    `ID_Account` int(11) NOT NULL,
    `ID_Tuple`   int(11) DEFAULT NULL,
    `RmtAbbr`    varchar(64) DEFAULT NULL,
    `Amount`     decimal(9,2) DEFAULT NULL,
    `Balance`    decimal(9,2),
    `When`       timestamp NULL DEFAULT NULL,
    `Seq`        decimal(6,2) DEFAULT NULL,
    `Tags`       varchar(255) DEFAULT NULL,
    `What`       varchar(127) DEFAULT NULL,
    `RmtConf`    varchar(100) DEFAULT NULL,
    PRIMARY KEY (`ID`)
  ) ENGINE = InnoDB

Stated

CREATE TABLE
  `TrxStated` (
    `ID`         int(11) NOT NULL AUTO_INCREMENT,
    `ID_Account` int(11) NOT NULL,
    `ID_Tuple`   int(11) DEFAULT NULL,
    `Amount`     decimal(9,2) DEFAULT NULL,
    `Balance`    decimal(9,2),
    `When`       timestamp NULL DEFAULT NULL,
    `What`       varchar(127) DEFAULT NULL,
    PRIMARY KEY (`ID`)
  ) ENGINE = InnoDB