Forum OpenACS Development: Re: dotWRK: Project Manager

I think the next item on our to-do list is developing a data model. Several people have said they're working on this, but apparently have not gotten to the point where they feel comfortable sharing their work.

My standards are pretty low for sharing work (so this is a little sloppy), but it might move the conversation along a little bit.

First, some caveats:

- I didn't tie things much into the object system. Of course we would do that, however.

- I have not yet added the following
  * workflow
  * content repository
  * constraints with pretty names
  * etc..

- this is just a dirty data model for discussion.

--------------

--
-- packages/dot-project/sql/postgresql/dot-project-table-create.sql
--
-- @author mailto:jader@bread.com
-- @creation-date 2003-05-07
-- @cvs-id $Id$
--

-- use categories package to keep track of types of projects

-- use categories to keep track of the status of the project too?
-- closed projects, research projects, in-progress projects?

create table dp_project (
    project_id        integer
                constraint dp_project_id_fk
                references acs_objects(object_id)
                constraint dp_project_id_pk
                primary key,
    -- for subprojects
    parent_project_id      integer
                references dp_project,
    title              varchar(255)
                constraint dp_project_title_nn
                not null,
    goal                varchar(4000),
    description         varchar(4000),
    start_date          timestamptz,
    end_date        timestamptz,
    ongoing_p        char(1) default 'f'
                constraint dp_project_ongoing_p_ck
                (ongoing_p in ('t','f'))
);

-- could be called project template

create table dp_project_plan (
    -- contains all the items project does, and perhaps a few more.
    -- can be copied to make a project.
    -- hmmm, they should then be categorized as having come from that
    -- template
);

-- slack time be always computed, and therefore not in the data model?

create table dp_task (
    task_id        integer        primary key,
    parent_task    integer        references dp_task,
    description    varchar(4000),
    -- dates are optional, because it may be computed in reference
    -- to all other items
    start_date    timestamptz,
    end_date    timestamptz,
    deadline    timestamptz,
    estimate_hours_work    number,
    estimate_days_work    number,
    -- these are used for PERT type charts, optionally
    estimate_hours_work_min    number,
    estimate_hours_work_max number,
    estimate_days_work_min    number,
    estimate_days_work_max  number
);

-- use ACS groups to make the roles of various users, or use roles?
create table dp_role (
    -- description of roles (manager, etc..)
)

create table dp_task_assignee (
    task_id        integer        references dp_task,
    user_id        integer        references person
    role_id        integer        references dp_role
);

create table dp_task_dependency_type (
    dependency_type_id    integer        primary key,
    description        varchar(200);
);

-- would add in start_to_start, start_to_finish, finish_to_start,
-- finish_to_finish

create table dp_task_dependency (
    task_id        integer        references dp_task,
    parent_task_id    integer        references dp_task,
    task_type    integer        references dp_task_dependency_type
)

-- processes are templates for a set of tasks. For example: deploy website.

create table dp_process (
    -- not defined yet
)