Take the 2-minute tour ×

I am doing an API that will serve for comunicating between our company and a phone capturing company (a company that accepts phone call from our customer and captures their input (keys, voice message, etc)).

How can I store the workflow in a database? I want to be able to provide unique ID for each call that we will use to identify it in next requests, ask for a prompt, capture data/voice, go back to a certain position in the workflow after N-th wrong attempts (something like GOTO) and jumt to the next step in case of a successful input, end call, etc.

I am trying to achive operands like:
- IF (possible multiple choise, not just Y/N, but also 1,2,3,4... choises)
- WHILE(N-th attempts)
- GOTO(jump to different step - not next)

I know that my explanation is too vague, and I am sorry about that. You all know what a automatic phone call is and what could happen during it.

I was thining to have a separate workflow handling class for each new phone capture project. This way if some one ask the API for step1 of project 1 the code will know that it should pass the request to the project class. API will have a main class(dispatcher) that will read the workflow table and handle serving the request to the desired project class.

I want to make something flexible and simple. Does anyone have ideas, examples or suggestions?

Here is my idea so far. Store the workflow in a table

CREATE TABLE `projects_workflow` (
`project_id` INT NOT NULL ,
`name` VARCHAR( 20 ) NOT NULL ,
`prompt` VARCHAR( 20 ) NOT NULL ,
`capture` VARCHAR( 20 ) NOT NULL ,
`attempts` INT NOT NULL ,
`success` VARCHAR( 20 ) NOT NULL ,
`error` VARCHAR( 20 ) NOT NULL

flow_id - primary id, project_id - project id, cause we will have different projects, name - name of the node in the workflow (project id and name make a unique key), prompt - what will the caller hear (a sustem defined messages), capture - what will the sistem capture(entry(6,6,), VOICE, NONE), attempts - home many time can this repeat before counted as error, success and error lead to the next node.

Possible workflow could be:

flow_id project_id name       prompt  capture   attempts   sccess    error
1       1          capture1   01      6,6       1          endcall   error1
2       1          error1     02      NONE      4          capture1  capture2
3       1          capture2   03      VOICE     1          endcall
4       1          endcall    04      ENDCALL   1

If a event/node in the workflow with a error pass successfully the error couter will be reset.

So some one calls, he/she is asked for a 6-numbered number if wrong a error message will be responded (4 times) and then go to capture caller voice message. If success the call will end. I am going to keep a log of every step in the database.

I can and probably will excange the sucess and error columns with the IDs of the nodes instead of their names

P.S. Sorry for the bad description of the problem.

share|improve this question
your question is too broad to be answered. Try to narrow it down and ask a question per subject and make it specific: I have this use case ......, and this is my idea for a database structure <structure> is there a better way to lay out my database? Then when you have your database try and write some code and if you're stuck ask a specific question on the issue you're stuck on. –  Johan Jul 14 '11 at 9:48
I have tried to explain better and post my idea so far. Hope that helps. –  Yasen Zhelev Jul 14 '11 at 10:12

1 Answer 1

You will need a whole bunch of tables. Each of your varchar fields should be a foreign key to a lookup table. You will end up needing more information than you can store in that varchar field.

Also write down all states that your individual fields can take and draw a state transition diagram. Then you can identify the fields on which a state transition depends. Your workflow table will then contain those fields + some associated action foreign key.

share|improve this answer
Could you provide some links to articles or solutions that implement your proposal? –  Yasen Zhelev Jul 14 '11 at 10:22
I don't have any links. It's just my personal experience that I earned while developing a telephony application which is partly doing what want you do. –  MicSim Jul 14 '11 at 10:54
OK, Can you then at least provide more details, scheme, table structure, something? I do not get your idea from your answer –  Yasen Zhelev Jul 14 '11 at 10:56

Your Answer


