Index: openacs-4/contrib/packages/project-manager/sql/postgresql/project-manager-table-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/project-manager/sql/postgresql/Attic/project-manager-table-create.sql,v
diff -u -r1.13 -r1.14
--- openacs-4/contrib/packages/project-manager/sql/postgresql/project-manager-table-create.sql	15 Sep 2003 23:09:56 -0000	1.13
+++ openacs-4/contrib/packages/project-manager/sql/postgresql/project-manager-table-create.sql	30 Sep 2003 12:10:01 -0000	1.14
@@ -1,19 +1,18 @@
 -- TODO:
 -- 
+-- which items in this data model need to use the content repository?
 -- need to add in workflow (for status among other things)
+-- need to take into account acs-rels
 -- add categories to projects
 
 --
--- packages/project-manager/sql/postgresql/project-manager-table-create.sql
+-- packages/test-project/sql/postgresql/test-project-table-create.sql
 --
--- @author jader@bread.com
--- @author ncarroll@ee.usyd.edu.au was involved in creating the initial CR version
--- @author everyone else involved in this thread: http://openacs.org/forums/message-view?message_id=90742
+-- @author jader@bread.com, ncarroll@ee.usyd.edu.au and everyone else involved in this thread: http://openacs.org/forums/message-view?message_id=90742
 -- @creation-date 2003-05-15
 --
 
