Designing database for complex workflow applications


This article covers how to design the database for the application that involves complex non-sequential workflows. This is a follow up post to my first article written on designing sequential workflow database for business applications.


In this post, I chose simple task workflow that consists of three states – Ready, In Progress and Completed. As shown in the figure below, the task can be moved from Ready to In Progress or directly to Completed state.

Workflow States

Before going into the database design, I would like to give brief idea about the approach. Considering that application might have many workflows, we will need a master Workflow table to have store all the workflows. Each workflow can have multiple states, so we require a table to store the states and it has one to many relationship with the Workflow table. Next, these states must be linked together.

We need a table to store the navigation paths for each state in the workflow. For ex: Ready state can have two navigation paths – In Progress and Completed.

Finally, tasks table will hold all tasks in the application along with the current workflow state and WorkflowNavigation table helps to navigate to the next state.

Database Design

Workflow Database Design


This design is flexible enough to accommodate any changes to the workflow. For ex: if new state needs to be added to the workflow, configure navigation in WorkflowNavigation table by inserting records with possible states that it can navigate to and also add records for the existing states to link to the new state. This way new or existing tasks can use the new state based on their current state.

And same applies to deletion of an existing state as well. But I would advise not to delete the workflow state directly since it would impact the tasks that have it as their current state. Instead use IsActive column in the WorkflowStates table to identify the states workflow supports.

Additional Points

As you might have realized, there is a new table, TaskStateHistory, added to the design. It allows the task to go to previous states by storing the history of the task as shown in the figure below. To go to previous state, select the latest (using TOP operator) workflow state using the CreatedDate column.

Also, consider adding columns such as CreatedBy, CreatedDate, ModifiedBy, ModifiedDate, etc in tables as needed for audit purpose.


Workflow History


This article is not as descriptive as my first one since I just wanted to focus only on the subject. I would recommend reading the first one to get a little background.

Please do let me know your feedback or comments on the presented approach.

A small tribute to big achievers

Last month, I read an article on yahoo about damage caused by Joplin, Missouri tornado. I felt sad for the loss of many lives and as I read through the article, I came across couple of people who inspired me and I didn’t want to miss the opportunity to share it with you.

One amongst them was Will Norton, 18-year old young man graduated from the high school the day when the tornado struck. He and his father, Mark, were driving back home from the high school commencement ceremony and tornado has sucked him out of the vehicle. His dad tried to hold unto him but unfortunately he couldn’t make it. He went missing for five days and finally search ended after his body was found in a pond. His dad suffered multiple injuries and he is currently recuperating.

I felt sad for him for that moment, but when I gone through his profile and his achievements, I had smile on my face. I was awestruck with all his activities and achievements in such young age. He was a famous you tuber.

As a Christian, I am attracted to his last days of his life. He tweeted below verse day before the tornado.

“But about that day or hour no one knows, not even the angels in heaven, nor the Son, but only the Father.” Matthew 24:36

During his last moments in the car, he recited bible verses. This made me think that he knew his destination and he was well prepared for it. Praise the Lord! Though one can argue that it is mere coincidence, but the truth of death is inevitable and what matters is how we live this short life.

I was also inspired by their family. They made it feel that it’s no loss but he is in much better place, Heaven. His sister Sarah has done a great video sharing their experience about the incident. They are organizing several social activities in his memory.

Other person is Don Lansaw, also a victim of the Joplin tornado. He sacrificed his life for his wife, Bethany Lansaw, while covering her during the horrific incident and finally succumbed to injuries. It was a heart touching moment of true love and this video is the testimony of Bethany Lawson.

Solutions to common ASP.NET problems

