Make your own free website on Tripod.com

Original Table design

Notice how there are two foreign keys in the RE_ACTION table: email_action_id and datamap_action_id. One of these must always be NULL. I didn't like this approach because the structure of the table would change if and when we invent new types of actions.

Improved Table design

The major difference here is that the RE_ACTION table becomes merely the source of available action_id key values, which are used as foreign keys by the other two Action tables. This reverses the relationship! Before these tables embedded their primary key values in the RE_ACTION table.

Notice also that we've added a new table, the RE_EXPR_ACTION table, which uses a composite key to guarantee only one action of each type per Expression. Then the RE_ACTION table donates its primary key as a foreign key into this table, and that makes the relationship between Actions and Expressions. In the old design, the RE_ACTION table was trying to be a link table between Actions and Expressions and was also a "lookup" table for the specific action type.

Summary

Split RE_ACTION table into two tables with distinct purposes, and reversed the direction of the foreign key relationships from the general Action_IDs and the specific Action_IDs. Notice how there are two foreign keys in the RE_ACTION table: email_action_id and datamap_action_id. One of these must always be NULL. I didn't like this approach because the structure of the table would change if and when we invent new types of actions.