Document routing workflow database design

Поиск
Список
Период
Сортировка
Искать
От
jonesd@xmission.com
Тема
Document routing workflow database design
Дата
Msg-id
20120124100810.opsgjrd7uo0ks8kg@webmail.xmission.com
Список
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
В списке pgsql-general по дате отправления
От: Adrian Klaver
Дата:
Сообщение: Re: update with from
От: panam
Дата:
FAQ