AnsweredAssumed Answered

Data Model Review

Question asked by robin1 on Jun 29, 2012
Latest reply on Aug 3, 2012 by bernd.ruecker
Hi guys,

I've been taking a look at the Data Model for Activiti and noticed some areas that can be improved.  If I'm off on any of these, please comment back.

The Activiti tables fall into 3 categories: 

•   Identity
•   Engine
•   History

The Identity tables are:
•   ACT_ID_GROUP
•   ACT_ID_MEMBERSHIP
•   ACT_ID_USER
•   ACT_ID_INFO

The Engine tables are:
•   ACT_GE_PROPERTY (Activiti version)
•   ACT_GE_BYTEARRAY (Process version)
•   ACT_RE_DEPLOYMENT (When and how a process is deployed)
•   ACT_RE_PROCDEF (Process definition)
•   ACT_RU_EXECUTION (Instance of a process)
•   ACT_RU_JOB (Job within a process)
•   ACT_RU_TASK (Tasks within a process)
•   ACT_RU_IDENTITYLINK (Link between Engine and Identity)
•   ACT_RU_VARIABLE (Variables for tasks)
•   ACT_RU_EVENT_SUBSCR (Process events)

Generally speaking, the relationships between the tables are all messed up.  On a high level:  a process has executions, an execution has tasks, a task has variables.
•   ACT_RU_TASK shouldn’t have the field PROC_DEF_ID_ (it’s redundant)
•   ACT_RU_VARIABLE shouldn’t have the field EXECUTION_ID_  (it’s redundant)
•   There should be a foreign key constraint between ACT_RE_PROCDEF. ID_ and ACT_RU_EXECUTION. PROC_DEF_ID_

It’s really bothering me that there are no foreign key constraints between any of the Identity tables and the Engine tables.

There should be foreign key constraints between:
•   ACT_RU_IDENTITYLINK. GROUP_ID_
•   ACT_ID_GROUP.ID_

There should be foreign key constraints between:
•   ACT_ID_USER.ID_
•   ACT_RU_IDENTITYLINK. USER_ID_
•   ACT_RU_TASK. OWNER_
•   ACT_RU_TASK. ASSIGNEE_

There should be a foreign key constraint between:
•   ACT_RU_EXECUTION.ID_
•   ACT_RU_JOB. EXECUTION _ID_

There should be a foreign key constraint between:
•   ACT_RU_TASK.ID_
•   ACT_RU_VARIABLE. TASK_ID_

The following fields are redundant:
•   ACT_RU_EXECUTION. PROC_INST_ID_ (same as ID_)
•   ACT_RU_TASK. PROC_INST _ID_ (same as EXECUTION _ID_)
•   ACT_RU_VARIABLE. PROC_INST _ID_ (same as EXECUTION _ID_)
•   ACT_RU_JOB. PROCESS_INSTANCE _ID_ (same as EXECUTION _ID_)

The History tables are:
•   ACT_HI_PROCINST (History for Process Executions/Instances)
•   ACT_HI_ACTINST (History of Process Actions)
•   ACT_HI_TASKINST (History of Process Tasks)
•   ACT_HI_DETAIL (Doesn’t seem to be used – should be history of Task Variables)
•   ACT_HI_COMMENT (Task comments)
•   ACT_HI_ATTACHMENT (Task URL???  No idea)

There are no foreign key constraints on any of the History tables.  Not between each other.  Not to the Identity tables.  This isn’t a big deal.

I ran a bunch of workflows on my local database a number of times assigning tasks to different groups.  These History records are storing surprisingly little of the user information.  ACT_HI_TASKINST. ASSIGNEE_ and ACT_HI_ACTINST.ASSIGNEE_ are  the only fields that are ever populated but most of the time the value is null. 

Upon further review, I found that ACT_RU_TASK. OWNER_ and ACT_RU_TASK. ASSIGNEE_ are always null.  I imagine these field are no longer required and this information is now stored in ACT_RU_IDENTITYLINK.  The problem is, when a task is assigned to a group, GROUP_ID_ is not stored in any of the history tables.

For some reason USER_ID_ of the person who performs a task is rarely populated in the history tables?

Outcomes