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.
(with input from Rocael and Dave Bauer who will also be posting with more details)