Database Model plugin_tmp.xml

area
This table contain all company areas of system. It can be used to categorize an user and can be used into reports.
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
Id VARCHAR(10) PK NN     Area id  
name VARCHAR(50)         Area name  
description VARCHAR(100)         Area description  


category
This table contain a list of all categories of system. A category could be used to give more qualification for the requirements and tasks for a specific project.
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
id VARCHAR(10) PK NN     Category ID  
name VARCHAR(50)         Category Name  
description VARCHAR(100)         Category Description  
project_id VARCHAR(10)         id of project related to the category  
type_ INTEGER(1)     UNSIGNED   if it is 1 the category is applicable to the requirement, if it is 0, the it is a task category  


customer
This table contain all customer of all projects. Each customer ID should be into the user table, to respect the architecture hierarchy
where a Customer entity IS A User entity.
Besides, the customr entity contain a number of attributes of the relationship between the specific customer and the project (exemple: enable status, pre approving permission, etc). Because a Resource and Leader entities are automatically a Customer entity, all this attibutes defines into the customer table will be inherited by Resource and Leader entities.
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
Id VARCHAR(10) PK NN     Customer ID  
project_id VARCHAR(10) PK NN     Project ID  
is_disable INTEGER(1)     UNSIGNED   if it is '1' the current customer is disabled to perform actions for the related project  
is_req_acceptable INTEGER(1)     UNSIGNED      
can_see_tech_comment INTEGER(1)     UNSIGNED   if it is '1' the current customer is allowed to view the technical comments that was written by resources into the tasks.  
pre_approve_req INTEGER(1)     UNSIGNED   if it is '1' all the requirements opened by this customer for the related project will be turn in tasks automatically without leader approving.  


department
This table contain all company departments of system. It can be used to categorize an user and can be used into reports.
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
Id VARCHAR(10) PK NN     Department ID  
name VARCHAR(50)         Department Name  
description VARCHAR(100)         Department Dexcription  


event_log
This table contain the event generated by system. Some events of system could be stored into data base and used for audit purposes. The Logger Notification save the event information into this table as well. The event table contain the reference (username) to the user that has included the event but it doesn't has any FK to user table.
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
summary VARCHAR(10)         Event type. Could be a mneumonic or some free string that could be used to group the events  
description TEXT         Event description  
creation_date TIMESTAMP         When the event was created (Date/time)  
username VARCHAR(30)         Who create the event (username of user table)  


function
This table contain all company functions of system. It can be used to categorize an user and can be used into reports.
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
Id VARCHAR(10) PK NN     Function ID  
name VARCHAR(50)         Function Name  
description VARCHAR(100)         Function Description  


leader
This table contain the IDs of all project leaders. Each leader ID should be into the resource table, to respect the architecture hierarchy where a Leader entity IS A Resource entity.
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
id VARCHAR(10) PK NN     Leader ID  
project_id VARCHAR(10) PK NN     Project ID  


notification
This table contain the notification os system. A notification is an alert that starts in a determined moment and send a customized message for a specific channel. The native channel implemented into the tool is: Email, Http and DBLog, but more channels could be extended through the framework of tool.
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
id VARCHAR(10) PK NN     notification id AI
name VARCHAR(50)   NN     notification name  
description VARCHAR(100)         notification description  
notification_class VARCHAR(100)   NN     the class signature (package structure plus class name) that implement the channel of notification (email, http, or some customized implementation)  
sql_text TEXT   NN     the SQL that contain the condition of notiication. In other words, if the SQL return some data from DB means that something should be sent by the notificator.  
retry_number INTEGER(2)     UNSIGNED   number of times that the notification engine will try to send the alert.  
next_notification TIMESTAMP(14)   NN     Date/time provisioned by system to send the next alert.  
final_date TIMESTAMP(14)         If null, the current alert is active. If the alert is disabled, this field contain the date/time of disabling action.  
last_check TIMESTAMP(14)   NN        
period_minute INTEGER(3)     UNSIGNED   The minute that the notification should starts.  
period_hour INTEGER(3)     UNSIGNED   The haur that the notification should starts.  
periodicity INTEGER(2)   NN UNSIGNED   The periodicity of alert: 1-Yearly; 2-Monthly; 3-Weekly; 4-Daily; 5-Eventually  