Being an active participant in web forums, most of the time I see some common issues being raised by the developers. Some issues are within the ASP.NET (may or may not be documented) and some are just misconceptions. This post covers few among these issues based upon my experience.

  1. Fileupload control in ASP.NET AJAX Update panel

    Fileupload control doesn’t work in ASP.NET AJAX Update panel. Though it seems to work using synchronous postback trigger but it does full postback of the page. Instead use ASP.NET AJAX AsyncFileUpload control introduced in ASP.NET AJAX 3.5 which uploads the file through partial post back. 

    Also there are other free 3rd party controls available like swf upload, neat upload, etc., which displays status of the upload process and also asynchronously upload the file.

  2. Preventing duplicate insertion on page refresh or fire postback events twice on page refresh

    Consider a web application which has “New Customer” page, which is used to insert new customer into the database. Assuming the page is working fine i.e., upon clicking the submit button, a new record with the entered values is inserted into database. Now, by clicking/pressing “Refresh(F5)” button (may be accidentally), a new record gets inserted into database with same values hence duplicating the record but with new “CustomerId”. 

    For this problem there are various solutions like redirecting to other page or to the same page while page submission. Thanks to Terry Morton, she has posted an article which covers this topic in depth.

  3. Page is getting showed up even after logout

    Consider a web application with forms authentication implemented to authenticate a user. While signing out, the session and authentication cookie are cleared using Session.Abadon() and FormsAuthentication.Signout() methods and user is successfully redirected to login page or home page. 

    Now, when any anonymous user hits browser “back button”, again the previous page from which authenticated user signed out is visible but when some action is performed on the page like clicking a button or link on the page, it redirects them back to login page or home page. Though there is no harm but it’s not secure and is considered as bug in the application.

    Along with clearing session and authentication cookie, cache must be cleared. Check out the solutions given by Hajan and other developers in this thread.

  4. Roles attribute in each node in ASP.NET sitemap control

    Site node in the sitemap control which has “Roles” attribute makes us think it restricts the visibility of the node for the specified roles. Most of developers have mistaken its functionality. It is indeed reverse of it, the site map node shows up for those roles specified. Restriction is actually controlled by authorization but not through the attributes. Check out Dave Sussman’s article for detailed explanation regarding this and also my article which covered some of its detail by implementing it practically. 

    Hope you liked the post, please comment similar kind of issues to update the list. Happy coding J

Designing sequential workflow database for business applications


This article describes about an approach to design database for the objects which have sequential workflow in an application.


In most of the business applications, we come across certain items which have a workflow defined in the system. For example, in e-commerce applications, orders have a workflow starting from placing the order till the items getting shipped. The stages or the workflow of the object is defined by the business of the application. Workflows drive the system. So it is very important to have a flexible application to maintain the workflows. In this article, I tried to come up with the database design to manage such workflows.

We will define a simple workflow for orders and walk through the design process.

As the image infers the order workflow begins when a customer orders an item, then it gets processed and finally shipped.


Starting with “Orders” table, we can think of columns such as OrderId, CustomerId, CreatedDate, Status.

OrderId – Auto numbering column distinctly identify the orders

CustomerId – Identifies the customer who placed the order

CreatedDate – Date on which order is placed

Status – To maintain the current status of the order such as ordered, processed, shipped

We are now bothered about the “status” column in the “Orders” table which manages the workflow. As a novice developer, I tend to think of storing state of the order directly into the status column (varchar) like ‘ordered’, “processed” and ‘shipped’ which is a bad idea because somewhere we need to hard code the workflow (may be in stored procedure or in front end). For instance, maybe we need to check the current status of an order by using ‘if-loop’ and then hard code the next step.

To overcome it, we can plan to store an integer value in the status column and uniquely identify each state like 1-Ordered, 2-Processed, 3-Shipped. So whenever item moves to next stage current status is increased by 1. Going by first glance, it seems good. But we are again hard coding things, I mean how does a new developer know that 1 is for Ordered, 2 is for Processed and 3 is for Shipped. It is better we maintain another table namely “Status” with “StatusId” (Primary key) and “Name” columns. And we refer “StatusId” column in the “Orders” table as foreign key. Suppose an order which is under processing is having status ‘2’ and when it’s ready to ship, the status is incremented by ‘1’ and status changes to ‘3’. Ok done and all seems to be working well and solution is deployed.

One fine day, client wants to improve the efficiency of the system by introducing an additional state ‘Review’ before processing the order. Now the workflow of the order is changed to Ordered > Review > Processed > Shipped. Assuming that quite a number of orders are already in the database, will the above modification in the workflow fit in the existing database design? Let’s check it.

We can’t re-order the workflow by directly changing the “processed” status to “review” and altering the statusid of “processed” state to 3 and shipped to 4 since some orders already exists with processed or shipped status. Changing likewise will bring all shipped items to processed and processed items to review which isn’t good.

