Database schema of the task list

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.

• owner: the user who owns the process.
  There are two basic ways that processes get created; either the wftk engine creates them when a definition is activated, or a user creates a process manually using the to-do manager. In the latter case, the user's id is used as "owner"; in the former, we'll see what makes sense. In some cases, it makes sense for the initiating user to be the owner, but if a process is initiated anonymously (say, in response to an HTTP form submission) then the owner of the process definition will probably be used. We'll see what makes sense later.
  
• title, description: short and long description of the project for reporting.
  For a manual process, these are obvious. For a wftk-initiated process, they may be superfluous. Again, only banging on a prototype will answer that question. Certainly we'll need a title in place in order for the to-do manager and other UI components to have something to display. I certainly don't want to have to retrieve the definitions of all active processes in order to create an activity report, for instance.
  
• started: date the process was created.
  Enough said. Obviously applies to both categories of process.
  
• parent_process, parent_task: parent information.
  If this is a subprocess, then it might require different handling. Certainly when it completes, then its parent task must also be completed. The task ID would logically be unique, but I have a nasty habit of programming defensively.
  
• definition: location (URL, presumably) of the process definition.
  This is only meaningful in the context of wftk, of course. The process definition is used by the wftk core engine to determine what comes next when a task is completed or rejected.
  
• datasheet: location of the datasheet for the process.
  The datasheet module is of course a wftk module -- but could be used quite nicely independently of wftk. It's a place to stash data associated with a process. (Note that the task also has a datasheet location. By default these locations will be identical.)
  
• status: state of the process.
  I guess pending, active, completed, aborted would be logical states. Anybody have any suggestions?

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