Database structure

Here we outline Twproject’s database relational structure. We assume familiarity with relational concepts. For readability and in order to give the essential information, we don’t cover complete schemas. E.g. relationship to the area and operator table are usually removed.

Basic project data and dates

Basic project data is in the twk_task table. Notice the “parent” column that is a foreign key that points to the same table, and determines the project position in the tree. The recursive values of the parents are also stored (denormalized) in the “ancestorids” field for performance reasons. The twk_taskrelation contains one record for each parent-descendant couple, plus the self-self relation.

 

Project and assignments

The most important relation connecting projects with people is the assignment. The twk_assignment table is a relational table that connects projects to resources (people) and roles. Also all worklog inserted in Twproject is “on” an assignment. So to query the worklog on a project, you will have to join to assignments and from there join to worklogs. The twk_assignment_data_hist contains the hisotory of assignment values changes.

ToDos

ToDos are stored in twk_issue table. Every ToDo is in relation with a task and eventually assigned to a resource.

ToDos related worklogs will refer to an assignment and with the ToDo itself.
Notice that there is a de-normalized reference to worklogs, and the table issue_history where the history of changes to the ToDo is kept.

Agenda

Every item in the agenda has a schedule and a set of participants. The schedule is directly joined, the participants are collected by joining through the twk_agenda_tar. If eventually the agenda event is also a meeting, there is a foreign key to the twk_meeting table.

Resource

Resources are obviously involved in most Twproject tables; here we just show the direct dependencies on this table. Notice that the resource basic data (such as “e-mail”) is kept in a join though twk_res_ad to olpl_anagraphicaldata.

What is the logged user?

Twproject logged users are “double” entities: they are “resources”, so that assignments can be done on them, and users of the system, so they can log in.

So a user that logs in is a record in the olpl_operator table, that is in 1-to-1 relationship with a record in the twk_resources table.

The resource record id is shown in the web interface in the resource editor general tab;

The operator record id in the security/login tab.

In almost all cases what you need to filter records is the resource record id: on ToDo, worklogs, assignments.

An exception is the subscription engine.

Filter by area?

Filtering by area is trivial, as most objects have an area or areax column for which you can filter.