So we consider adding the new “review” status with status id as 4. Now after ordered (statusid = 1) it should be review (statusid = 4) not processed, so the above logic doesn’t work as we are incrementing the statusid each time the order state is changed. And again for that we need to hardcode the workflow in the stored procedure or front end to incorporate the modification.

Database has to be designed in such a manner that any modification like adding a new state or removal of existing state from the workflow can be easily done and shouldn’t impact the existing system. For that we add “NextStatusId” column to the “Status” table. “NextStatusId” column stores the “statusid” of the next state in the workflow. Below are their values for the above defined scenario.

By this database modification, the orders which are already having “Ordered” status will pick next state from “NextStatusId” column and will move to “Review” and from “Review” they move to “Processed” following the modified workflow. Whenever a new state is added or removed the “NextStatusId” ” of the affected states are altered accordingly.

It works well with the business applications which have sequential workflow defined through a certain single path. How about designing the database for the applications which have many alternative flows in each state or even switch from current state to other states. Stay tuned!

I would like to thank my ex-colleague Sumanth Pereji for reviewing the concept.

Please feel free to comment with alternative ideas or criticize the presented one.

Update: As promised, the article to design database for complex workflows is posted here.

Designing the folder structure of the ASP.NET web application


This article describes an approach to design the folder structure of ASP.NET web application and utilize the default features provided by ASP.NET framework.


Sometime ago I was working on a small web application which had limited business functionality. Below are the main points which had to be considered before starting with the coding.

  1. Application is accessed by only 3 types of users i.e., Anonymous Users, Authenticated/Registered Users and Administrators. User should be able to access their respective pages, without any hard coding to check the roles in individual pages.
  2. Since the scope of the application is minimal, implementing complex things like roles provider to check the role of logged-in user is not preferable and application should be able to manage it.
  3. Menu should automatically pickup and display the pages which user can access based upon his privileges.

This article mainly describes features in ASP.NET to meet the above requirements with ease.


Prior to start with the actual thing, I assume that authentication (Forms\Windows) is already implemented in the web application. This application uses forms authentication and if you’re interested to know more about forms authentication, check 1 & 2 for detailed explanation and click here for simple straight forward approach.

