Enforce a constraint problem

Question

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

Answer 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