--- PROJECTS
-
+-- Project repository datamodel.
 create table pm_projects (
 	project_id		integer
 				constraint project_manager_id_fk
@@ -54,82 +53,65 @@
 
 
 
--- ROLES
-
-create sequence pm_role_seq start 3;
-
-create table pm_roles (
+create table pm_project_roles (
         role_id                 integer
-                                constraint pm_role_id_pk
+                                constraint pm_project_role_id_pk
                                 primary key,
         one_line                varchar(100)
-                                constraint pm_role_one_line_uq
+                                constraint pm_project_role_one_line_uq
                                 unique,
         description             varchar(2000),
-        sort_order              integer,
-        is_observer_p           char(1) default 'f'
-                                constraint pm_role_is_observer_ck
-                                check (is_observer_p in ('t','f'))
+        sort_order              integer
 );
 
 
-comment on table pm_roles is '
-  Roles represent the way in which a party participates in a project
-  or task. For example, they could be a manager, or client, or
-  participant.. The sort order determines what order it is displayed
-  in.  The is_observer_p specifies whether they are directly
-  responsible for the task. People not directly responsible will not
-  get email notifications reminding them that tasks are overdue, for
-  example.
+comment on table pm_project_roles is '
+  Roles represent the way in which a party participates in a project. For example,
+  they could be a manager, or client, or participant.. The sort order determines 
+  what order it is displayed in.
 ';
 
-insert into pm_roles (role_id, one_line, description, sort_order) values ('1','Participant','Team members who are responsible for the completion of the project','10');
-insert into pm_roles (role_id, one_line, description, sort_order) values ('2','Manager','Manages the team to complete the project or task on time and on budget.','20');
+insert into pm_project_roles (role_id, one_line, description, sort_order) values ('1','Participant','Team members who are responsible for the completion of the project','10');
+insert into pm_project_roles (role_id, one_line, description, sort_order) values ('2','Manager','Manages the team to complete the project on time and on budget.','20');
 
 
-create table pm_default_roles (
-        role_id                 integer
-                                constraint pm_default_role_fk
-                                references pm_roles
-                                on delete cascade,
-        party_id                integer
-                                constraint pm_default_role_party_fk 
-                                references parties(party_id)
-                                on delete cascade,
-        constraint pm_default_roles_uq
-        unique (role_id, party_id)
-);
+create sequence pm_project_role_seq start 3;
 
-comment on table pm_default_roles is '
-  Specifies what role a person is a part of by default
-';
 
--- PROJECT ASSIGNMENT
-
-create table pm_project_assignment (
+create table pm_project_role_map (
         project_id              integer
                                 constraint pm_project_role_map_project_fk
                                 references pm_projects
                                 on delete cascade,
         role_id                 integer
                                 constraint pm_project_role_map_role_fk
-                                references pm_roles,
+                                references pm_project_roles,
         party_id                integer
                                 constraint pm_project_role_map_user_id_fk 
                                 references parties(party_id)
-                                on delete cascade,
-        constraint pm_project_assignment_uq
-        unique (project_id, role_id, party_id)
+                                on delete cascade
 );
 
-
-comment on table pm_project_assignment is '
+comment on table pm_project_role_map is '
   Maps who is a part of what project, and in what capacity
 ';
 
 
--- TASKS
+create table pm_project_default_roles (
+        role_id                 integer
+                                constraint pm_proj_default_role_fk
+                                references pm_project_roles
+                                on delete cascade,
+        party_id                 integer
+                                constraint pm_proj_default_role_party_fk 
+                                references parties(party_id)
+                                on delete cascade
+);
 
+comment on table pm_project_role_map is '
+  Specifies what role a person is a part of by default, for projects
+';
+
 -- we create two tables to store task information
 -- the information that we keep revisions on is in the 
 -- pm_task_revisions table, the rest is in pm_task
@@ -304,8 +286,6 @@
 );
 
 
--- DEPENDENCIES
-
 -- dependency types
 -- such as:
 -- cannot start until Task X finishes
@@ -342,65 +322,78 @@
                                         on delete cascade,
         dependency_type                 varchar
                                         constraint pm_tasks_const_type
-                                        references pm_task_dependency_types,
-        constraint pm_task_dependency_uq
-        unique (task_id, parent_task_id)
+                                        references pm_task_dependency_types
 );
 
 
--- WORKGROUPS
+-- assignments
 
-create sequence pm_workgroup_seq;
 
-create table pm_workgroup (
-        workgroup_id            integer
-                                constraint pm_workgroup_id_pk
+create table pm_task_roles (
+        role_id                 integer
+                                constraint pm_task_role_id_pk
                                 primary key,
         one_line                varchar(100)
-                                constraint pm_workgroup_one_line_uq
+                                constraint pm_task_role_one_line_uq
                                 unique,
         description             varchar(2000),
+        is_observer_p           char(1) default 'f'
+                                constraint pm_task_role_ck
+                                check (is_observer_p in ('t','f')),
         sort_order              integer
 );
 
-create table pm_workgroup_parties (
-        workgroup_id            integer
-                                constraint pm_workgroup_parties_wg_id_fk
-                                references pm_workgroup(workgroup_id)
-                                on delete cascade,
-        party_id                integer
-                                constraint pm_workgroup_party_fk 
-                                references parties(party_id)
-                                on delete cascade,
-        role_id                 integer
-                                constraint pm_workgroup_role_id
-                                references pm_roles,
-        constraint pm_workgroup_parties_uq
-        unique (workgroup_id, party_id, role_id)
-);
+comment on table pm_task_roles is '
+  Roles represent the way in which a role participates in a task. For example,
+  they could be an assignee, or a watcher. They can be divided up the same way 
+  the organization is broken up, by sales and R and D for example. These terms
+  can be made up to fit the conditions of the local company. Defaults are set
+  up by another table, pm_task_default_role 
+  The is_observer_p specifies
+  whether they are directly responsible for the task. People not directly
+  responsible will not get email notifications reminding them that tasks are
+  overdue, for example.
+';
 
+insert into pm_task_roles (role_id, one_line, description, is_observer_p, sort_order) values ('1','Manager','This person oversees someone responsible for completion of the task','t','10');
+insert into pm_task_roles (role_id, one_line, description, is_observer_p, sort_order) values ('2','Assignee','This person is directly responsible for completion of the task','f','20');
+insert into pm_task_roles (role_id, one_line, description, is_observer_p, sort_order) values ('3','Watcher','This person is interested in developments of a ticket, but not directly responsible for it.','t','30');
 
--- TASK ASSIGNMENTS
 
-create table pm_task_assignment (
+create sequence pm_task_role_seq start 4;
+
+
+create table pm_task_role_map (
         task_id                 integer
-                                constraint pm_task_assignment_task_fk
-                                references pm_tasks(task_id)
+                                constraint pm_task_role_map_task_fk
+                                references pm_tasks
                                 on delete cascade,
         role_id                 integer
-                                constraint pm_task_assignment_role_fk
-                                references pm_roles,
+                                constraint pm_task_roles_fk
+                                references pm_task_roles,
         party_id                integer
-                                constraint pm_task_assignment_party_fk 
+                                constraint pm_task_role_map_user_id_fk
                                 references parties(party_id)
-                                on delete cascade,
-        constraint pm_task_assignment_uq
-        unique (task_id, role_id, party_id)
+                                on delete cascade
 );
 
+comment on table pm_task_role_map is '
+  Maps who is a part of what task, and in what capacity
+';
 
-comment on table pm_project_assignment is '
-  Maps who is a part of what project, and in what capacity
+create table pm_task_default_roles (
+        role_id                 integer
+                                constraint pm_task_default_role_fk
+                                references pm_task_roles
+                                on delete cascade,
+        party_id                integer
+                                constraint pm_task_default_role_party_fk 
+                                references parties(party_id)
+                                on delete cascade
+);
+
+comment on table pm_task_role_map is '
+  Specifies what role a person is a part of by default, for tasks
 ';
 
 
