1

This business model is for a case management database. This is closely modeled on the idea of a file folder representing the phase and a sequential checklist representing the stages. A case consists of a phase that can have one or more stages. A phase can only have one stage that is "Current" or open at any one point in time. A case can only start from one type of stage but can progress to any one of a number of stages that are end types. In this business model there are many different types of phases and stages

An example: you apply for a license. The process always starts with you submitting a form but can have different endings: the application is approved or rejected or sent back for more information.

Edit: @Colin 't Hart asks what a phase is in relation to a case. Here is where trying to simplify a question can omit details. The complete schema structure is: - one case can have one or more phases but only one phase is open or "current" at at time. - each phase can have one or more stages but only one phase is open or "current" at a time. - there are different types of cases/phases/stages and transitions from the current unit to the next unit require adding a close date to the current and inserting a new record with an open date. An example: a production line for widgets

  • a production ticket initiates the creation of the case
  • the first phase: sourcing components is created
  • the first stage: contacting suppliers is created
  • the first stage is completed, the second stage: orders from suppliers is opened
  • orders stage is closed, inventory check stage is created
  • inventory stage is closed, sourcing components phase is closed
  • new phase: assembly is opened
  • new stage: move components to shop floor is opened
  • moving components stage is closed, new stage production line is opened
  • and so on....

Problem:

  • the existing table structure is flawed in that the same information (what is the first type of stage for a kind of phase) is stored in two different tables
  • You can have more than one entry in STAGE where IS_START_STAGE = 1 which violates a business rule
  • You can insert a new entry into STAGE where IS_START_STAGE = 1 and this does not match the corresponding entry in PHASE_FIRST_STAGE
  • the relationship should be something like constraint PHASE_FIRST_STAGE.STAGE_ID can only be in the entries in STAGE where IS_FIRST_STAGE = 1
  • Is there anyway to enforce these business rules?

CREATE TABLE PHASE_FIRST_STAGE
(
  PHASE_ID           NUMBER(9)           NOT NULL, --PRIMARY KEY and foreign key to PHASE
  STAGE_ID           NUMBER(9)           NOT NULL,  --FOREIGN KEY to STAGE table
);
ALTER TABLE PHASE_FIRST_STAGE ADD (CONSTRAINT PFS01
  FOREIGN KEY (PHASE_ID) 
  REFERENCES PHASE (ID),
  FOREIGN KEY (STAGE_ID) 
  REFERENCES STAGE (ID));
COMMENT ON TABLE PHASE_FIRST_STAGE IS 'Contains the default first stages to enter when a phase is entered.';

CREATE TABLE STAGE
(
  ID               NUMBER(9)           NOT NULL, --PRIMARY KEY
  PHASE_ID         NUMBER(9)           NOT NULL,  --FOREIGN KEY to PHASE
  DISABLED         NUMBER(1)           DEFAULT 0   NOT NULL,  --CHECK IN (0,1)
  IS_START_STAGE            NUMBER(1),--CHECK IN (0,1)
  IS_END_STAGE              NUMBER(1) --CHECK IN (0,1)
);

COMMENT ON TABLE STAGE IS 'Contains all the stages a phase can have. Each stage must have only one phase. ';
--not shown is a similar table called PHASE with a one phase => many type of stage relationship
3
  • If you have to add business logic in constraints, you can use triggers to enforce the business rules Sep 17, 2013 at 6:50
  • I find it very difficult to understand what you're describing and how the datamodel is supposed to solve the problem. Could you separate the two: first describe purely your business -- without refering to the data model, and then describe how your data model implements it? Sep 24, 2013 at 19:03
  • Can you give us examples of phases? I can understand how a case (for example, a driver's license application) could go through various stages (application, rejection, approval) but how do the phases come into it? Sep 25, 2013 at 8:50

1 Answer 1

1

Justin Cave's answer here and Tom Kyte's pointed me to a solution using a function based index. I think this can be made even simpler with some more thought but this works now:

CREATE OR REPLACE FUNCTION UNIQUE_START_STAGE (
   phase_id_in   IN NUMBER,
   stage_id_in   IN NUMBER)
   RETURN NUMBER
   DETERMINISTIC
IS
 -- PURPOSE:enforce business logic that a phase can have only one stage where
 -- the disabled field has a value of 0 and IS_START_STAGE has a value of 1

   v_count   NUMBER (9);
BEGIN
   SELECT COUNT (s.id)
     INTO v_count
     FROM STAGE s
    WHERE     S.IS_START_STAGE = 1
          AND s.disabled = 0
          AND S.PHASE_ID = phase_id_in;

 IF v_count = 1
 THEN
     --return the primary key if there is only one
     v_count := stage_id_in;
  ELSIF v_count < 1
  THEN
     v_count := NULL;
  END IF;

   RETURN v_count;
END UNIQUE_START_STAGE;

and then we create an index based the idea that there can only be one child stage that is enabled for a phase that is the start stage

CREATE UNIQUE INDEX unique_start_stage_idx
   ON stage (
      CASE
         WHEN disabled = 1 THEN NULL
         WHEN is_start_stage = 0 THEN NULL
         ELSE UNIQUE_START_STAGE (phase_id, id)
      END);

--and add the same constraint to the other table

CREATE UNIQUE INDEX unique_start_stage_idx2 ON PHASE_FIRST_STAGE ( UNIQUE_START_STAGE (phase_id, stage_id));

This solution partially solves the problem:

  • it enforces that there is only one entry in STAGE for each value of PHASE_ID where IS_START_STAGE =1 and DISABLED = 0
  • it enforces this same uniqueness in PHASE_FIRST_STAGE
  • it does not enforce that an entry in STAGE is also in PHASE_FIRST_STAGE
  • you could replace the PHASE_FIRST_STAGE table with a view of STAGE that cleans up the last issue

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.