Database FAQ and requirements

Basics

Object Identity

Each object (e.g. person, task business, etc) in P.NET has a unique object identity, and has an entry in the PN_OBJECT table, which also lists its object_type. Objects of the same type are stored in a type-specific table (e.g. PN_TASK, PN_BUSINESS) and the primary key of each of these table references an object id in the PN_OBJECT table. The types of objects are listed in the PN_OBJECT_TYPE table, which currently contains entries for the following object types:

address	agenda_item	application	baseline	bookmark	brand	business
calendar	configuration	deliverable	discussion_group	doc_container	doc_space	document
document_version	domain_value	enterprise	event	facility	form	form_data
form_domain	form_domain_value	form_field	form_filter_value	form_list	gate	group
license	meeting	methodology	news	person	phase	plan
portfolio	post	process	project	scheduled_subscription	subscription	task
user_domain	workflow	workflow_envelope	workflow_envelope_history	workflow_envelope_version	workflow_rule	workflow_step
workflow_transition

Spaces

associated with them. A space represents a collection of resources (e.g. people, data, tools) associated with the object. The resources associated with a space are represented by variety of tables of the form PN_SPACE_HAS_xxx (e.g. PN_SPACE_HAS_PLAN, PN_SPACE_HAS_PERSON, etc.)

There's a 1-1 relationship between spaces and their corresponding objects, and there are actually in the middle of being combined. So

• PN_PERSON (with PK PERSON_ID, also an OBJECT_ID) represents both a person and it’s corresponding space
• PN_PROJECT_SPACE (with PK PROJECT_ID, also an OBJECT_ID) represents both a project and its corresponding space

Businesses and business spaces have not yet been combined. There is both a PN_BUSINESS object and a PN_BUSINESS_SPACE object. The PN_BUSINESS_SPACE object has a foreign key to the corresponding business.

Note: PN_BUSINESS_SPACE has a SPACE_TYPE field. Its purpose is currently being investigated.
Note: There's a table PN_OBJECT_SPACE, with PK (OBJECT_ID, SPACE_ID). Its purpose is currently being investigated; it is believed to be obsolete.

Spaces may have subspaces. In particular, both a business space and a project space can have subspaces which are projects. This hierarchy is represented by the table PN_SPACE_HAS_SPACE.

Persons & Spaces

A project also has a corresponding space, so PN_SPACE_HAS_PERSON identifies the persons assigned to a project. The table PN_PERSON_ALLOCATION (with PK SPACE_ID, PERSON_ID, ALLOCATION_ID) at one time was used (and may again be used in the future) for managing partial allocations of persons to projects, but is not currently used.

PN_PERSON_PROPERTIES associates space and context-specific property/values pairs with persons.

Projects, Processes, Phases, Tasks & Persons

Projects may have multiple processes, represented by PN_SPACE_HAS_PROCESS (remember that a project is represented by its space)

Processes are divided into phases; the table PN_PHASE contains a column PN_PROCESS, which identified the process associated with that phase.

Phases are gated. The table PN_GATE identifies gates, and contains a column PN_PHASE, which identifies the corresponding phase.

Phases are divided into tasks. The table PN_PHASE_HAS_TASK represents that relationship.

Tasks are assigned to persons. This is represented by the table PN_ASSIGNMENT, which more generally associates objects (OBJECT_ID) with persons (PERSON_ID) in the context of a space. Tasks are objects, so PN_ASSIGNMENT identifies the tasks (identified by OBJECT_ID) associated with a person (PERSON_ID) (The corresponding space will be the project space associated with the task (by traversing from task to phase to process and then project space)

Portfolios

A portfolio is a collection of spaces, though in practice, portfolios only contain project spaces (i.e. projects). PN_PORTFOLIO_HAS_SPACE identifies the spaces associated with a portfolio.

A portfolio can be given a name (a column of PN_PORTFOLIO) and associated with a space.

• A portfolio can be associated with a person, indicating some collection of projects that are private to that person.
• A portfolio can be associated with a business, indicating some collection of projects that are shared by all people associated with the business.

In either case, the relationship is represented by PN_SPACE_HAS_PORTFOLIO

Note: PN_PORTFOLIO has a field PORTFOLIO_TYPE. Its purpose is currently being investigated.
Note: PN_BUSINESS_SPACE has a field COMPLETE_PORTFOLIO_ID. Its possible that this represents the projects associated with the business, although this is seemingly also represented by PN_SPACE_HAS_SPACE. This is being investigated.

User & Group Based Access Control

PN_USER holds the users who can be granted access to objects. Every PK of PN_USER is also a key of PN_PERSON.

PN_GROUP represents groups of users. The users associated with a group are represented by PN_GROUP_HAS_PERSON. Each group has an owner, identified by the field PRINCIPAL_OWNER_ID.

Each user is associated with at least one group -- their principal group -- which only has that user associated with it. Principal groups are identified by the field IS_PRINICPAL in PN_GROUP.

Groups may form a hierarchy, represented by PN_GROUP_HAS_GROUP.

PN_OBJECT_PERMISSION associates a group with an object, and indicates through the "bitmask" ACTIONS field, the actions that members of the group can apply to the object. The PN_OBJECT_TYPE table has a DEFAULT_PERMISSION_ACTIONS field which specifies the default actions permitted for an object of a given type

PN_MODULE_PERMISSION associates a group with a space and a module (i.e. a tool), and indicates the actions that members of the group can perform with that module, when operating on the specified space.

Database reports and models