Let us start with implementation now.

  1. Below is the initial solution of the application.

  2. Pages marked in “green” should be visible to all users (Anonymous users), “blue” should be visible to Authenticated/Registered users and “red” should be visible to “Administrators” as shown below.

  3. Application uses forms authentication and for simplicity users are hardcoded in “web.config” file so that I can easily login and demo the features discussed in this article.

      <authentication mode="Forms">
          <forms name=".rexo" loginUrl="home.aspx" protection="All"  defaultUrl="~/user/contacts.aspx">
            <credentials passwordFormat="Clear">
              <!--Registered users-->
              <user name="tom" password="tom"/>
              <user name="jerry" password="jerry"/>
              <!--Admin user-->
              <user name="admin1" password="admin1"/>
              <user name="admin2" password="admin2"/>
  4. Our first aim is to restrict the web pages based upon their roles i.e., set authorization to the pages. For that, create a new folder “User” in the solution and place all the pages which can only be accessed by authenticated users of the application.
  5. Navigate to “web.config” file, set the authorization using “<location>” tag which restricts the anonymous users to access pages placed in “User” folder.
  6. <configuration>
        <compilation debug="false">
    <authentication mode="Forms">
          <forms name=".rexo" loginUrl="home.aspx" protection="All" defaultUrl="default.aspx">
            <credentials passwordFormat="Clear">
              <!--Registered users-->
              <user name="tom" password="tom" />
              <user name="jerry" password="jerry" />
              <!--Admin users-->
              <user name="admin1" password="admin1" />
              <user name="admin2" password="admin2" />
        </authentication>  </system.web>
    <!--This tag restricts the unauthenticated users to access pages placed in user folder -->
      <location path="User">
            <deny users="?"/>

    If it is messing-up root “web.config” file, add a new “web.config” file in “User” folder and directly write the authorization tag inside “<system.web>” section without specifying “<location>” tag.

  7. Now without logging into the application, try to access any page placed in “User” folder like “emails.aspx”. It’ll be redirected to login page with url of the page which is accessed being stored in “ReturnUrl” parameter of the querystring.
  8. And when username and password is typed, user gets navigated directly to emails page.

  9. Moving forward with our requirements, we don’t want all the registered users to be able to access “AdminTasks” and “Userslist” pages. Only “Administrators” needs to have access to these pages. This can also be done through configuration without implementing role manager. For that, we need to create another folder “Admin”, place the admin related pages in that and specify the authorization tag in the “web.config” file as below.

  10. This can also be achieved without creating a “web.config” file separately, by defining the authorization via <location> tag in root “web.config” file as we have done before for “User” folder. But for easy understanding, I chose this way.

  11. Next thing is to have a “menu” control which displays appropriate menu items for respective user. I mean if anonymous user is browsing the application, he should be able to see “Home”, “Feedback” & “AboutUs” pages and registered users should see “Contacts” & “Emails” pages and administrator should be able to see “Admintasks” & “Userslist” pages. Below is the sitemap file

    <?xml version="1.0" encoding="utf-8" ?>
    <siteMap xmlns="" >
        <siteMapNode url="default.aspx" title=""  description="">
          <!--Anonymous users-->
            <siteMapNode url="Home.aspx" title="Home"  description="" />
            <siteMapNode url="AboutUs.aspx" title="About Us"  description="" />
            <siteMapNode url="Feedback.aspx" title="Feedback"  description="" />
          <!--Registered users-->
          <siteMapNode url="user/contacts.aspx" title="Contacts"  description="" />
          <siteMapNode url="user/emails.aspx" title="Emails"  description="" />
          <!--Admin pages-->
          <siteMapNode url="admin/admintasks.aspx" title="Admin tasks"  description="" />
          <siteMapNode url="admin/userslist.aspx" title="Users List"  description="" />

    Before we go further, we’ll just browse the attributes of sitemap node. We can see “securityTrimmingEnabled” and “Roles” attribute which makes us think that if Security Trimming is set to “true” and by specifying list of roles in “roles” attributes, we are done with the task. But wait, we don’t have role manager implemented in the application which restricts us to use default features of sitemap control.

    All our assumptions are wrong!! I must say almost 99% of newbies of sitemap control will fall prey to this misconception. “Roles” attribute will make the sitemap node to show up for the specified roles instead of restricting the users.

    So how to restrict the menu items based on the roles of the user. In fact, we need not do anything to achieve the functionality. Sitemap control automatically picks up the authorization tags specified in “web.config” file and displays the menu items for the respective user.

    In root web.config, specify the sitemap provider and set “securityTrimmingEnabled” to true which is false by default.

      <siteMap defaultProvider="myprovider" enabled="true">
            <add name="myprovider" type="System.Web.XmlSiteMapProvider " siteMapFile="web.sitemap" securityTrimmingEnabled="true"/>

    Menu control is usually placed in a master page and set “datasourceid” property of menu control to sitemapdatasource.

    <asp:Menu ID="rexoMenu" runat="server" DataSourceID="SiteMapDataSource1" CssClass="menu2"
                            <asp:MenuItemStyle CssClass="menu2" />
    <asp:SiteMapDataSource ID="SiteMapDataSource1" runat="server" ShowStartingNode="false"  StartingNodeOffset="0"/>

    For more details regarding sitemap control and authorization tags check out Dave Sussman’s article.

Now let us browse the application.

Logged-in as registered user – (tom)

Logged-in as administrator – (admin1)

That’s it. I guess we are done with the requirements and good to start with coding. Happy Coding!!

Note: The concepts described in this article also work with Windows Authentication. And it applies to both ASP.NET website and web application project.


This article described some of the features in ASP.NET framework to be considered while designing the folder structure of the application.

I would like to thank everyone for encouraging me through your comments and mails for my first article. I am also glad that it has been published as article of the day on website.

Creating a new website programmatically on IIS using ASP.NET and C#


This article describes how to create a new web site on IIS using ASP.NET and C#.


Before going into the article, I just want to brief about the client requirement. It is a Content Management System which allows users to create new websites and host it on the server. Users can browse through the available domain names via a third party API and select a domain name and choose to host it on the server and then a new ASP.NET website should be created on IIS automatically and run within no time.

