Re: RM VERY STRONG SUGGESTION 4: TRANSITION CONSTRAINTS

Here is an article I did on state transition constraints in SQL: http://www.simple-talk.com/sql/t-sql-programming/constraint-yourself!/

The basic idea is to model a transition as a (prior_state, current_stte) pair that references a table of legal pairs. Here is the skeleton:

CREATE TABLE StateChanges
(previous_state VARCHAR(15) NOT NULL,
 current_state VARCHAR(15) NOT NULL,
PRIMARY KEY (previous_state, current_state));

INSERT INTO StateChanges

VALUES ('Born', 'Born'), -- initial state


       ('Born', 'Married'),

       ('Born', 'Dead'),

       ('Married', 'Divorced'),

       ('Married', 'Dead'),

       ('Divorced', 'Married'),

       ('Divorced', 'Dead'),


       ('Dead', 'Dead'); -- terminal state



The target table looks like this.
CREATE TABLE MyLife
(..
 previous_state VARCHAR(15) DEFAULT 'Born' NOT NULL,
 current_state VARCHAR(15) DEFAULT 'Born' NOT NULL,
 FOREIGN KEY (previous_state, current_state)

Add a (start_time, end_time) pair to show the duration of the state in the history and a NULL end_time shows the current state. Simple and declarative code with Standard SQL.

 I am designing such a schema for a company that handles auctions -- offers are posted, bids are posted in reply, bids and bids can expire, be resended, transactions completed, feedback made, etc.

The current design is a lot of bit flags and the data has no integrity. Contradictory flags are not detected. A timeline is almost impossible to construct. etc. Received on Sat Sep 04 2010 - 22:36:28 CEST