Forum OpenACS Development: Re: dotWRK: Project Manager
create table projects (
project_id integer
constraint project_id_pk
primary key,
project_code varchar(20)
constraint project_code_nn
not null
constraint project_code_uq
unique,
name varchar(100)
constraint name_nn
not null,
--A project starts off as unapproved. Must be approved before tasks are scheduled.
status varchar(100)
default 'Pending Approval',
mfg_po_number varchar(100),
--The sponsering (paying) entity for this project
manufacturer_id integer
constraint manufacturer_id_nn
not null
constraint manufacturer_id_fk
references manufacturers (manufacturer_id),
--This restricts a project to one company.
--Its usefull because now the company can own the calendar and the survey
--However, if they ever want to share a project between companies it will be hard.
company_id integer
constraint company_id_nn
not null
constraint company_id_fk
references companies (company_id),
team_type_id integer
constraint team_type_id_nn
not null
constraint team_type_id_fk
references team_types (team_type_id),
--For book keeping only. Not used right now. Use team type and shifts.
est_man_hours integer,
--Crew's can't leave during a shift and might do overtime so we collect how many shifts separately.
num_shifts integer
constraint num_shifts_nn
not null,
-- Completion Survey that teams will respond to when they finish each task.
-- This will be from a hard coded master survey at least for the first round.
calendar_id integer
constraint calendar_id_nn
not null
constraint calendar_id_fk
references calendars (calendar_id),
photo_folder_id integer
constraint photo_folder_id_nn
not null
constraint photo_folder_id_fk
references cr_folders (folder_id),
comp_survey_id integer
constraint comp_survey_id_nn
not null
constraint comp_survey_id_fk
references surveys (survey_id),
notes_survey_response_id integer
constraint notes_survey_response_id_fk
references survey_responses (response_id),
department_id integer
constraint customer_departments_fk
references customer_departments (department_id),
store_type_id integer
constraint store_type_id_fk
references store_types (store_type_id),
--Eventually we may need to put work hours in a look up table. For now choices are set in the ad_form.
prototype_pa_album_id integer
constraint prototype_pa_album_id_fk
references cr_revisions (revision_id),
work_hours varchar(100)
);
Project_code and Project_name: This is like short name and pretty name, or Key and pretty name. There tends to be a key that accounting systems and such use for each project.
Status: If I was starting now I would definitely use Workflow for status NOT store it here. I hope to get funding on this project to transition to workflow. I very much believe it can provide value to my client.
mfg_po_number: This is the PO number of my client's customer. Each implementation will have random pieces of information totally specific to that site, or even that project. It would be far better to have a general way of collecting, editing and displaying them then to put fields in the data model like this. Work on making attributes more functional perhaps?
Department_id: This is a nice obscure one. It refers to a set of look-up tables that model our client's customer's internal set of departments, and sub-departments that they use for keeping track of their inventory. If Categories were available when we wrote this I think that is what we would have used. Another important point for dotProject is each project may be classified on totally different category trees for each different type of stake holder.
Manufacturer_id, Company_id: Customer_id. For this site these are the stakeholders in the project. In We represent them all as groups and each has its own table with some extra info. One issue we have already run into is that a Project needs to be associated with more then one Manufacturer, or Company. For dotProject we should probably use rels to relate projects to their stake holders. This will should be the most flexible solution and will help dotProject use the toolkit.
Team_type_id: In this case each project requires a certain type of team (2-man, 4-man, reset team, etc.). The team types are stored in a look-up table. I think if we were doing it over I'd use categories for this.
Estimated Man-hours, num-shifts: In this use case they frequently schedule assuming overtime. Work rules and the sorts of estimates needed are going to vary a lot from industry to industry. For instance if this system were to expand we might end up modeling the overtime laws in each state. We may want a more general solution then trying to put all the combinations into the data-model.
Work_hours: This is currently modeled as a look-up table of things like 5pm to 12am, 7am to 4pm etc. A bad late patch for a requirements change. Something we should think about is how to model "when is work allowed to take place". There is both the issue of working hours (only days, only nights) and the issue of work days (what holidays do you observe, is it a project or task that can ONLY be done on weekend and holidays).
Calendar_id, photo_folder_id: One of the important roles of dotProject will be to provide a framework that lets you elegantly use all of the functionality of the toolkit. In this instance we are using Calendar, Survey and Photo Album. Using one instance of each package then putting the id in the model like this is very much WRONG in my opinion. It was a fast simple solution implemented to get results quickly before dotLRN 1.0 was released. dotProject should follow the dotLRN model and extend it to use rels to relate objects such as calendars, photo albums, surveys to one or more projects and tasks.
Use of Calendar: I personally think acs_events should be used to store all time related info. I suggest considering moving the project start-time and end-time to the project's calendar. I think once we start apply dotProject to real use cases we'll see an explosion of date related info that needs to be stored. For instance for our use case we need to store Material Arrival Date. A few other common dates would be, billing dates, current .contract start and end dates, no work days, meetings, deliverables etc. We use calendar items with specific item_types to keep track of project dates. We never end up using any of the pages in the calendar package but we use the api's and modified forms of the widgets extensively. It doesn't' make sense to duplicate date handling functionality.
Survey: We used survey in two different ways. The first is each project has a list of documents that are supposed to be associated with it. For instance, building plans and material lists. These need to be named and displayed consistently for all projects and File Storage provides too open ended an interface and makes it too likely that different people would name the documents differently. Thus we created one survey with a bunch of file type questions and each project has one response to this survey. It was quick and easy and survey has nice tcl api's.
The second (and probably more appropriate) way we used survey was to create one "completion survey" for each project with each task creating one response when the task is marked complete.
Photos: An extremely important part of this project was collecting and displaying photos of completed tasks. We used one folder per project with each task creating an album. One interesting requirement is in addition to one album per task, there also must be an album of "proto-type" pics that are at the project level and show what the end result is supposed to look like. Use of rels to relate photo albums and folders to projects and/or tasks would be a much more elegant and scalable way to use the tool kit then storing all the ids in the projects table.
I will try to post our Task data model and a similar discussion soon. Please don't take these data models as a starting point, rather to use them as an example of some of the issues in a real life use case.
The things I want to emphasize:
- Have dotProject be a tool for organizing packages and never repeat existing ACS functionality.
- use calendar to manage date information.
- use workflow to manage status information, including auditing when status changes and who makes the change.
- never put a constraint to users, always use parties so there is flexibility whether its a user, person or group.
- Mapping tables and look-up tables are not that scalable, expensive to maintain and requires that you have programmers for each new field you want to add, and each new filter on reports. I recommend dotProject minimizes these and work instead on improving the toolkits functionality and flexibility in areas such as rels, attributes, categories, list builder etc.
-Caroline
(with input from Rocael and Dave Bauer who will also be posting with more details)
Here are some thoughts in response to your posting:
-- First of all, every company is going to have a list of information they will need to associate with a project. For example, our company associates recipes with each project. Some companies may associate products with projects. I think this need is going to be pretty universal.
Also, there will be other information, such as PO numbers, or customer codes, or Sales regions, etc...
This is an absolutely critical element of dotProject, I think. We have to find a flexible solution to this. For us, this is one of the main reasons we're building it ourselves instead of using something off the shelf (there are other reasons too, however).
Here are a couple of brainstorms to this idea:
- we set up a table of attributes, as you describe. Allow people to set up attributes for different types of tables, and then allow them to actually set up the values. This is pretty flexible. The attributes act as additional columns they can add information into.
- this is not mutually exclusive, but we could also have some sort of service contract for queries that are run when you look at a project page. For example, at our company, we want to see all the products associated with that project.
A way this could work is we could have a set of project_types:
create table project_type (
-- info on project type
);
Each project has one (or more?) project_types. Let's say one or more for the sake of flexibility. We can categorize projects into many types then.
We also create a table that tracks the service contracts:
create table project_type_service_contracts (
project_type_id integer constraint dfsafdsaf
references project_type(project_type_id),
tcl_procedure_name varchar(200),
tcl_sortby varchar(100)
);
Then each project_type could have a set of associated code that would be run for it. Potentially several pieces of code.
For example, let's say we have two project types:
* Customer projects
* Research and development projects
For all customer projects, we want to display a list of relevant products.
Let's say we have a table, called customer_products. It has a column called project_id that references the relevant Project.
We set up a Tcl function called customer_product_list_by_project(project_id). It returns a list of products for that project, in an HTML table.
We insert a row into project_type_service_contract, and then whenever we look at the project page, you see the relevant entries.
Does this make sense? Does it seem useful to other people? The nice thing about it is that it's only used if necessary.
Of course, this does mean that someone who can insert rows into your table can root your computer. But I think that's probably true anyway. ?
I'll finish reading your posting, Caroline. This is just one thought I had had that I hadn't posted about much.
Yes I think this is why we need to think in terms of scheduling resources. Resources are going to be available sometimes and not others. MS Project does a good job with this, and I think we can do something similar.
Can you describe how rels work a little more? I guess I'm unfamiliar with them. Or refer to where I could learn more about them?
Caroline suggest we use acs_events to keep track of all date related activity. I'm willing to do this, I guess, but that's another piece of software I'm not familiar with. Can you describe a little more what advantages acs-events would give us?
I like the idea of completion surveys for projects. This should probably be optional, however.
Interesting thoughts about photo-albums. Makes me very curious about rels.
Good point about users vs. parties
I'm not sure I completely understood your point about mapping points and lookup tables. Where are these used? Is this something one of the data models?
Thanks for all of your points, Caroline. It's very helpful to have real-world examples to work from (and your real-world experience in creating it). I look forward to Rocael and Dave's postings too.
First, he suggested using <include>able templates instead of the tcl procedures. That makes so much sense -- I wish I had thought of that.
Second: "there are two things i think that are most important. workflow, so if you need to complete a survey, workflow would make sure its done before advancing to the next status."
Third: "there is one other way to display stuff, using the CR you could have a item_id, and an associated template. different templates can be used depending on the context."
He said he'll post later with some more detailed comments.
