11-26-13, 09:57 #1Registered User
- Join Date
- May 2011
Database design for event workflow
I thought I would throw this out there just in case somebody had any input with regards how best to implement this db schema.
What we are looking for is the ability for the user to configure workflows, this in itself is not a problem however where we are seeing some potential issues is with the processing of the events.
At a very basic level you have an 'Event' which has a 'Status' the workflow configured by the user will in essence state that if the 'Event' has been at a given 'Status' for n length of time then trigger another event, real world example would be: -
An online form is sent to a user to complete, it is set to a status of 'Waiting', if the form is still at a 'Status' of waiting after 7 days then send a chase email.
So this all sounds easy enough until we consider that as the database of generated events increases, you must compare that entire database of events against your configured workflow to see if anything should be trigger. Thus the event processor task will take longer and longer to run as the database of events increases and thus creates a scalability risk.
Any thoughts, input appreciated.
11-26-13, 10:15 #2Resident Curmudgeon
- Join Date
- Feb 2004
- In front of the computer
If the user must choose a workflow trigger, then make sure that your triggers are supported by indexes. This will ensure that the workflows can use indexes to limit the number of rows that they need to check.
If the user can dynamically create workflow triggers (a much more complex situation), then I'd create both a new workflow trigger cost estimator that would allow your application to ensure that the amount of work that it will need to do to enforce the trigger will be manageable as well as a management report based on that cost estimator so that the administrator can either add indexes or disable rules/triggers if the cost gets too high.
-PatPIn theory, theory and practice are identical. In practice, theory and practice are unrelated.