Ppo

From PTAGISWiki

Talk:Design_People,Project,_Organization_Logical_Data_Model

Contents 1 People Projects and Organizations 1.1 Overview 1.2 Goal 1.3 Objectives 1.4 Key Assumptions 1.5 Current Conditions 1.6 Use Cases 1.7 Key Interfaces 2 Regional Requirements 2.1 Requirement 1: ISRP Final Review 2002 2.2 Requirement 2: Generic Coordinator ID for Organization or Project 3 Current Processes for Managing User Contact Meta Data 3.1 New Account Setup Step 1 3.2 New Account Setup Step 2 3.3 Manage Account Step 1 3.4 Manage Account Step 2 3.5 Administrative Account Management Step 1 3.6 Administrative Account Management Step 2 3.7 LDAP Issues 4 PPO Road Map From Initial PPO Discussions 4.1 Phase I - Development 4.2 Phase II - Verification 4.3 Phase III - Deployment 4.4 Phase IV - Support & Maintenance 5 Links 6 Reader Comments

People Projects and Organizations

Overview

In 2002, the Independent Scientific Review Panel (ISRP) raised a concern related to PTAGIS metadata. Their concern that Principle Investigators (as identified by the Coordinator ID encoded in a Tagging, Release or Recapture file) may retire or die which may result in loss of metadata. The PIT Tag Steering Committee (PTSC) expressed an interest in overloading the Coordinator ID so that it would relate to a long term project, so that data technicians would not have to be concerned with tracking Principle Investigators Coordinator IDs when they create their tagging files.

The PTAGIS project has developed a model that will allow the PTAGIS system to track changes to people associated with projects over time in order to address these two primary concerns.

Furthermore, since people information (name, address, phone number, email address, etc.) is used widely in other PTAGIS subsystems (Tag Distribution and Inventory, Interrogation Site Support, address lists, etc.) the design of the model allows the flexibility to associate people with more than one office (as is the case with some PSMFC employees that work from NOAA offices, or Oregon Department of Fish and Wildlife people to work from Eastern Oregon State University offices, etc.)

Development of this model also provides a simpler mechanism to assign and track people with various roles that might change over time.

The goal of the model is to provide flexibility to track people, projects and their organizations over time and to improve the consistancy of existing people, organization and process information across the PTAGIS subsystems.

Goal

Implement a consistant mechanism to track PTAGIS contacts and responsibilities, mark and recovery projects and associated organizations over time and allow web based administration of this mechanism by end-users and PTAGIS administration.

Objectives

Key Assumptions

• People may be assigned to more that one organization.
• Organizations may change names.
• People may have more than one set of contact information (main office, field office).
• People have a primary mailing address.
• It is desirable to have a single, master list of people contact information.
• It is desirable to have a single, master list of organization information.
• People can assume various roles that may change over time.
• The set of and types of PTAGIS roles changes over time as the PTAGIS system matures.
• PTAGIS supports not only BPA Projects, but projects sponsored by or funded by other organizations.
• ...more...

Current Conditions

PTAGIS has many tables that track coordinator ID information:

• PAB_CONTACTS contains up-to-date information on coordinators that are identified in the VALID_TBL, and the unique numeric key identifier is used in Dave's SxC subschema.
• COORD ID domain in VALID_TBL
• Information in the COORD_ID_INFO table
• Associations of TDI_SPONSORS and COORD_ID's in the TDI_SPONSOR_COORDINATOR table.

Use Cases

Key Interfaces

Regional Requirements

Requirement 1: ISRP Final Review 2002

ISRP Final Review , ISRP 2002-14 Recommendation: Fund Date: Nov 5, 2002 [There are no budget numbers associated with this review.] Comment: Fundable (qualified). Agree with CBFWA that this is a Corp Program. However, the project should not be funded until the sponsor and the Columbia Basin PIT Tag Steering Committee develop and implement required procedures for storage and retrieval of full metadata on PIT tagged fish before records are stored in PTAGIS. Given the urgent need for the project to include metadata, the budget should not be reduced and may need to be increased.

The ISRP acknowledges that some metadata are available in the record stored in PTAGIS for a given PIT tagged fish. However, the metadata are insufficient due to the fact that much of it is optional and the fields are too small to include full information concerning how a given fish has been treated prior to release (e.g., the treatment history of fish reared under a NATURES program or genetic background in a captive breeding program). There should be a requirement to:

Tie the record (tagging and detections) for each PIT tagged fish to the verified migration 
path of the fish and to a published or electronic permanent document that describes the capture
or rearing history and treatment of the fish. 

At the present time, it is our understanding that the initials of the principal investigator responsible for tagging a fish are stored in the record and one must contact that person to obtain required metadata on a tagged fish. This procedure may have been adequate given the short time that PIT tags have been in use, but in the not too distant future the principal investigators are going to retire or die and the required metadata will be lost.

The ISRP recognizes that this lack of adequate metadata is not the full responsibility of the sponsor and that ownership of data is a concern. However, the verified migration path of a PIT tagged fish and the published documents (or hatchery reports, progress reports, etc.) are absolutely required to ensure maximum long-term scientific value of the information in the database. Perhaps the required documents could be stored in the StreamNet library and then be made available to the public within a limited amount of time. This problem has been pointed out in previous ISRP and ISAB reviews and it appears that little if any progress has been made in resolving the issues.

If funded, this project should be coordinated with other monitoring projects to ensure compatibility of objectives, common methods and protocols. This coordination could be accomplished under the favorably reviewed CBFWA proposal #35033.

Requirement 2: Generic Coordinator ID for Organization or Project

Subject:
Re: Coordinator ID
From:
Brian Jonasson <bjonasso@eou.edu>
Date:
Fri, 17 Dec 2004 11:37:03 -0800
To:
"Buettner,Edwin" <ebuettner@IDFG.STATE.ID.US>
CC:
"Marsh, Doug" <doug.marsh@noaa.gov>, "Morrill, Charles" <cfwdfw@aol.com>, "Stein, Carter" <carters@psmfc.org>, "Hoffman,
Thomas" <Thomas_Hoffman@r1.fws.gov>, "Leth,Brian" <bleth@IDFG.STATE.ID.US>

The generic coordinator ID would probably work well, as it would eliminate the dead end " No longer works for this
organization"
on the PTAGIS Data Coordinators web page.

Brian

Buettner,Edwin wrote:

>Generally, a biologist position for a long term project stays in one office so maybe the general contact info for that
office would work.  As an example I'm thinking again of the Chinook Hatchery Evaluation, which is located a Nampa
Research, so the contact info would be that for the Nampa Research office.   Would that type of scenario work?.  What are
your thoughts?....ED
>
>	-----Original Message-----
>	From: Brian Jonasson [1]
>	Sent: Fri 12/17/2004 9:54 AM
>	To: Buettner,Edwin
>	Cc: Marsh, Doug; Morrill, Charles; Stein, Carter; Hoffman, Thomas; Leth,Brian
>	Subject: Re: Coordinator ID
>
>
>	It sounds like a good idea, but how would the contact information read for the generic Coordinator ID?
>
>	Brian
>
>	Buettner,Edwin wrote:
>
>		Hi All,
>
>		One of the IDFG Bios just talked to me about using a generic Coordinator ID for long term projects like
hatchery evaluations.  His reasoning is that biologists move through that position frequently and he was wondering if it
would be better to just have a generic Coor ID tied to the project rather than to an individual.  An example would be IHC
for Idaho Chinook Hatchery Evaluation and IHS for Idaho Steelhead Hatchery Evaluation.  What do you think of this
idea?.....ED
>
>
>
>
>
>

Current Processes for Managing User Contact Meta Data

The PTAGIS Web Site collects user contact information to provide support for access to the web sites advanced features.

Data about the user is stored in a Lightwieght Directory Access Protocol (LDAP) directory. Strategically, the PTAGIS project anticipated that it would be desirable for the PSMFC web infrastructure to support a single-sign-on mechanism, and a 'corporate' directory of information about people (employees, customers, organizations, etc.) that could be managed, and maintained at a corporate level. However... {could write more about the problems and issues related to ldap usage as a database}.

New Account Setup Step 1

