Difference between revisions of "FerretMyMoney/sql/fmmTrxacts"

from HTYP, the free directory anyone can edit if they can prove to me that they're not a spambot
Jump to navigation Jump to search
(two balances, flags for how balance is applied)
(consolidated fingerprint fields)
 
(3 intermediate revisions by the same user not shown)
Line 2: Line 2:
 
* '''Purpose''': stores extended data about [[KMyMoney]] [[KMyMoney/sql/kmmTransactions|transactions]]
 
* '''Purpose''': stores extended data about [[KMyMoney]] [[KMyMoney/sql/kmmTransactions|transactions]]
 
* '''Fields''':
 
* '''Fields''':
−
** '''UseBank''': if TRUE, use prior BalBank instead of prior BalCalc when calculating this BalCalc
+
** '''UseBank''': if TRUE, set BalCalc to BalBank rather than using any prior calculations
 
** '''UseDate''': if TRUE, the bank's balance should apply after the last transaction of the day, and is not tied to a specific transaction.
 
** '''UseDate''': if TRUE, the bank's balance should apply after the last transaction of the day, and is not tied to a specific transaction.
 
*** The actual implementation of this could either be to always move the bank's balance to the last transaction of the day, or it could be to store the balance in a separate table such as {{l/same|fmmBankBals}}. This table doesn't care how it's done.
 
*** The actual implementation of this could either be to always move the bank's balance to the last transaction of the day, or it could be to store the balance in a separate table such as {{l/same|fmmBankBals}}. This table doesn't care how it's done.
Line 8: Line 8:
 
** '''2012-02-19''' designed
 
** '''2012-02-19''' designed
 
** '''2012-02-21''' added '''SortBank''' field
 
** '''2012-02-21''' added '''SortBank''' field
−
** '''2012-03-03''' changed '''Balance''' to '''BalCalc''' and '''BalBank'''; added '''UseBank'''
+
** '''2012-03-03''' changed '''Balance''' to '''BalCalc''' and '''BalBank'''; added '''UseBank'''.
 +
*** Changed '''ID''' to '''ID_Trx''' and added 2nd key, '''ID_Acct'''.
 +
*** Balances apply to a particular transaction within a particular account.
 +
** '''2012-03-15''' added auto-numbered primary key '''ID''' and KMM_* fingerprinting fields
 +
** '''2012-03-22''' decided KMM_* fingerprinting fields should be one big field (KMM_finger), so fingerprinting code can be modified to use additional fields without modifying the table design. (The original version had separate fields for kmmSplits.memo, kmmSplits.postDate, and kmmTransactions.entryDate)
 
==SQL==
 
==SQL==
 
<section begin=sql /><mysql>CREATE TABLE `fmmTrxacts` (
 
<section begin=sql /><mysql>CREATE TABLE `fmmTrxacts` (
−
   ID       VARCHAR(32)      NOT NULL COMMENT "ID of Transaction record in KMM",
+
   ID         INT        NOT NULL AUTO_INCREMENT,
−
   WhenBank DATETIME    DEFAULT NULL COMMENT "posting date as shown by the bank record",
+
  ID_Trx    VARCHAR(32)      NOT NULL COMMENT "ID of KMM Transaction",
−
   SortBank DECIMAL(3,2) DEFAULT NULL COMMENT "additional sorting to match bank order, if needed",
+
  ID_Acct    VARCHAR(32)      NOT NULL COMMENT "ID of KMM Account",
−
   BalCalc DECIMAL(9,2) DEFAULT NULL COMMENT "balance calculated after this transaction",
+
   WhenBank   DATETIME    DEFAULT NULL COMMENT "posting date as shown by the bank record",
−
   BalBank DECIMAL(9,2) DEFAULT NULL COMMENT "balance according to bank after this transaction",
+
   SortBank   DECIMAL(3,2) DEFAULT NULL COMMENT "additional sorting to match bank order, if needed",
−
   UseBank BOOL        DEFAULT 0    COMMENT "TRUE = use the bank's balance; ignore previous calculations",
+
   BalCalc   DECIMAL(9,2) DEFAULT NULL COMMENT "balance calculated after this transaction",
−
   UseDate BOOL        DEFAULT 0    COMMENT "TRUE = bank's balance is end-of-day, not specific transaction",
+
   BalBank   DECIMAL(9,2) DEFAULT NULL COMMENT "balance according to bank after this transaction",
−
   PRIMARY KEY(`ID`)
+
   UseBank   BOOL        DEFAULT 0    COMMENT "TRUE = use the bank's balance; ignore previous calculations",
 +
   UseDate   BOOL        DEFAULT 0    COMMENT "TRUE = bank's balance is end-of-day, not specific transaction",
 +
  KMM_finger TEXT        DEFAULT NULL COMMENT "software-generated fingerprint for corresponding KMM data",  
 +
   PRIMARY KEY(ID),
 +
  KEY(`ID_Trx`,`ID_Acct`)
 
  )
 
  )
 
  ENGINE = MYISAM;</mysql>
 
  ENGINE = MYISAM;</mysql>
 