For each new website request, administrator cannot open IIS to create a new ASP.NET website. We require an instant solution where user enters website name and a new website should be created automatically on IIS with required settings like host name, local path, default document, etc., similar to the settings which a user does manually through wizard on the server.

Environment: Windows Server 2003, IIS 6.0, ASP.NET 2.0

For demo purposes, user interface is .aspx page with just a text box to enter website name and a submit button.

On the server side, web service does the job. It takes the website name as parameter and creates new website on IIS. And a point to be noted is all the new websites created point to a same physical location and depending upon the name of the website, content gets populated. If you need to configure different physical paths for each website, send an additional parameter to the web service.

Let’s start the job now

  1. Create a new “ASP.NET Web Service” with Language as “C#”.
  2. Open “web.config” file, add below configuration values under “appSettings” section.
      <!---Default format is IIS://<your server name>/W3SVC-->
      <add key="metabasePath" value="IIS://C37336-113134/W3SVC"/>
      <!--Framework version of newly created website-->
      <add key="frameworkVersion" value="2.0.50727"/>
      <!---Local path of newly created website -->
      <add key="defaultFileLocation" value="C:\Test"/>
      <!---Application Pool of newly created website-->
      <add key="defaultAppPool" value="CustomAppPool"/>


  3. Add Reference to “System.DirectoryServices” library and navigate to .cs file and import the namespaces “using System.DirectoryServices”.
  4. Create a new web method “CreateWebsite”
  5. /// <summary>
    /// Creates new ASP.NET website on IIS
    /// </summary>
    /// <param name="siteName"></param>
    /// <returns></returns>
    public string CreateWebsite(string siteName)
         //Initialize configuration variables
         string metabasePath =  Convert.ToString(ConfigurationManager.AppSettings["metabasePath"]);
         string frameworkVersion = Convert.ToString(ConfigurationManager.AppSettings["frameworkVersion"].ToString());
         string physicalPath = Convert.ToString(ConfigurationManager.AppSettings["defaultFileLocation"].ToString());
         string defaultAppPool = ConfigurationManager.AppSettings["defaultAppPool"].ToString();//Host Header Info
         object[] hosts = new object[2];
         string hostHeader = siteName.Replace("www.", string.Empty).Replace(".com",string.Empty);
         hosts[0] = ":80:" + hostHeader+".com";
         hosts[1] = ":80:" + "www." + hostHeader + ".com";
         //Gets unique site id for the new website
         int siteId = GetUniqueSiteId(metabasePath);
         //Extracts the directory entry
         DirectoryEntry objDirEntry = new DirectoryEntry(metabasePath);
         string className = objDirEntry.SchemaClassName;
         if (!className.EndsWith("Service")) return "Invalid configuration variables";
         //creates new website by specifying site name and host header
         DirectoryEntry newSite = objDirEntry.Children.Add(Convert.ToString(siteId), (className.Replace("Service", "Server")));
         newSite.Properties["ServerComment"][0] = siteName;
         newSite.Properties["ServerBindings"].Value = hosts;
         newSite.Invoke("Put", "ServerAutoStart", 1);
         newSite.Invoke("Put", "ServerSize", 1);
         //Creates root directory by specifying the local path, default  document and permissions
         DirectoryEntry newSiteVDir = newSite.Children.Add("Root", "IIsWebVirtualDir");
         newSiteVDir.Properties["Path"][0] = physicalPath;
         newSiteVDir.Properties["EnableDefaultDoc"][0] = true;
         newSiteVDir.Properties["DefaultDoc"].Value = "default.aspx";
         newSiteVDir.Properties["AppIsolated"][0] = 2;
         newSiteVDir.Properties["AccessRead"][0] = true;
         newSiteVDir.Properties["AccessWrite"][0] = false;
         newSiteVDir.Properties["AccessScript"][0] = true;
         newSiteVDir.Properties["AccessFlags"].Value = 513;
         newSiteVDir.Properties["AppRoot"][0] = @"/LM/W3SVC/" + Convert.ToString(siteId) + "/Root";
         newSiteVDir.Properties["AppPoolId"].Value = defaultAppPool;
         newSiteVDir.Properties["AuthNTLM"][0] = true;
         newSiteVDir.Properties["AuthAnonymous"][0] = true;
         //Sets the framework version to 2.0 for the new website
         //Assuming the version will be something like n.n.nnnnn
         Regex versionRegex = new Regex(@"(?<=\\v)\d{1}\.\d{1}\.\d{1,5}(?=\\)");
         PropertyValueCollection lstScriptMaps =  newSiteVDir.Properties["ScriptMaps"];
         System.Collections.ArrayList arrScriptMaps = new   System.Collections.ArrayList();
          foreach (string scriptMap in lstScriptMaps)
              if (scriptMap.Contains("Framework"))
                 arrScriptMaps.Add(versionRegex.Replace(scriptMap, frameworkVersion));
          newSiteVDir.Properties["ScriptMaps"].Value =  arrScriptMaps.ToArray();
          return "Website created successfully.";
      catch (Exception ex)
          return "Website creation failed. <br/>" + ex.Message;
    /// <summary>
    /// Retunrs Unique SiteId for the new website
    /// </summary>
    /// <param name="metabasePath"></param>
    /// <returns></returns>
    private int GetUniqueSiteId(string metabasePath)
         int siteId = 1;
         DirectoryEntry objDirEntry = new DirectoryEntry(metabasePath);
         foreach (DirectoryEntry e in objDirEntry.Children)
            if (e.SchemaClassName == "IIsWebServer")
                int id = Convert.ToInt32(e.Name);
                if (id >= siteId)
                siteId = id + 1;
         return siteId;
  6. Now take a new “ASP.NET website” and add the web reference to the created web service.
  7. In default.aspx, place a textbox control and button control and in button click event handler, call the web method by passing in textbox value.
    Service createWebsiteService = new Service();
    lblStatus.Text = createWebsiteService.CreateWebsite(txtSiteName.Text);
  8. Now get ready to run the application and enter a website name “” in the textbox and click the button. OOPsssss!!!
  9. Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))

    Don’t worry. We are not yet done with required configuration part yet.