notification_field
This table contain the fields used by the channel of notification in order to provide the parameters used for notification engine. For example: the native email notification of tool contain knows how to send an e-mail but it need some information about that SMPT server, user, tittle, etc, that changes in each case. Thus, the records of this tables will be "translated" in customized fields of the notification GUI.
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
notification_id VARCHAR(10) PK NN     the id of related notification  
name VARCHAR(100) PK NN     the unique name of the field (parameter) notification.  
value VARCHAR(100)         the value of parameter that will be used into the channel implementation  


p_sequence
This table contain the sequence used by system to increment the ids of all tables. The architecture of system was desing to avoid using features of specific data bases. A "sequence" feature and "auto-increment ID" feature are available in many commercial data bases, but each data base has a specific rule to create and increment the sequence, then into PLANdora the control os "ID sequence" is into the business rules of system and the table p_sequence store the next ID available.
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
Id BIGINT(9) PK NN     Unique Sequential ID  


planning
This entity is a abstraction of the main planning entities of system, i.e, Task, Requeriment and Project.
This "super-entity" contain the common fields of all planning entities.
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
Id VARCHAR(10) PK NN     ID  
description TEXT         Description of the entity (task, requirement, project, etc)  
creation_date TIMESTAMP(14)         Entity creation date  
final_date TIMESTAMP(14)         Entity final date (when it isn't null the entity was excluded)  


preference
This table contain the customized system preferences of users that selected options different from standard.
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
user_Id VARCHAR(10) PK NN     user id related to the preference option  
id VARCHAR(50) PK NN     preference id  
value VARCHAR(25)   NN     value of preference  


project
This table contain all projects of system.
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
Id VARCHAR(10) PK NN     Project ID  
name VARCHAR(30)         Project Name  
parent_id VARCHAR(10)         ID of parent project (used to create a hierarchy of projects for Gantt Chart, KPI and Reports purposes)  
project_status_id VARCHAR(10)   NN     Project Status ID  


project_history
This table contain the life-cycle of a project. Each state transition of project is stored here.
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
project_id VARCHAR(10) PK NN     Project ID  
project_status_id VARCHAR(10) PK NN     Project Status ID  
creation_date TIMESTAMP(14) PK NN     History occurrence date  


project_status
This table contain all status of project. Each status is related to a numeric constant used into the system to define the status. This constant is following: 1 - Project Opened ; 2 - Project on-Hold ; 3 - Project Aborted/Closed
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
Id VARCHAR(10) PK NN     Project Status ID  
name VARCHAR(50)         Status Name  
state_machine_order BIGINT(4)         Numeric Constant of project state-machine  


report
This table contain all Report or KPI records of system. Each Report or KPI contain a SQL statement. A Report use the SQL statement to populate data into the Jasper report layout. In the other hand the system timer use the SQL statement of KPI record to get a indicator value from data base and set the new value into the report_result table.
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
Id VARCHAR(10) PK NN     Report / KPI ID  
name VARCHAR(100)         Metric label (Report/KPI)  
type INTEGER(2)   NN     Metric Type (1=daily, etc)  
report_perspective_id VARCHAR(10)   NN     If it is a KPI, define the BSC perspective, otherwise is NULL  
sql_text TEXT   NN     SQL statement that implement the metric business rule.  
execution_hour INTEGER(2)   NN     Execution time of metric (hour that timer should wake up to run the SQL)  
last_execution TIMESTAMP(14)         Last execution date/time  
project_Id VARCHAR(10)   NN     Project id related with the metric  
final_date TIMESTAMP(14)         Final date of metric. If NULL the metric is alive, otherwise, contain the date/time of exclusion.  
data_type INTEGER(2)   NN     Type of metric data (1=date, 0=float, etc)  
file_name VARCHAR(100)         If the current record is a KPI this field is null, otherwise, contain the path of Jasper file used by report engine.  


report_result
This table contain the values (metrics) generated by timer when it wake up and run a KPI's SQL clausule.
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
report_id VARCHAR(10) PK NN     KPI id  
last_execution TIMESTAMP(14) PK NN     Date of last execution  
value VARCHAR(25)   NN     KPI value (generated after the timer execution)  


requeriment
This table contain all requirements of system (bugs, support requests, system requirement, etc).
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
Id VARCHAR(10) PK NN     Requirement id  
suggested_date TIMESTAMP(14)         Delivery date suggested by customer  
deadline_date TIMESTAMP(14)         Delivery date planned by leader  
project_id VARCHAR(10)   NN     Project id  
user_id VARCHAR(10)   NN     User id related to the requirement (customer)  
requeriment_status_id VARCHAR(10)   NN     Requirement status id  
priority INTEGER(1)   NN     Requirement priority  
is_acceptance INTEGER(1)   NN     It is 1 if current requirement demands the customer acceptance to be closed, otherwise, it is 0  
category_id VARCHAR(10)         Category id  
reopening BIGINT(6)         Number of times that requirement was reopened  


requeriment_history
Each requirement has the own life cycle, and this table contain the information about the historical occurrence of the requirement.
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
requeriment_Id VARCHAR(10) PK NN     requirement foreign key  
requeriment_status_id VARCHAR(10) PK NN     requirement status key related to the history occurrence  
creation_date TIMESTAMP(14) PK NN     creation date of current history occurrence  
user_id VARCHAR(10)         user id related to the history occurremce  
comment VARCHAR(255)         Comment  


requeriment_status
This table contain a list of all status of the requirement. Each status is related to a numeric constant used into the system to define the status. This constant is following: 1 - Waiting Approve ; 100 - Planned ; 200 - Canceled ; 201 - Closed ; 202 - Refused ; 300 - In-Progress
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
Id VARCHAR(10) PK NN     Requirement Status id  
name VARCHAR(50)         Requeriment Status name  
description VARCHAR(100)         Requeriment Status description  
state_machine_order BIGINT(4)         Numeric constant of requirement status state machine. 1=Waiting Approve; 202=Refuse; 201=Closed; 200=Canceled; 100=Planned; 300=in-Progress  


resource
This table contain all resources of all project. Each resource ID should be into the customer table, to respect the architecture hierarchy
where a Resource entity IS A Customer entity.
Besides, the resource entity contain a number of attributes of the relationship between the specific resource and the project (exemple: cost, capacity, etc). Because a Leader entity is automatically a Resource entity, all this attibutes defines into the resource table will be inherited by Leader entity.
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
id VARCHAR(10) PK NN     Resource ID  
project_id VARCHAR(10) PK NN     Project ID  
can_self_alloc INTEGER(1)         If it is '1' the resource could set tasks for him self  
cost_per_hour DECIMAL(9,2)         The cost of one hour of this resource into the related project  
can_see_customer INTEGER(1)         If is is '1' the resource could view into the "resource task" form the name of the customer that made the request.  
capacity_per_day DECIMAL(9,2)         The number of minutes (capacity) allocated to this resource to work into the project  


resource_task
This table contain the allocation of resource for a specific task. Contain the general information about the status, task date and duration. However, there are another table (resource_task_alloct) that contain the time values along the time day by day. For example, if the resource_task contain a task that should be completed in 12 hours, then there are two related rows in resource_task_alloc with value = 8 and 4.
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
task_id VARCHAR(10) PK NN     Task ID  
resource_id VARCHAR(10) PK NN     Resource ID  
project_id VARCHAR(10) PK NN     Project ID  
start_date TIMESTAMP(14)         Estimated Date to start the task by resource  
estimated_Time BIGINT(6)   NN     Estimated time (in minutes) to finish the task  
actual_date TIMESTAMP(14)         Actual start date of task  
actual_Time BIGINT(6)         Actual duration time of task  
task_status_id VARCHAR(10)   NN     Current status of task (for a specific resource)  
is_acceptance_task INTEGER(1)     UNSIGNED   boolean field that define if current task/resource is under customer approval.  


resource_task_alloc
This table contain the allocation of human resources for a specific task. An allocation is a quantity of minutes used by resource to work in the task.
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
task_id VARCHAR(10) PK NN     Task ID  
resource_id VARCHAR(10) PK NN     Resource ID  
project_id VARCHAR(10) PK NN     Project ID  
sequence BIGINT(6) PK NN     Sequence used to order the task slots (It is used into Gantt Chart for scheduling purposes)  
alloc_time BIGINT(6)   NN     Time value allocated into the slot (in minutes)  


root
This table contain only one record. It is the ID of root user and is always "3" and it is alwayed related to the project "0". The project "0" it is the root project.
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
Id VARCHAR(10) PK NN     Root ID  
project_id VARCHAR(10) PK NN     Project ID  


task
This table contain a list of all tasks of system. A task represent a work event performed by the resources of project.
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
Id VARCHAR(10) PK NN     Task ID  
name VARCHAR(50)         Task Name  
project_id VARCHAR(10)   NN     Project ID related to the task  
requeriment_id VARCHAR(10)         Requirement ID related to the task  
category_id VARCHAR(10)   NN     Category ID related to the task  
task_id VARCHAR(10)         Parent Task ID. The parent task is used to join a group of tasks.  
is_parent_task INTEGER(1)         If it is 0 the current task is a common task, if it is 1 the task is a joinner task (used to join a group of tasks)  


task_history
Each task has the own life cycle, and this table contain the information about the historical occurrence of the task.
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
task_id VARCHAR(10) PK NN     Task id  
resource_id VARCHAR(10) PK NN     Resource id  
project_id VARCHAR(10) PK NN     Project id related to the task  
task_status_id VARCHAR(10) PK NN     Status of task  
creation_date TIMESTAMP(14) PK NN     Date/Time of history occurrence  
start_date TIMESTAMP(14)         Estimated initial date to the conclusion of task (historical occurrence)  
estimated_Time BIGINT(6)   NN     Estimated task durantion (historical occurrence)  
actual_date TIMESTAMP(14)         Actual initial date (historical occurrence)  
actual_Time BIGINT(6)         Actual task duration (historical occurrence)  
user_id VARCHAR(10)         Id of user related to the history event  
comment TEXT         Comment  


task_status
This table contain all status of task. Each status is related to a numeric constant used into the system to define the status. This constant is following: 1 - Task Open ; 100 - Task Closed ; 101 - Task Canceled ; 20 - Task In ProgressI ; 50 - Task on-Hold
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
Id VARCHAR(10) PK NN     Task Status ID  
name VARCHAR(50)         Task Status Name  
description VARCHAR(100)         Task Status Description  
state_machine_order BIGINT(4)         Numeric constant of "task status" state machine. 1=Open; 100=Close; 101=Canceled; 20=in-Progress; 50=on-Hold  


user
This table contain all users of system, independently of project or role.
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
Id VARCHAR(10) PK NN     User ID  
username VARCHAR(30)   NN     User name that should be used in login process  
password VARCHAR(40)   NN     Access password of user  
name VARCHAR(50)   NN     Name of user  
email VARCHAR(70)         email account of user  
phone VARCHAR(20)         User phone number  
color VARCHAR(10)         Representative color of user (used by Gantt Chart bars to identify a task assigned to user). It is a RGB hexadecimal pattern (ex: FF0000 = red, C2C2C2= gray, etc)  
department_id VARCHAR(10)   NN     Department Id related to user  
area_id VARCHAR(10)   NN     Area id related to user  
function_id VARCHAR(10)   NN     Function id related to user  
country CHAR(2)   NN     Country related to user used by system for i18n purposes (ex.: BR, US, etc)  
language CHAR(2)   NN     Language related to user used by system for i18n purposes (ex.: pt, en, etc)