We made an ad-hoc system using MySQL, integrated with project management data.
Because it is on a server, it's easy for multiple access without the "who's got the cookie" problem of multiple-updaters that you have with a shared spreadsheet.
Because it's integrated into our project management system, we can instantly see the labour that's been billed to a project or profit centre.
Here's an example project. Scroll down to see time tracking and time summary roll-ups, which are standard SQL queries.
Here's the schema for our "timelog:"
CREATE TABLE IF NOT EXISTS `s_timelog` (
`id` int(10) unsigned NOT NULL auto_increment,
`mw_user_id` int(11) unsigned NOT NULL default '2',
`in` datetime NOT NULL,
`out` datetime NOT NULL,
`project_id` int(11) unsigned NOT NULL,
`activity_id` int(11) unsigned NOT NULL,
`accomplishment` varchar(255) character set utf8 NOT NULL,
`Paid` enum('yes','no') character set utf8 NOT NULL default 'no' COMMENT 'hours that are compensated',
`Class_B_qualified` enum('yes','no') character set utf8 NOT NULL default 'no',
`updated` timestamp NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `mw_user_id` (`mw_user_id`),
KEY `in` (`in`),
KEY `project_id` (`project_id`),
KEY `activity_id` (`activity_id`),
KEY `out` (`out`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='EcoReality time log.' AUTO_INCREMENT=13955 ;
where:
- mw_user_id points to the user performing the labour
- project_id points to a definition of the project the labour is on
- activity_id points to a definition of an activity the labour is on
A "project" looks like this:
CREATE TABLE IF NOT EXISTS `s_project` (
`id` int(6) unsigned NOT NULL auto_increment,
`profit_centre` int(10) unsigned NOT NULL default '0',
`name` varchar(62) NOT NULL,
`URL` varchar(255) default NULL,
`need` varchar(255) NOT NULL,
`description` text NOT NULL,
`super_id` int(11) unsigned NOT NULL default '0',
`type` enum('capital','operations') NOT NULL default 'operations',
`duration` enum('fixed','ongoing') NOT NULL default 'fixed',
`status` enum('abandoned','agreed','cancelled','completed','on hold','ongoing','requested') NOT NULL default 'requested',
`start` date NOT NULL,
`end` date NOT NULL,
`completed` date NOT NULL,
`depends_on_id` int(6) unsigned NOT NULL default '0',
`budget_expense` decimal(7,2) NOT NULL,
`budget_effort` decimal(7,2) NOT NULL,
`effort_units` enum('minutes','hours','days','weeks','months') NOT NULL default 'hours',
`steward_id` int(5) unsigned NOT NULL default '0',
`private` enum('yes','no') NOT NULL default 'no',
`updated` timestamp NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`notes` text NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`),
KEY `duration` (`duration`),
KEY `status` (`status`),
KEY `super_id` (`super_id`,`depends_on_id`,`steward_id`),
KEY `depends_on_id` (`depends_on_id`,`steward_id`),
KEY `steward_id` (`steward_id`),
KEY `profit_centre` (`profit_centre`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='EcoReality (and other) projects that require resources.' AUTO_INCREMENT=140 ;
Projects can be nested and have dependents.
Activities are typically things that end in "ing," and can be hierarchical:
CREATE TABLE IF NOT EXISTS `s_activity` (
`id` int(10) unsigned NOT NULL auto_increment,
`name` varchar(31) character set utf8 NOT NULL,
`super_activity` int(11) unsigned NOT NULL default '0',
`description` varchar(255) character set utf8 NOT NULL,
PRIMARY KEY (`id`),
KEY `name` (`name`),
KEY `super_activity` (`super_activity`),
KEY `description` (`description`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin PACK_KEYS=0 COMMENT='activity names' AUTO_INCREMENT=42 ;
INSERT INTO `s_activity` (`id`, `name`, `super_activity`, `description`) VALUES
(1, 'Administering', 0, 'Basic administration and management activities.'),
(2, 'Agriculture', 0, 'Activities related to growing crops.'),
(3, 'Designing', 0, 'Developing designs for systems, processes, structures and machines.'),
(4, 'Educating', 0, 'Performing an educational activity.'),
(5, 'Managing Information', 1, 'Performing an information management activity.'),
(6, 'Maintaining', 0, 'Repairing or sustaining the condition of an artifact, process, structure, or system.'),
(7, 'Planning', 1, 'Making plans for future projects and activities.'),
…
