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