Database schema of the task list

Previous: File organization of todomgr.tcl ] [ Top: To-do manager ] [ Next: Schema for user access ]

The task list is wftk's active process repository. It consists of two tables; one defines projects, the other tasks. My epiphany about the relationship of wftk to a general task management tool came when I realized that tasks can exist without projects perfectly well.

This schema works on PostgreSQL. Nothing fancy, and I'm omitting DBA details like indexing and the like; I just want to define the fields we'll need. Let's call our tables "process" and "task", yeah, those are fairly descriptive. Translation of this schema to other SQL dialects should be relatively straightforward, as most of the fields are just character data anyway (one of the things I like about PostgreSQL is that I don't have to feel guilty about text fields, or pretend that I can project meaningful size boundaries onto them.)

Besides the task and process tables, I'm including a user table, which will probably be replaced at some point with a more sophisticated directory mechanism, and a keyword table. The keyword table started out as an organizational mechanism completely extraneous to wftk, but I realized that it's also an excellent place to store permissions linking users with classes of task and process. I'm not sure how much overlap that will end up having with workflow proper, but that's the whole purpose of doing a prototype. (I'm approaching this with the attitude of "Plan to throw one away; you will anyway." I surely wish I knew the attribution for that quote, because it's a good one.)

So here are the tables we'll be defining, all in all:
create table process (
See Process table

create table task (
See Task table

create table user (
See Schema for user access

create table keyword (
See Keyword table

create table permission (
See Permission table

The schema I'm presenting here is meant to implement the necessary fields for wftk and those for this to-do manager. The nice thing about SQL, of course, is that properly written SQL interface code can deal with views onto the data (so that you can tack other stuff onto whatever table you like and things won't break.) Don't feel overly constrained by these definitions. (This is turning into my mantra: don't feel constrained by any of my coding decisions!)

Process table
The process table will actually not be used very heavily for to-do management. The only place we care about this table will be in the overview, when we'll use it to group tasks by process (or "project"). Nevertheless, this presentation is also an implementation of the wftk active process repository, so let's take a look at what we'll need in there.
id text,
owner text,
title text,
description text,
started timestamp,
parent_process text,
parent_task text,
definition text,
datasheet text,
status text

OK, let's run down the list and I'll tell you what each of those is for.

Task table
The task table is similarly overloaded. I think I'm getting somewhat carried away about everything I want to attach to tasks, but this will do for a first run.
id text,
process text,
status text,
owner text,
description text,
role text,
queue text,
created timestamp,
sched_date date,
sched_time time,
priority smallint,
complete timestamp,
datasheet text,
subprocess text

These are as follows.
Previous: File organization of todomgr.tcl ] [ Top: To-do manager ] [ Next: Schema for user access ]

This code and documentation are released under the terms of the GNU license. They are additionally copyright (c) 2000, Vivtek. All rights reserved except those explicitly granted under the terms of the GNU license.