I am attempting to create a Java internet based application that may work flow different objects lower different pathways. These objects and pathways could be defined within the DB as Work Flow Classes and Work Items. Right now i'm focusing on the Calculation Assignment object which will get its work flow path according to its selected Calculation Level.

Listed here are my tables for work flowing Information:

TPQOT_CALC_ASSIGNMENT "Calculation Assignment - Main Object"
CALC_NUM (PK)        VARCHAR2(6)  "Calculation Number"
CALC_REV (PK)        VARCHAR2(2)  "Calculation Revision Designator"
CALC_DEPT (PK)       VARCHAR2(3)  "Calculation Originating Department"
CALC_LEVEL_ID (FK)   VARCHAR2(24)  "Calculation Level ID"
WORKFLOW_INSTANCE_ID (FK) VARCHAR2(24)  "Workflow Instance Unique ID"

TPQOT_CALC_LEVEL_WORKFLOW "Calculation Level To Work Flow Class Lookup Table"
CALC_LEVEL_ID (PK)     VARCHAR2(24) "Calculation Level To Work Flow Type Unique ID (Surrogate)"
CALC_LEVEL             VARCHAR2(3) "Calculation Level"
DEPT_OWNER             VARCHAR2(3) "Department Owner"
WORKFLOW_CLASS_ID (FK) VARCHAR2(24) "Work flow Class Unique ID"

WF_WORKFLOW_INSTANCE "Workflow Instances"
WORKFLOW_INSTANCE_ID (PK)  VARCHAR2(24)  "Work Flow Instance Unique ID"
WORKFLOW_CLASS_ID (FK)     VARCHAR2(24)  "Work Flow Class Unique ID"  
STARTED_BY                 VARCHAR2(9)  "Work Flow Instance Started By Badge"  
LAST_DATE                  DATE         "Last date instance was worked"
STATUS                     VARCHAR2(3)  "Workflow Instance Status"

WF_WORKFLOW_CLASS "Workflow process classes by department Reference Table"
WORKFLOW_CLASS_ID (PK)   VARCHAR2(24) "Work flow Class Unique ID (Surrogate)"
DEPT_OWNER               VARCHAR2(3)  "Department Owner"
NAME                     VARCHAR2(64)  "Short Name"
DESCRIPTION              VARCHAR2(256)  "Description"
VERSION                  VARCHAR2(2) "Version Number"

Whenever a user begins a brand new Calculation Work Flow, they select a Calculation Level from the pull lower, which informs me which WORK_FLOW_CLASS_ID I ought to store within the WF_WORKFLOW_INSTANCE table once they click submit.

Within the TPQOT_CALC_ASSIGNMENT table the 2 foreign secrets saved - CALC_LEVEL_ID &lifier WORKFLOW_INSTANCE_ID - both connect with exactly the same WORK_FLOW_CLASS within their respected tables.

Let's say the Calculation Assignment information is edited, and also the Calc Level is transformed from an amount II Calc to an amount III Calc (Which relates to another Work Flow Classes)?

Is a flaw which will arrived at haunt me in the future? I recognize I possibly could code to make certain when the Calc Level is up-to-date, then make certain a brand new work flow instance is produced properly.

You might question why I not have the Calc Level in the actual work flow instance. For the reason that I might want to create another resist work flow that does not use an amount area also it may have one path. For instance employment applicant's resume.


====EDIT==== Let's say I simply remove CALC_LEVEL_ID in the TPQOT_CALC_ASSIGNMENT table? The TPQOT_CALC_LEVEL_WORKFLOW table is going to be employed for the Calc Level Pull lower, which supplies me the WORKFLOW_CLASS_ID for creatining a WF_WORKFLOW_INSTANCE? When viewing a Calculation Assignment Form, I'd have the ability to UNION the tables and obtain the Calc Level label. Any thoughs?