Basically for creating a new website on IIS, user requires administrator privileges. But by default ASP.NET application runs under low privileged account i.e., Network Service, so additional settings needs to be done to run the above mentioned code.

Method 1

Remember, we have used two ASP.NET applications for this, one for website and another for web service. Now web service needs to be run under administrative privileges. Add below code in the “web.config” file under “system.web” section.

<identity impersonate="true" userName="adminname" password="adminpwd"/>

It impersonates the user request with administrator privileges and thus executes the code successfully.

Method 2

Typing administrator user name and password in web.config may not be feasible and less secured. No problem! We can do it in other way by creating a new application pool for the web service as explained below.

  1. Open IIS
  2. Right click on Application Pool > select “New” > select “Application Pool”
  3. Enter “Application Pool ID” and click OK. New application pool is created
  4. Right click on newly created application pool > select “Properties”
  5. Navigate to “Identity” tab, choose “configurable” and enter administrator user name and password and click “OK”
  6. Now configure web service to run under newly created application pool. Right click on already created web service>>Select “Properties” and navigate to “Home Directory” tab >>Select newly create application pool under “Application Pool” field

Note: Be careful with step no. 5 and 6, configuration issues occur while running the web service. This happens when invalid password is entered under the administrator account in application pool. In this case, modifying the administrator password doesn’t help. We need to again start configuration from beginning by removing the web service association with application domain and deleting the application domain and creating a new one with correct credentials.

For both methods, it is preferred to have new admin account which serves only this purpose and make sure he is member of  IIS_WPG user group. Because due to security reasons, password of existing administrator may change from time to time and for every reset we need to again re-configure the application pool.

That’s it. Now run the application and see the result.

Additional Inputs

  1. If time is not crucial part for new website creation on IIS, consider storing the website names in a database and implement the code written in the web service in a windows application (or) windows service and schedule it, so that the configuration part can be skipped.
  2. On Load Balancing servers, this won’t work because we don’t know where the request is routed. In this scenario, windows application/service is preferred.


This article demonstrated one of the concepts to create a new website on IIS programmatically using ASP.NET and C#.

Note: It is just the concept which is explained in this article but doesn’t include additional things like exception handling & web service security. Please feel to modify it according to your requirements.

It is my first article, please bear with me and it would be great help if you guys provide feedback and help me improve the content.


Get every new post delivered to your Inbox.