This is the table definition for our client site. As promised I'll discuss all the things we would do differently if we were starting now and doing a general system.

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 it’s 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)

About work_hours:

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.