I'm looking at a database design for tracking the movement/routing of
documents through a workflow using PostgreSQL (version 9.1).
Basically, I have a state diagram for the possible routings and came
up with two different designs for how to implement the tables. As a
quick advance note, not all transitions have the same information
content (for example, some require an explicit routing code, others
reference other tables, etc.).
The basic method would be to create a routing table that captures all
state transitions:
routing
-------
action_id
document_id
new_state_id
action_timestamp
...
It would couple this to a table of permissable transitions:
transition
----------
old_state_id
new_state_id
...
It would also have supplemental tables to capture information specific
to different transition types (I prefer this to having a bunch of
NULLs in the "master" table, as the additional data involved is
somewhat varied and sparse). Triggers would enforce creation these
secondary records. For example:
process_completion
------------------
action_id
resulting_activity_id
I came up with a second method, which may be too clever for my own
good. In this scheme, each transition type would have its own table:
routing_action
--------------
action_id
document_id
recipient_id
action_timestamp
completion_action
-----------------
action_id
resulting_activity_id
action_timestamp
Right now, I'm modeling queries on the second method using UNION
queries, although I realize that I could use inheritance to achieve
the same effect.
To me, the second method saves some overhead (no new_state_id required
- it's implicit in the table scheme) and simplifies the insertion
process (one INSERT as opposed to two) at the cost of additional
database complexity (more tables) and perhaps breaking from the SQL
paradigm (by placing information in the table scheme instead of in
rows).
Thanks in advance,
Dominic Jones