<section end=sql />
 
<section end=sql />

Latest revision as of 15:00, 22 March 2012

About

  • Purpose: stores extended data about KMyMoney transactions
  • Fields:
    • UseBank: if TRUE, set BalCalc to BalBank rather than using any prior calculations
    • UseDate: if TRUE, the bank's balance should apply after the last transaction of the day, and is not tied to a specific transaction.
      • The actual implementation of this could either be to always move the bank's balance to the last transaction of the day, or it could be to store the balance in a separate table such as fmmBankBals. This table doesn't care how it's done.
  • History:
    • 2012-02-19 designed
    • 2012-02-21 added SortBank field
    • 2012-03-03 changed Balance to BalCalc and BalBank; added UseBank.
      • Changed ID to ID_Trx and added 2nd key, ID_Acct.
      • Balances apply to a particular transaction within a particular account.
    • 2012-03-15 added auto-numbered primary key ID and KMM_* fingerprinting fields
    • 2012-03-22 decided KMM_* fingerprinting fields should be one big field (KMM_finger), so fingerprinting code can be modified to use additional fields without modifying the table design. (The original version had separate fields for kmmSplits.memo, kmmSplits.postDate, and kmmTransactions.entryDate)

SQL

<mysql>CREATE TABLE `fmmTrxacts` ( 

 ID         INT         NOT NULL AUTO_INCREMENT,
 ID_Trx     VARCHAR(32)      NOT NULL COMMENT "ID of KMM Transaction",
 ID_Acct    VARCHAR(32)      NOT NULL COMMENT "ID of KMM Account",
 WhenBank   DATETIME     DEFAULT NULL COMMENT "posting date as shown by the bank record",
 SortBank   DECIMAL(3,2) DEFAULT NULL COMMENT "additional sorting to match bank order, if needed",
 BalCalc    DECIMAL(9,2) DEFAULT NULL COMMENT "balance calculated after this transaction",
 BalBank    DECIMAL(9,2) DEFAULT NULL COMMENT "balance according to bank after this transaction",
 UseBank    BOOL         DEFAULT 0    COMMENT "TRUE = use the bank's balance; ignore previous calculations",
 UseDate    BOOL         DEFAULT 0    COMMENT "TRUE = bank's balance is end-of-day, not specific transaction",
 KMM_finger TEXT         DEFAULT NULL COMMENT "software-generated fingerprint for corresponding KMM data", 
 PRIMARY KEY(ID),
 KEY(`ID_Trx`,`ID_Acct`)
)
ENGINE = MYISAM;</mysql>

Retrieved from "https://htyp.org/mw/index.php?title=FerretMyMoney/sql/fmmTrxacts&oldid=17002"