• driven by ptagis/createAccount.jsp
• uses the beans LDAPUserMgr and LDAPGroupMgr

      // Create the account
      if(!userMgr.doesUserExist(user_name))
      {
         out.println(" "); out.flush(); /// to prevent browser timeout...
         String password = createPassword();
         LDAPUser user = userMgr.createUser(user_name);
         user.setAttribute("cn", first_name + " " + last_name);
         user.setAttribute("givenname", first_name);
         user.setAttribute("sn", last_name);
         user.setAttribute("o", org);
         user.setAttribute("telephonenumber", phone);
         user.setAttribute("telephonenumberext", phone_ext);
         user.setAttribute("facsimiletelephonenumber", fax);
         user.setAttribute("mail", email);
         user.setAttribute("physicaldeliveryofficename", dept);
         user.setAttribute("title", title);
         user.setAttribute("postaladdress", addr1);
         user.setAttribute("street", addr2);
         user.setAttribute("l", city);
         user.setAttribute("st", state);
         user.setAttribute("postalcode", zip);
         user.setAttribute("c", country);
         user.setAttribute("emailnewsletter", wantNews);
         user.setAttribute("notifyminimon", notifyMMIS);
         user.setAttribute("notifyspec", notifyPSD);
         user.setAttribute("notifyp3", notifyP3TS);
         user.setAttribute("userPassword", password);
         user.setAttribute("passwordastext", password);
         user.setAttribute("nsaccountlock", "false");
         user.setAttribute("inetuserstatus", "enabled");
         user.setAttribute("myfilesquota", "10000000");
         user.setAttribute("myregtagsquota", "50000");
         user.setAttribute("myreportsquota", "10000000");
         userMgr.addUserToLDAP(user);
         LDAPGroupMgr gm = new LDAPGroupMgr(PTAGIS_PROPERTIES);
         if(gm != null)
         {
            LDAPGroup g = gm.getLDAPGroup("Registered User");
            if(g != null) g.addMember(user);
         }
         out.println("<BR>A new account has been created for " + user_name);
         out.println("<BR>An email has been sent with your initial password.");

New Account Setup Step 2

Manage Account Step 1

• driven by ptagis/updateAccount.jsp
• uses bean LDAPUserMgr

      u = userMgr.getLDAPUser(user_name);^M
                String pwd= (String) session.getAttribute("password");^M
      u.setAttribute("userPassword", pwd); ^M
      u.setAttribute("cn", first_name + " " + last_name);^M
      u.setAttribute("givenname", first_name);^M
      u.setAttribute("sn", last_name);^M
      u.setAttribute("o", org);^M
      u.setAttribute("telephonenumber", phone);^M
      u.setAttribute("telephonenumberext", phone_ext);^M
      u.setAttribute("facsimiletelephonenumber", fax);^M
      u.setAttribute("mail", email);^M
      u.setAttribute("physicaldeliveryofficename", dept);^M
      u.setAttribute("title", title);^M
      u.setAttribute("postaladdress", addr1);^M
      u.setAttribute("street", addr2);^M
      u.setAttribute("l", city);^M
      u.setAttribute("st", state);^M
      u.setAttribute("postalcode", zip);^M
      u.setAttribute("c", country);^M
      u.setAttribute("emailnewsletter", wantNews);^M
      u.setAttribute("notifyminimon", notifyMMIS);^M
      u.setAttribute("notifyspec", notifyPSD);^M
      u.setAttribute("notifyp3", notifyP3TS);^M
      //u.writeStateToLDAP();^M
                userMgr.updateUser(u);^M
      out.write("<BR>Account updated for " + user_name);^M

Manage Account Step 2

Administrative Account Management Step 1

• driven by ptagis/admin/manageAccount.jsp
• uses beans

Delete
         LDAPUser u= userMgr.getLDAPUserFromDN(user);^M
         userMgr.deleteUser(u);^M
Update
      userMgr.updateUser(u);^M
List
   String[] uid = userMgr.listAllUIDs();^M

Administrative Account Management Step 2

LDAP Issues

In SAIC's implementation of the PTAGIS web site requirements, the LDAP implementation was customized to support additional, non-standard attributes. For example, the LDAP was customized to add one field for each role that users could assume in any of the PTAGIS processes. There are other issues with the LDAP

PPO Road Map From Initial PPO Discussions

In its final form, the PPO schema must satisfy competing demands of completeness, practicality, and compatibility with existing systems. It must be complete enough to accommodate real world complexities, when these confront the PTAGIS Project. For example, many Project participants may colloquially be regarded as "working for NOAA". However, if PPO is to deliver on its requirement of providing reliable and useful mailing addresses for PTAGIS participants, it requires the sophistication to model the fact that NOAA contains numerous sub-agencies - e.g. the NOAA Northwest Fisheries Science Center (NWFSC) and the NOAA Western Administrative Support Center (WASC) - and each of these may have multiple offices - e.g. NWFSC's Newport Research Stations and Manchester Research Station - each with its own address. To provide correct address information, PPO's assignment of persons to organizations must incorporate knowledge of the specific office(s) at which each individual works in the real world.

At the same time, the PPO schema - and the business rules defining permissible operations upon its tables - must be practical in the sense of balancing the benefits to be derived from PPO content against the initial and on-going costs of data collection and maintenance. Consider, for example, what might be required to populate the PPO project table with information describing all of the "tagging projects" that have been conducted from PTAGIS inception. Theoretically, it would be possible to detail every funding source and administrative sponsor for each fiscal year during which a particular researcher was involved, and model this involvement as a succession of distinct projects, each with its own activities, locations, and complement of data files. From the standpoint of practicality, however, much of this detail may not be available; and even if it were, the benefits of having it in the database might be too small to justify the effort of entering the data in the first place. Accordingly, the PPO schema - and associated business rules - must offer a practical alternative; for example, establishing a single project entry to represent the researcher's involvement with PTAGIS from its inception and on into the future.

To ensure that the as-built result strikes an acceptable balance among these competing demands, PPO implementation and deployment will be undertaken incrementally, in the following phases:

Phase I - Development

Phase I has been completed. During this phase the PPO logical data model was developed, based primarily on discussions between Doug and Carter. The logical model has been equipped to fulfill the request by PTSC for a means to track the history of custodial ownership of data sets - for example tagging files or interrogation files - contributed by researchers participating in the PTAGIS Project.

This implies a requirement for tracking relationships between people and organizations, between organizations and the PTAGIS project, and between organizations that exist today and their predecessors. In the course of this work, content of the LDAP database and TDI tables has been studied to gain practical insight on information of the sort to be tracked by the PPO schema, and the kinds of changes that have occurred with respect to people, organizations, and their inter-relationships during the lifetime of the PTAGIS project.

Phase II - Verification

Phase II is in progress. During this phase, Use Case Narratives are being prepared. Through practical, high-level examples, these will serve as "textual walk-throughs" to:

• Promote familiarity with the PPO schema and the Entity-Relationship notation used to describe it
• Test the hypothesis that the PPO tables do accommodate all of the necessary information concerning persons, and their relationships with projects and organizations
• Reveal business rules that govern content of the PPO tables
• Document areas of overlap between PPO and existing data stores
• Identify any requirement for new user interfaces to PPO tables, or for behind-the-scenes interfaces between the PPO tables and existing automated systems.

Also during this phase, meta-data describing the proposed PPO schema have been entered into Extended Data Dictionary (XDD) tables of the 'g2_ptagis3' database on 'sockeye'. XDD meta-data serve as input for numerous tools facilitating physical implementation of any new PTAGIS3 sub-schema, through:

• Generation of detailed reports describing the intended purpose of each new table and column, including Ingres data-types, storage structure, and keys
• Generation of SQL table create- and modify-scripts
• Generation of SpringMVC Java / JSP components for a web-app to perform Create-Read-Update-Delete operations on tables of the new sub-schema
• Support of context-sensitive Help screens describing tables and columns manipulated by the CRUD web-application

The SpringMVC 'XDD' web-app provides an easy means for manipulating meta-data in the PTAGIS Extended Data Dictionary. During the verification phase, reports on the PPO sub-schema will be reviewed, adjustments - such as adding or removing columns, or changing column data types - will be made using 'XDD' or other tools, as appropriate.

When it appears that the proposed design will fulfill its objectives, create- and modify-scripts will be generated and the tables will be created in the 'p3_dev' development database on 'sockeye'. This trial implementation of the PPO schema will be populated with data from existing, disparate repositories. Trial queries will be developed to:

• Address the needs expressed in the original PTSC request - i.e. tracking custodial ownership of a 'data set' through time
• Demonstrate the schema's ability to track and report on changes in relationships between people and organizations during the history of the PTAGIS project to date
• Demonstrate the schema's ability to deal with 'interrogation super-sites'
• Quantitatively describe the relationship between PPO and TDI tables that touch on similar real-world objects
• Demonstrate the ability of PPO to serve as a replacement data-source for reports currently extracting content from 'valid_tbl' (and possibly other tables in the PTAGIS3 database?)

It is anticipated that this work will involve some form of 'structured walk-throughs' with additional members of the PTAGIS staff. Phase II completion is defined by successful accomplishment of these steps.

Phase III - Deployment

Phase III will result in the PPO tables being created in the 'ptagis3' database on 'sockeye' (development) and 'blueback' (production). Reports and processes currently reading 'people-project-organization' content from 'valid_tbl' will be revised to obtain this content from the PPO tables.

The emphasis in Phase III will be on 'taking the easy steps' in a longer-term effort to move away from 'valid_tbl' and similar legacy data sources. Initially, for example, no attempt will be made to integrate TDI with PPO, or with the LDAP database. While both of these are desirable goals, their implementation will be tackled separately in priority order with other project needs.

Phase IV - Support & Maintenance

If desired, development of a PPO web-app for maintaining content of the PPO tables would be tackled in Phase IV. This work would be planned, prioritized, and coordinated with efforts to integrate PPO with TDI and the LDAP database.

Links

Click here for a detailed report on the proposed PPO sub-schema, based on meta-data in the 'g2_ptagis3' (sockeye) Extended Data Dictionary tables.

NOTE 

Since the meta-data report was generated, keying strategies have been refined. Though the Logical ERD and the meta-data report indicate that integer artificial keys (e.g. org_id, office_id, person_id) will be used exclusively, work is now in progress to incorporate (redundant) human-readable keys (e.g. org_code, office_code) in addition to the integer keys, to facilitate browsing and ad-hoc querying. - Doug 12:57, 23 October 2006 (PDT)

Here are links to use-case narratives for the PPO sub-schema:

• New 'person' Use Cases
• New 'project' Use Cases
• New 'organization' Use Cases
• New 'office' Use Cases

Here are links documenting evolution of the PPO data model:

• Version 1.1 - Minor change to logical ERD; see Reader Comments below
• Version 1.0 - First implementation candidate
• Version 0.4
• Version 0.3
• Version 0.2 - Earliest PDF version

Reader Comments

Please enter your signed comments on the most recent version of the PPO data model, below. Put your comments at the top of the comment list, including version number in 'definition list' format.

Ver 1.1 

I removed bpa_proj_nbr from the project table and added org_proj_nbr to the org_project_role table. This reflects the fact that the "project number" we're storing actually pertains to the relationship between project and organization, rather than the project itself. With the revised model, org_proj_nbr will contain the BPA project number, when the org_project_role record associates BPA with the project. When an organization other than BPA is being associated with the project, org_proj_nbr may be empty, or may contain the identifier by which this organization tracks its involvement with the project: Doug 17:22, 10 October 2006 (PDT)

Ver 0.4 

Here are my thoughts on modeling organizational changes and their integration with the PPO sub-schema: Doug 16:33, 26 September 2006 (PDT)

Ver 0.4 

(A) Organizations change in name and structure through time. For example WDW and WDF merged to become WDFW (Washington Dept of Fish and Wildlife); DestronFearing was purchased by DigitalAngel. The model lacks a means for tracking such changes. (B) A person may be affiliated with multiple organizations concurrently. The model accommodates this fact, however we should consider adding a 'person.remarks' attribute to help steer users to the most appropriate contact phone number or e-mail address when, for example, they're trying to contact the 'owner' of a tagging file. Alternatively, we could add org_id, office_id to project_activity_role, explicitly associating the 'data owner' role with the appropriate contact information.: Doug 10:11, 25 September 2006 (PDT)

Ver 0.4 

Here are my thoughts on interrogation super-sites and their integration with the PPO sub-schema: Doug 12:03, 22 September 2006 (PDT)

Ver 0.4 

This version associates 'DMM' with a 'project_activity', and improves the modeling of relationships between projects and organizations: Doug 17:06, 21 September 2006 (PDT)

Ver 0.3 

This version works with Carter's idea that 'DMM' should be modeled as a 'project': Doug 16:37, 18 September 2006 (PDT)

Ver 0.2 

Here's what I think now: Doug 10:19, 12 September 2006 (PDT)

Ver 0.2 

This is the first sample comment: Doug 10:06, 12 September 2006 (PDT)