WorkFerret/tables/session

About

• History:
  • 2010-01-28 Adapted from MS Access version
  • 2010-01-29 Changed ID_Prev to Seq; moved next to Sort
  • 2010-01-30 Added CostAdd field from MS Access version (not sure why it got left out)
  • 2010-02-01 Added ID_InvcLine field for invoice management
• Fields:
  • TimeAdd: time added to (finish-start), typically an approximation for when start/finish times aren't known
  • TimeSub: time subtracted from (finish-start), typically for distractions in the middle of a work session
  • TimeTotal = WhenFinish - WhenStart + TimeAdd - TimeSub
  • CostAdd is added before calculating CostLine; can be used for flat-rate billing and for deducting invoice amounts
  • CostLine = (TimeTotal * BillRate) + CostAdd

SQL

<mysql>DROP TABLE IF EXISTS `session`; CREATE TABLE `session` (

 `ID`         INT(4)           NOT NULL AUTO_INCREMENT,
 `ID_Proj`    INT(4)           NOT NULL COMMENT "Project to which this session applies",
 `WhenStart`  DATETIME     DEFAULT NULL COMMENT "when Session started (optional)",
 `WhenFinish` DATETIME     DEFAULT NULL COMMENT "when Session finished (optional)",
 `Sort`       DECIMAL(7,4) DEFAULT NULL COMMENT "when there's no specific time, use this to sort sessions",
 `Seq`        INT(4)           NOT NULL COMMENT "order in which this line was added (within a Project)",
 `TimeAdd`    INT(4)       DEFAULT NULL COMMENT "time to add, in minutes",
 `TimeSub`    INT(4)       DEFAULT NULL COMMENT "time to subtract, in minutes",
 `TimeTotal`  INT(4)       DEFAULT NULL COMMENT "calculated total time for this session",
 `ID_Rate`    INT(4)       DEFAULT NULL COMMENT "rate ID, for tracking usage -- ignored by billing",
 `BillRate`   DECIMAL(9,2) DEFAULT NULL COMMENT "rate, if using time",
 `CostAdd`    DECIMAL(9,2) DEFAULT NULL COMMENT "additional cost (can be used instead of by-rate total)",
 `CostLine`   DECIMAL(9,2)     NOT NULL COMMENT "final cost for this line",
 `CostBal`    DECIMAL(9,2) DEFAULT NULL COMMENT "cost balance after this line is added in",
 `Descr`      VARCHAR(127) DEFAULT NULL COMMENT "session summary, for invoices",
 `Notes`      VARCHAR(255) DEFAULT NULL COMMENT "longer notes, for reference",
 `WhenEntered` DATETIME        NOT NULL COMMENT "when this record was first created",
 `WhenEdited`  DATETIME    DEFAULT NULL COMMENT "when an existing record was modified by hand",
 `WhenFigured` DATETIME    DEFAULT NULL COMMENT "when the balance was last calculated",
 `ID_InvcLine INT(4)       DEFAULT NULL COMMENT "invoice line to which this session was applied",
  PRIMARY KEY(`ID`)

) ENGINE=MyISAM;</mysql>