Help a CRUD programmer think about an "approval workflow"

Question

I've been working on a web application that is basically a CRUD application (Create, Read, Update, Delete). Recently, I've started working on what I'm calling an "approval workflow". Basically, a request is generated for a material and then sent for approval to a manager. Depending on what is requested, different people need to approve the request or perhaps send it back to the requester for modification. The approvers need to keep track of what to approve what has been approved and the requesters need to see the status of their requests.

As a "CRUD" developer, I'm having a hard-time wrapping my head around how to design this. What database tables should I have? How do I keep track of the state of the request? How should I notify users of actions that have happened to their requests?

Is their a design pattern that could help me with this? Should I be drawing state-machines in my code?

I think this is a generic programing question, but if it makes any difference I'm using Django with MySQL.

Answer 1

There are design patterns for this. Maybe they will help a bit:

http://en.wikipedia.org/wiki/Workflow_patterns

Workflows are more state-driven than simple CRUD apps, so yes, reading up on state-machine patterns will help too. It sounds like you're on the right track.

As for data modelling, you could have a table of all "approvals", with a state field that is a foreign key to a table of states.

As for notifications, that's something your app will have to do when it changes the state of an approval, it will have to look up somewhere else to see who/what needs to be notified for a particular statechange (so you will have to track what entities get notified for which state-changes).

Answer 2

Approval == State Change

State Change == Update to the thing that changed && Insert to a log to record that something changed.

That's it.

The interesting rules are "who's allowed to do the update?" "Which state changes are legal updates?" and "which states are dead-ends?"

You're building a Finite Automata. The state is an attributes of an object. You push something "through the workflow" by updating its state.

Answer 3

I'm in a similar project now (different platform / DB).

I have a DB app with different levels of user permissions as to what kinds of records they can CRUD. In most cases, I control the allowable operations in code.

However, for a number of the constructs, I have a "status code" for the construct, which then defines (again, in code) who can do what to that construct, and what status codes they can move it to.

Answer 4

As many have said, approving it is moving it into a new state.

Something to consider that I have run into is that I've found frequently users want to have things in the same "state", but also record that things are in a different step or task in that state. For instance, the users may want to distinguish between "requested" and "in process". From an approval perspective, requested and in process are both unapproved (open). If something goes from approved back to unapproved (open), they might call that "review required" - but it's still not approved.

So you might want something like this:

Current Task : State

Requested : Open

In Process : Open

Review Required : Open

Approved : Approved

As time goes on your users will probably want more "modes" or "tasks". If you make a state for each of these you can end up with a lot more complexity than you or they really wanted. If you let the users have as many "modes" or "tasks" as they want, with a many to one relationship with the states, it will be simpler from your perspective, but more precise from the user's perspective.

Answer 5

This came up for us so often that we made a generic routing system. It is probably overkill for what you are doing but you are welcome to mine it for ideas. It allows the routing of arbitrary content to arbitrary users using either a chain (sequential approval) or star ( broadcast) with and a minimim votes to approve. Here is an auto-generated ERD of the schema.

Basically we have a routing scheme that contains one or more routing scheme lists. Each list can be either chain or star, and the whole scheme can be initiated as a chain or star. Each list and the whole scheme can have its own votes_to_approve. This means that you could have a scheme with type "star" with two lists, one star and one chain. The whole scheme can have votes_to_approve of 1 so if either list approves, the whole workflow is approved. The chain list can have votes_to_approve equal to the number of members so that each member has to approve before the next member can vote, and the first to disapprove kills that list. In the star list you can have votes_to_approve of 1 so that anyone in that list can instantly approve the whole workflow

These schemes are saved indefinitely and can be re-used once set up. To implement a scheme, an entry in the Routing table is made with the scheme_id, the thing to be routed and some details like approval and disapproval text. The "routing_" tables then store the state of the implemented scheme.

We use a cross-application event system to send emails, or inform other applications when the route changes state.