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 task data and dates

Basic task 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 task position in the tree. The recursive values of the parents are also stored (denormalized) in the “ancestorids” field for performance reasons.

Project / task dates: the start, end and duration of the task are found by a join to the table olpl_schedule, to the filds startx, endx and duration respectively.

Task and assignments

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

Issues

Issues can be without resource – just on a task, or without task – and then are a todo on a resource. But normally have a resource and a task, which indirectly most often determines an assignment, and that is where the worklog goes.

Notice that there is a de-normalized reference to worklogs, and the table issue_history where the history of changes to the issue 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 issues, 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.