CODEWITHSUNDEEP
databaseoracle-databasedatabase-design
jeff
jeff

Reputation: 3742

Database design and normalization when two FK have relation

I'm trying to develop a Java web based application that can work flow different objects down different paths. These objects and paths would be defined in the DB as Work Flow Classes and Work Items. Right now I am working on the Calculation Assignment object which gets its work flow path based on its selected Calculation Level.

Here are my tables for work flowing Calculations:

TPQOT_CALC_ASSIGNMENT "Calculation Assignment - Main Object"
=====================
CONSTRAINT dup_wf_instance UNIQUE (WORKFLOW_INSTANCE_ID) 
---------------------
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"
=========================
CONSTRAINT dup_calclvlwf UNIQUE (CALC_LEVEL, DEPT_OWNER, WORKFLOW_CLASS_ID)
CONSTRAINT dup_calclvl   UNIQUE (CALC_LEVEL, DEPT_OWNER)
-------------------------
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"
=================
CONSTRAINT dup_workflow UNIQUE (DEPT_OWNER, NAME)
-----------------
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"

When a user starts a new Calculation Work Flow, they choose a Calculation Level from a pull down, which tells me which WORK_FLOW_CLASS_ID I should store in the WF_WORKFLOW_INSTANCE table when they click submit.

In the TPQOT_CALC_ASSIGNMENT table the two foreign keys stored - CALC_LEVEL_ID & WORKFLOW_INSTANCE_ID - both relate to the same WORK_FLOW_CLASS in their respected tables.

What if the Calculation Assignment data is edited, and the Calc Level is changed from a Level II Calc to a Level III Calc (Which relates to a different Work Flow Classes)?

Is this a flaw that will come to haunt me down the road? I realize I could code to make sure that if the Calc Level is updated, then make sure a new work flow instance is created appropriately.

You may wonder why I don't have the Calc Level right in the work flow instance. It is because I may want to create another object to work flow that doesn't use a Level field and it may only have one path. For example a job applicant's resume. 

JOB_APPLICANT 
=====================
APPLICANT_ID (PK)
FIRST_NAME
WORKFLOW_INSTANCE_ID (FK)

====EDIT==== What if I just remove CALC_LEVEL_ID from the TPQOT_CALC_ASSIGNMENT table? The TPQOT_CALC_LEVEL_WORKFLOW table will be used for the Calc Level Pull down, which provides me the WORKFLOW_CLASS_ID for creatining a WF_WORKFLOW_INSTANCE? When viewing a Calculation Assignment Form, I would be able to UNION the tables and get the Calc Level label. Any thoughs?

Upvotes: 1

Views: 157

Answers (2)

Kevin Burton
Kevin Burton

Reputation: 11936

If you find revoking the update right too restrictive, You could enforce the rule using a simple trigger, to raise an error if the calc level is updated without the workflow id being changed:

CREATE OR REPLACE TRIGGER trg_chk_class_change
BEFORE UPDATE
ON tpqot_calc_assignment 
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
DECLARE

BEGIN   
    IF :NEW.calc_level_id <> :old.calc_level_id AND :NEW.workflow_class_id  = :OLD.workflow_class_id  THEN
        raise_application_error(-20001, 'Class change only allowed if workflowid is changed'); 
    END IF;    
END;

Upvotes: 2

Mike Sherrill &#39;Cat Recall&#39;
Mike Sherrill &#39;Cat Recall&#39;

Reputation: 95522

What if the Calculation Assignment data is edited, and the Calc Level is changed from a Level II Calc to a Level III Calc (Which relates to a different Work Flow Classes)?

If that's an update you intend to allow, then you're responsible for reacting appropriately to the update. You might need to revoke permissions on base tables, and handle all updates through an updatable view or a stored procedure. Or you might need to write one or more triggers.

If that's an update you shouldn't allow, then you should not allow it. You can grant only INSERT and DELETE privileges and revoke UPDATE privileges. There are probably other ways.

Allowing the update without reacting appropriately to it leaves your database in an invalid state. Since this is a requirement that should apply to all users, it should ideally be in the database, not in application code.

Upvotes: 3

Related Questions