Re: RM VERY STRONG SUGGESTION 4: TRANSITION CONSTRAINTS

On Sep 4, 4:36 pm, -CELKO- <jcelko..._at_earthlink.net> wrote:
> 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)
>    REFERENCES StateChanges (previous_state, current_state)
>    ON UPDATE CASCADE,
> ..);
>
> 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.

This solution might--and I say might because it probably won't--work to enforce only one of the transition constraints described in my original post. The problem is that it doesn't address the issue of key updates described in the original post. It also doesn't prevent one from inserting a row that contains, say, ('Married', 'Dead'), for someone that wasn't already 'Married.' Received on Sun Sep 05 2010 - 01:07:51 CEST