designing a mysql workflow database

I am writing a simple issue tracking system. I need to know how I can design a database allowing for dynamic workflows.

Employees will make requests in these system. Such requests move from office to office. For example an issue x workflow could be as follows:

1. The employee posts an issue for which I have defined a category
2. The issue is routed to the first department defined in the work flow
3. After approval, it is routed to the second department defined in the workflow

I already have tables for issues, issue_category, departments so I 开发者_如何学编程want to know how to implement an workflow table related to the departments and how to forward to the next table after approval.

Sorry for the long winded question. suggestions, guidelines, request for clarification welcome.

Not writing a system takes less time that writing one.

Have you looked into off-the-shelf workflow systems? There are lots of BPM solutions out there that will do what you're describing very nicely.

Issue tracking? Have you thought about just using JIRA or Bugzilla or something like that?

If your purpose is to learn how to write a workflow system, go for it. But if you're intending to put a solution into production for a wider audience be aware of other possibilities.

I'd forget about tables for a while and just think in a more abstract way about the problem. I see a number of meaningful entities in your statement:

1. workflow
2. task
3. employee
4. department

I also imagine some other items that might be useful in your solution:

1. queue for each employee to accept and prioritize incoming tasks
2. a mechanism for allowing both computer and human consumers of tasks to plug into the system
3. an auditing capability to track how tasks flow through for compliance and debugging purposes
4. an alerting mechanism to notify users that a task has been completed
5. a scheduler to allow tasks to be added on a regular basis

You have a lot of thinking to do before you even begin thinking about tables. I'd recommend doing that first.

This is a very big problem. If you're doing this for someone else, make sure that all parties understand what they're getting into.