Re: Relation Schemata vs. Relation Variables

I did a short article on transition constraints in www.DBAzine.com

http://www.dbazine.com/ofinterest/oi-articles/celko35/view?searchterm=Celko%20transition

I examined triggers, CHECK() and DRI actions. The last one seems to be the best.

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 ('Grape Juice', 'Grape Juice');
INSERT INTO StateChanges VALUES ('Grape Juice', 'Wine');
INSERT INTO StateChanges VALUES ('Wine', 'Vinegar');
INSERT INTO StateChanges VALUES ('Wine', 'Spoilage');
INSERT INTO StateChanges VALUES ('Vinegar', 'Spoilage');
INSERT INTO StateChanges VALUES ('Spoilage', 'Spoilage');

• 'Grape Juice' is the initial state and a DEFAULT. 'Spoilage' is a terminal state.

CREATE TABLE WineCellarChanges
( ..
 previous_state VARCHAR(15) DEFAULT 'Grape Juice' NOT NULL,  current_state VARCHAR(15) DEFAULT 'Grape Juice' NOT NULL,  FOREIGN KEY (previous_state, current_state)    REFERENCES StateChanges (previous_state, current_state)    ON UPDATE CASCADE,
 transition_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, ..);

>> For instance, consider the following states for a relation describing people's marital status, and a transition constraint that says: Single people can't become Divorced: <<

You are wording it in the negative. I would 'single' the initial state and 'dead' the (very) terminal state for the transitions. Basically the StateChanges table is a directed graph written as an adjacency list model.

>> Since a transition that violates a transition constraint can result in the same /possible state/ as a transition that doesn't, the notions of relational assignment and multiple assignment are broken: <<

Using DRI, I think we avoid an illegal transition. I can also use the transition_time to keep a history and do a "rollback" if needed. Notice that state changes can be temporal -- graph juice becomes wine or vinegar in (n) days, so the time stamp can be important.

--CELKO-- Received on Mon Aug 21 2006 - 22:38:09 CEST