Powershell PathTooLongException


PathTooLongException could be triggered in various occasions when the path you are dealing with exceed 260 characters. For example while copying an innocent file to a destination with super long path. I hit PathTooLongException when I need to clean up a build in my Powershell build script.

For simplicity sake, I extracted the script that trigger PathTooLongException as following:

Here is our friendly error message:


Here is an example of what a (really) long path looks like:


We will not argue whether it is right or wrong to have such a long path. Often, we are given a piece of software to work on. We have little choice but to find the most efficient solution to deal with the situation.

PathTooLongException is known since 2006. It has been discussed extensively by Microsoft BLC Team in 2007 here. At the point of writing this, Microsoft has no plan to do anything about it. The general advice you will find is is: do not use such long path. (Wow! Really helpful!)

I have been digging around the web for solution but unfortunately there was little luck. Well, the truth is I wasn’t happy with many of the solutions or they are just unnecessary complicated. I eventually wrote my own “hack” after some discussion with Alistair Deneys.

The trick is to setup a short symbolic link to represent the root of the working directory. Pass the short symbolic link as target directory to Remove-Item. We will use mklink command to set up the short symbolic link.

The bad news is, mklink is a Operating System level command. It is not recognized in Powershell. I will have to open Command Prompt to set up the symbolic link before proceeding my Powershell build script. As my build script is supposed to be fully automated without manual intervention, manually setting up the symbolic link using Command Prompt is obviously an ugly option.

The following is the command we need to execute to set up the short symbolic link using Command Prompt:

The good news is we can call Command Prompt from Powershell to set up the symbolic link fairly easily. How easy?

Of course, we will need to ensure there isn’t a folder or another symbolic link which is already being named the same as our target symbolic link. (Else you will get another exception.)

Once we created the symbolic link by executing the above command, there will be a “shortcut” icon.


Prior to deleting the directory with a really long path, we will set up the symbolic link on the fly to “cheat” Powershell that the path isn’t really that long 😉

Obviously we don’t want to leave any trace for another person to guess what this “shortcut” is about especially the symbolic link is no longer required after our operation. Hence, once we are done doing what we need to do (in this case, I’m deleting some files), we will need to clean up the symbolic link.

There isn’t any dedicated command to remove a symbolic link, we will use the bare bone approach to remove the symbolic link / shortcut created earlier.

The interesting thing about removing a symbolic link / shortcut is it will remove it as a shortcut but the shortcut becomes a real directory (What?!). Hence we will need to remove it one more time! I don’t know what is the justification for this behaviour and I do not have the curiouscity to find out for now. What I ended up doing is calling Remove-Item twice.

Then, things got a little more sticky because Remove-Item will throw ItemNotFoundException if the symbolic link or the actual directory is not there.


Theoretically this should not happen because all we need to do is create a symbolic link, delete the symbolic link 2 times and we are clear. However reality is not always as friendly as theory 🙂 So, we need to script our Remove-Item defensively. I ended up creating a function to handle the removing task really carefully:

The full script looks as following:

Here, my Powershell happily helped me to remove the files which the path exceed 260 characters and I have a fairly simple “hack” that I can embed into my Powershell build script.

One Last Thought…

During my “hacking” time, I have also tried Subst and New-PSDrive. You might be able to do the same using them. However in my case, some of the dependencies didn’t work well with the above. For example, AttachDatabase could not recognize the path set by Subst. That’s why I settled with Mklink.

This obviously this isn’t a bullet proof solution to handle PathTooLongException. Imagine if the “saving” you gain by representing the actual directory is still not sufficient – as in the path starting from the symbolic link is still more than 260 characters. However I have yet to encounter such situation and this “hack” works for me everytime so far. If you have a simpler way to get this to work, feel free to drop me a comment. Enjoy hacking your Powershell!

Continuous Integration and Continuous Delivery with NuGet


Continuous Integration (CI) is a development practice that requires developers to integrate codes into a shared repository. Each commit will then be verified by an automated build and sometimes with automated tests.

Why Continuous Integration is important? If you have been programming in a team, you probably encountered situation where one developer committed codes that cause every developer’s code base to break. It could be extremely painful to isolate the codes that broke the code base. Continuous Integration serves as a preventive measurement by building the latest code base to verify whether there is any breaking changes. If there is, raise an alert perhaps by sending out an email to the developer who last committed the codes or perhaps notify the whole development team or even to reject the commit. If there isn’t any breaking change, CI will proceed to run a set of unit test to ensure the last commit has not modify any logic in an unexpected manner. This process sometimes also known as Gated CI, which guarantees the sanity of the code base in a relatively short period of time (usually within few minutes).


The idea of Continuous Integration goes beyond validating the code base in a team of developers working on. If the code base utilizes other development teams’ components, it is also about continuously pulling the latest components to build against the current code base. If the code base utilizes other micro-services, then it is about continuously connecting to the latest version of the micro-services. On the other hand, if the code base output is being utilized by other development teams, it is also about continuously delivering the output so that other development teams can pull the latest to integrate with. If the code base output is a micro-service, then it is about continuously exposing the latest micro-service so that other micro-services can connect and integrate to the latest version. The process of delivering the output for other teams to utilize leads us to another concept known as Continuous Delivery.

Continuous Delivery (CD) is a development practice where development team build software in a manner the latest version of software can be released to production at any time. The delivery could mean the software being delivered to a staging or pre-production server or simply a private development NuGet feed.

Why Continuous Delivery is important? In today software development fast pace of change, stakeholders and customers wanted all the features yesterday. Product Managers do not want to wait 1 week for the team to “get ready” to release. Business expectation is as soon as the codes are written and functionalities are tested, software should be READY to ship. Development teams must establish an efficient delivery process where delivering software is as simple as pushing a button. A good benchmark is the delivery can be accomplished by anyone in the team. Perhaps to be done by a QA after he has verified the quality of the deliverable or by Product Manager when he thinks the time is right. In complex enterprise system, it is not always possible to ship codes to production quickly. Therefore complex enterprise system is often broken into smaller components or micro-services. In this case, the components or micro-services must be ready to be pushed to a shared platform so that other components or micro-services can consume the deliverable as soon as available. This delivery process must be at READY state at all time. The decision of whether to deliver the whole system or the smaller component should be a matter of business decision.

Note that Continuous Delivery does not necessary mean Continuous Deployment. Continuous Deployment is where every change goes through the pipeline and automatically gets pushed into production. This could lead to several production deployments every day, which is not always desirable. Continuous Delivery allows development team to do frequent deployments but may choose not to do it. In today’s standard for .NET development, NuGet package is commonly used for either delivering a component or a whole application.

NuGet is the package manager for the Microsoft development platform. A NuGet package is a set of well-managed library and the relevant files. NuGet packages can be installed and be added to .NET solution from GUI or command line. Instead of referencing to individual library in the form of .dll, developers can reference to a NuGet package which provides much better management in handling dependencies and assemblies versions. In a more holistic view, a NuGet package can even be an application deliverable by itself.

Real life use cases

Example 1: Micro-services

In a cloud based (software as a service) solution, domains are encapsulated in the respective micro-service. Every development team is responsible for their own micro-services.


Throughout the Sprint, developers commit codes into TFS. After every commit, TFS will build the latest code base. Once the building process is completed, unit tests will be executed to ensure existing logic are still intact. Several NuGet packages are then generated to represent several micro-services (WCF, Web application, etc). These services will be deployed by a deployment tool known as Octopus Deploy to a Staging environment (hosted in AWS EC2) for QA to perform testing. This process continues until the last User Story is completed by the developers.

In a matter of clicks, the earlier NuGet package can also be deployed to Pre-production environment (hosted in AWS EC2) for other types of testing. Lastly, with the blessing from Product Manager, DevOps team will use the same deployment tool to Promote the same NuGet packages that were tested by QA earlier into Production. Throughout this process, it is very important that there is no manual intervention (such as copying a dll, changing a configuration, etc) by hands to ensure the integrity of the NuGet package and deployment process. The entire delivery process must be pre-configured or pre-scripted to ensure the process is consistent, replicatable, and robust.

Example 2: Components

In a complex enterprise application, functionalities are split into components. Each component is a set of binary (dll) and other relevant files. A component is not a stand-alone application. The component has no practical usage until it sits on the larger platform. Development teams are responsible for their respective component.

Throughout the Sprint, developers commit codes into a Git repository. The repository is monitored by Team City (build server). Team City will pull the latest changes and execute a set of Powershell script. From the Powershell script, an instance of the platform is setup. The latest code base will be built and the output is placed on top of the platform. Various tests are executed on the platform to ensure the component functionality is intact. Then, a set of NuGet package will be generated from the Powershell script to be published as the artifacts. These artifacts will be used by QA to run other forms of tests. This process continues until the last User Story is completed by the developers.

When QA gives the green light and with the blessing from Product Manager, the NuGet packages will be promoted to ProGet (an internal NuGet feeds). This promotion process happens in a matter of clicks. No manual intervention (modifying the dependencies, version, etc) should happen to ensure the integrity of the NuGet package.

Once the NuGet package is promoted / pushed into ProGet, other components update this latest component into their components. In Scaled Agile, a release train is planned on frequent and consistent time frame. Internal release happens on weekly basis. This weekly build will always pull all of the latest components from ProGet to generate a platform installer.


From the examples, we can tell that Continuous Integration and Continuous Delivery are a fairly simple concepts. There is neither black magic nor rocket science in both the use cases. The choice of tools and approaches to accomplish largely depend on the nature of the software we are building. While designing software, it is always a good idea to keep Continuous Integration and Continuous Delivery in mind to maximize team productivity and to have quick and robust delivery.

Facebook Feed Widget in Sitecore Habitat


In the previous post we have a introductory view on what Sitecore Habitat is. In this post we will get our hands dirty creating a Facebook Widget in Sitecore Habitat.

Out of the box, Sitecore Habitat contains number of Features. One of the Features is “Social” and we will use Sitecore.Feature.Social project in Habitat solution for Facebook Feed Widget. Before we get started, let’s examine what is available in Sitecore.Feature.Social project.


From here we can see that Social comes with certain implementation for Twitter feed. If we visit our Habitat instance, we can see there is a Twitter feed shows in the following page:


From here, we will create a Facebook feed to replace the Twitter feed.

First step, we create a new data template to cater for the fields for Facebook feed.


Click on image for full size view

Then, we create an item in Content Editor named FacebookDetails for setting the actual properties for the Facebook feed. In this example, we are using Sitecore Facebook page for demonstration purpose.


Click on image for full size view

The complete properties are as following:

FacebookUrl: https://www.facebook.com/Sitecore
Type: timeline
Width: 350
Height: 600
Hide Facebook Cover: [unchecked]
Hide Face Pile: [checked]
Title: Sitecore Facebook Feed

Type is a Droplist. Facebook feed supports multiple types, namely timeline, events and messages. By having Type as a Droplist, user can select which kind of feed to fetch from Facebook. However for simplicity sake, you can also configure your Type as a Single-Line Text. Doing that you will have to type in the Type field yourself each time you were to change the feed type. Not a show stopper, but something nice to have 🙂

Next, we will move to Visual Studio to tie the fields to a FacebookFeed View that we are about to create.

Look for Template.cs under Sitecore.Feature.Social project. Add the following struct into the class. Do note that the GUID in your instance will be different from the GUID displayed here if you are creating the fields by yourself. Hence, remember to replace the GUID in these properties. If you installing the Sitecore package provided at the end of this post, the GUID will remain the same.

This is the code that “tie” the fields we created earlier in Content Editor. The next thing we will do is to create a view that consume these properties’ GUID.

Add a new FacebookFeed View in Sitecore.Feature.Social project under Social folder.


Add in the following code to construct a Facebook Feed widget.

Note all the HTML attributes are the fields we created earlier in data template and the values are the fields we filled in FacebookDetail item in Content Editor.

Now, compile the solution (or project). Ensure that FacebookFeed.cshtml and Sitecore.Feature.Social.dll are copied over to your Sitecore instance. Again, if you are installing the package provided at the end of this article, you will not need to copy the files over.

At this point, we have successfully created created a Facebook Feed Widget. However the widget has not been configured to display on any page on our instance. Now let’s create and configure the necessary items in Content Editor for our Facebook Feed Widget.

Create a view rendering item to represent the View (.cshtml) we have created earlier.

Click on image to view full size

Click on image to view full size

Add FacebookFeed rendering view into Layout that we want to display the View from. For simplicity sake, we use “col-wide-1” as this layout is readily available on Social page.


Click on image to view full size

Next, we will now move to Experience Editor to replace the Twitter Feed with Facebook Feed Widget we have just created.

Navigate to Social page using Breadcrumb and remove the existing Twitter Feed.


Add the new Facebook Feed into the page. Check the “Open the Properties dialog box immediately” option:


Click on Browse under Data Source:


Select FacebookDetails and click OK


On your browser, navigate to http://habitat.local/en/Modules/Feature/Social and Facebook Feed Widget will appear in your page:


Obviously, this is the most perfect Facebook Feed Widget we can build (just kidding!). You are welcome to modify any steps to cater for your own Sitecore instance.

The objective of this article is to guide you through the steps in creating a Facebook Feeds Widget in Sitecore Habitat. If you would like to have a Facebook Feeds Widget in Sitecore Habitat without going through (most of) the steps, feel free to download and install the ready made package.

Download Sitecore-Habitat-Facebook-Feed-Widget package.

Sitecore Habitat


Sitecore released Habitat on 18th December 2015. Habitat is a Sitecore solution example built on a modular architecture. Habitat architecture and methodology focus on few main concepts which are Simplicity, a simple, consistent and discoverable architecture. Flexibility, the ability to change and add quickly and without worry. Extensibility, the ability to simply add new features without steep learning curve.

Sitecore Habitat is a new “demo” site provided by Sitecore. Prior to that there is LaunchSitecore.net which is another Sitecore “demo” site where public can evaluate what Sitecore is capable of. The major difference between LaunchSitecore and Habitat is that LaunchSitecore is designed to allow public to explore the core functionalities in Sitecore, while Habitat is a modular architectural solution to build features.

To get a quick look and feel what Habitat is about, you can download Sitecore Habitat package and install it on your local Sitecore instance. Once installed, your Sitecore instance home page will have the following look:

sitecore habitat homepage

Now, let’s dive a little deeper into Habitat code base. You can clone a new repository of Habitat from GitHub.

Once cloned, you will see from Visual Studio there are 37 projects in the solution. The “magic” of Habitat sits at the Feature folder. 14 features are provided out of the box (at the point of writing). They are: Accounts, Demo, Identity, Language, Media, Metadata, Multisite, Navigation, News, PageContent, Person, Search, Social and Teasers.

sitecore habitat feature

The idea of the Habitat architecture is you can develop or use a feature independently. If one feature is broken, you can simply not use it and it will not affect the rest of the feature. Each feature has the consistent architecture to allow development practices to be more predictable.

If you are into developing a feature in Habitat, I highly recommend you to look at the videos prepared Thomas Eldblom on:

Sitecore Habitat Architecture – Introduction

This video explains how Sitecore Habitat is solution framework that focuses on streamlining development process and optimizing productivity. It also explains the how productivity is increased when developers simplify coupling.

Sitecore Habitat Architecture – Modules

This video explains the closure principles in Sitecore. It digs down deeper into Habitat code base by using Navigation as an example. It explains the Views, Controllers, Data Model and how the items are structured and how do they get built.

Sitecore Habitat Architecture – Layers

Focus on the layered architecture and how to couple modules to build the entire solution. It also explains how different layers fit together as a solution and how different modules and layer interact with each other.

This is a quick introduction to Sitecore Habitat. If you would like to find out more, feel free to visit the Habitat Wiki page.

On the next post, we will dive a little deeper into the code base to build a Facebook widget in Sitecore Habitat.

Scrum Part 3 – The Team


SCRUM team members consist of 3 main roles: Product Owner, Scrum Master and The Team. Each role are closely inter-related. It is recommended for Scrum team members to be physically sitting together in the same location whenever possible.

Product Owner

product-ownerProduct Owner (PO) is the person who is ultimately responsible for the product, which could be a system, a small piece of software or even a component. He often understands the historical evolution of the product, well versed with the product current functionality, and define the vision of what the product will eventually be.

As the ideology of Scrum is to build a potentially shippable product at the end of every Sprint, PO is the person who ultimately decides whether or not to ship the product. Whatever decision PO made, he is answerable to the business in the larger scope. The decision made by Product Owner are often influenced by other Stakeholders (or Subject Matter Experts) and the business owner.

Ideally, PO is the person who is much closer to the customers (both internal customers or external customers). He understands the business challenges the customers are facing. He will communicate these business challenges to The Team by writing user stories so that The Team can build the necessary features.

A user story follows the following format:

As a <type of business user>,

I want <to perform certain task(s)>

so that I can <achieve some goals / benefits / values>

For example:

As a content editor

I want to be able to update my company website content without changing the source code

so that I can frequently let the visitors know the latest promotion

During Sprint Planning and Spring Grooming session, PO will provide as much details as possible to The Team related to the stories. At this point, PO would have completed writing each story and identify what is the priority for each story so that The Team can determine which story to pick first in Sprint Planning.

Some Product Owners who have a strong technical background might choose to write code together with The Team. However, he might not be able to commit on complicated stories or large number of stories due to his other obligations within the business. There are also some Product Owners who do not code but will participant in testing to work very closely with The Team.

In some organization structure, a business analyst might play a portion of Product Owner’s role due to the inaccessibility to Product Owner. Issues such as time zone difference or other business commitment might not allow the Product Owner to have high availability for The Team. Business analyst will step in to clarify the questions The Team has. Whenever business analyst is in doubt, he will clarify the matters with Product Owner.

Scrum Master

scrum-masterScrum Master is responsible to resolve any problem for the team members throughout a Sprint. Scrum Master is expected to understand different Scrum ceremonies better than the rest of the team members so that he can make the right call when conflicts arise. Scrum Master is the person who enforces the prescriptive guidelines in Scrum to ensure the Scrum team is able to reap the most values for adopting Scrum.

Having said that, Scrum Master does not have to be the most senior or most technically competent person in the Scrum team. In fact, Scrum Master might not even know technical at all. Scrum Master’s main responsibility is to clear out obstacles for The Team and enforce Scrum guidelines throughout the Sprint.

Now, let’s walk through Scrum Master’s specific responsibilities throughout the Sprint…

Sprint Grooming

Prior to the Sprint Grooming, Scrum Master has to ensure Product Owner has completed writing every story and priority has been placed on every story.

Scrum Master has to call for a Sprint Grooming few days prior to Sprint Planning. A few days of buffer should be allocated before Sprint Planning because Sprint Grooming sometimes cannot be completed in a single session. During Sprint Grooming Scrum Master needs to ensure The Team fully understands the stories written by Product Owner and ensure The Team has all necessary resources to kick start the Sprint.

After Sprint Grooming, Scrum Master has to call for another session for The Team to estimate the story point for each story. Often in Scrum team there are 1 or 2 team members who are more senior or has deeper domain knowledge with the product. They will indirectly influence the story point estimation given by other team members as the less senior team members tend to look up to them. Scrum Master has to spot this early enough and encourage the junior team members to independently provide story point estimation and justify why a story should have a higher (or lower) story point. Scrum Master has the responsibility to ensure all team members are working in harmony rather than domination by senior team members.

Sprint Planning

During Sprint Planning, Scrum Master has to facilitate the Scrum team to finalize the stories to be committed in the coming Sprint.

In the perspective of Scrum, stories to be committed are always in accordance to priority defined by Product Owner. Among all prioritized stories, together with their story point, The Team will commit to the stories with highest priority and make a cut off at the point of hitting The Team capacity (velocity). Occasionally, 1 or 2 stories will be reshuffled for the best match of priority and story point to fit the team capacity within one Sprint. This has to be done with the acknowledgement of Product Owner.

Scrum Master’s responsibility during Sprint Planning is to ensure the above guidelines are followed. During Sprint Planning, Scrum Master has no say over whether to commit or not to commit certain story. The decision is entirely depend on the discussion between Product Owner and The Team. Again, Scrum Master role is to facilitate this process and to conduct a smooth planning session.

Daily Stand Up

Daily stand up is a quick 15 minutes catch up for The Team. Scrum Master has to ensure the following questions are answered by each team member:

  1. What did I accomplish yesterday?
  2. What will I do today?
  3. What impediment am I facing?

On Question 1: Under healthy progress, everyday every team member would have completed certain task to contribute to the completion of the stories. However, if Scrum Master notices a team member has not been able to accomplish anything in the last working day, it’s a sign that the team member is stuck at certain task. Scrum Master has the responsibility to find out what cause it and assist the team member to resolve it.

On Question 2: Under healthy progress, everyday every team member will pick a new task or continue working on the remaining task from yesterday. However, if Scrum Master notices a team member do not have the next task to move to, it is a sign that the team has committed less story than The Team velocity allows. If such situation persists, Scrum Master has to consider increasing team velocity in the next Sprint and take on more stories. In some occasions, a team member dare not to pick up certain task because it is too difficult / not enough domain knowledge / not sure what is the solution, Scrum Master has to identify the appropriate resource and make necessary arrangement to help the team member to proceed with the story.

On Question 3: Under most circumstances, team members will report there is no impediment. When a team member raises an impediment, Scrum Master should not dismiss the impediment lightly and should pay serious attention to resolve the impediment. It is Scrum Master’s responsibility to make use of his resourcefulness to make the necessary arrangement to get the impediment resolved as soon as possible to allow The Team to continue their progress.

Scrum Master should not dominate Daily Stand Up. A good Scrum Master will maintain his silence during Daily Stand Up but he is also quick to voice up when there are team members diverting from objective of Daily Stand Up.

Sprint Review

Sprint Review is a session driven by The Team to demonstrate the deliverable (completed stories) to Product Owner. Scrum Master involvement during Sprint Review is often minimal.

Sprint Retrospective

Sprint Retrospective is the session to reflect what happened in the last Sprint. Scrum Master needs to get The Team to cover the following topics:

  1. What went well?
  2. What went wrong?
  3. What can be improved?

During the Sprint Retrospective, Scrum Master has to maintain a safe and harmonious environment for team members to speak up.

Scrum Master also has to ensure the team members understand the purpose of Sprint Retrospective is to provide continuous improvement for the coming Sprints rather than finding fault happened in the last Sprint. Therefore finger pointing should be avoided. Scrum Master has to ensure The Team carrying out the session to constructively identify the root cause of “what went wrong” and openly identifying “what can be improved” in the future Sprints.

Scrum Master has to take note of the items being discussed during Sprint Retrospective. Sometimes The Team will treat Sprint Retrospective as a good chat but forget about what has been agreed to do by the next Sprint. In this situation, Scrum Master has to step in to remind The Team that those are the items have been agreed upon and The Team should honor the agreement made during Sprint Retrospective.

In newly established Scrum team, The Team might be uncomfortable to speak up during Sprint Retrospective. A workaround for this scenario is for Scrum Master to request every team member taking turn to give at least 1 point for the 3 topics. This is also useful for matured Scrum team as The Team members often have many points to bring up. Having the team members to take turns to speak is an information flood control.

Of all the ceremonies in Scrum, a Scrum Master should NEVER dominate any session. In fact, Scrum Master is known as the servant leader. Scrum Master is the person to serve the team to ensure all team members are able to carry out their tasks efficiently.

In matured Scrum team, Scrum Master often play a passive role to monitor The Team is following Scrum guidelines. However in a newly established Scrum team (or team members who are less familiar with Scrum), Scrum Master has to play an active role to ensure The Team members are following the appropriate Scrum guidelines. If team members are diverting from Scrum guidelines, Scrum Master have to be quick to speak up to enforce Scrum guidelines and explain the purpose of the specific guidelines.

The Team

the-teamThe Team is the main driver in a Sprint. The Team typically consists of Developer, Quality Assurance, Architect, Business Analyst and sometimes even Graphic Designer, Database Administrator to work together to deliver the potentially shippable product at the end of Sprint.

The Team is cross functional. The Team is able to step into different functional areas to ensure an user story is fully implemented. For example if The Team is handling Accounting module in a system. When a user story involving Sales module, The Team will be able to step into Sales module do the necessary work,.

In an ideal Scrum team, everyone in The Team is equal. There is no difference in title, role or seniority. Scrum does not recognize whether a member is a Developer, Quality Assurance, Architect, Business Analyst, or Designer. Everyone in The Team is equivalently qualified to pick up any story. Everyone in The Team should be able perform designing work, coding, testing, writing documentation, etc. The idea is to take on the full stack of tasks in a user story. However, this is often impractical in today’s many organizations.

In today’s many organizations, hiring process is role base. For example, the hiring manager might put up a job vacancy looking for “Developer with 1-3 years of ASP.NET & C#.NET experience” or “Quality Assurance with 1 years experience in building automation using Selenium”. Many development teams are not ready to embrace the ideal Scrum ideology where every member can do full stack of tasks in the story.

With that, a less drastic and practical approach to embrace Scrum value of “everyone is equal” is by actively encouraging team members to step into different roles within The Team.

The idea is to allow team members to step into different roles and doing different tasks they are not “officially” responsible for. For example, an Architect is encouraged to actively code and implement user stories; a Developer is encouraged to help out in testing; a Business Analyst is encouraged to help out in testing, a Quality Assurance is encouraged to help out in documentation, etc.

When team members step into different roles within team, team members learn to see the development task from a different perspective and be able to communicate from the perspective of different roles. Not only this will keep team members’ job interesting, but also enhance team members’ career growth by having wider scope of development experience.

Let’s walk through the main responsibility for The Team during different phases:

Sprint Grooming – All members in the team should get good comprehension with every story. All questions related to user story implementation should be clarified by team members. Junior members tend to rely on the senior members to “figure out” the story. This should be avoided. Again, every team member is equal in a Scrum team. Every team member should understand every story the best they could because any team member is expected to be capable to pick up the story later to implement it.

Estimation – All team member has the equal right to decide what story point the story should have. Junior members tend to look up to senior members and follow their story point estimation. This should be avoided. Every team member is encouraged to provide independent estimation. When estimation is significant varied, the team member is encouraged to provide justification why the given story point is significant higher or lower. Junior team member might not necessary know less on specific scope of the story. The purpose of such explanation is to allow the rest of the team members to identify aspects they might have overlooked or unaware.

Sprint Planning – Assuming Sprint Grooming is fruitfully conducted and estimation is agreed earlier, Sprint Planning will be a smooth process for The Team to formally commit to a set of user story up to The Team capacity. Product Owner might shuffle around a few stories, The Team will have to provide their insight whether such arrangement is feasible.

Daily Stand Up – Once the Sprint started, team members will pick up story and implement the full stack story. On daily basis, team members will gather at the same time to report the following questions to the rest of the team:

  1. What did I accomplish yesterday?
  2. What will I do today?
  3. What impediment am I facing?

The purpose of Daily Stand Up is for team members to be aware of what each other are working on to avoid redundant effort. It is also a platform for The Team to expose any problem early enough for the whole Scrum Team to act on.

Sprint Review – is the time for team members to proudly demonstrate what they have accomplished in the Sprint. As the full stack of tasks for a particular story is implemented by 1 or 2 team members, the respective team members naturally have strong ownership on the story. It is best for the respective team member(s) to demonstrate the completed story to Product Owner during Sprint Review. Product Owner will provide feedback whether he is satisfied with the deliverable. Team members will listen to PO feedback and take necessary actions base on the feedback.

Sprint Retrospective – is the time for team members to reflect back what happened throughout last Sprint by answering:

  1. What went well?
  2. What went wrong?
  3. What can be improved?

Team members are encouraged to acknowledge other team members who has done well. This is not only subjected for the senior members to recognize the junior members effort. Junior members are also encouraged to identify other team members who have done a good job in the last Sprint.

When addressing what went wrong and what can be improved, team members are encouraged to be courageous to speak up yet be compassionate while speaking. A Scrum team is very much like a working “family” team. If there are problems need to be addressed in a team, it is best for the issues to be resolved as soon as possible in humane manner. Improvement such as processes, resources availability, better approach to deal with a issues are to be discussed during Sprint Retrospective.

This concludes the series of Scrum article. If you find the information useful, please share it. If you would like to discuss specific aspect of Scrum or have certain scenario would seek my advice, please leave me a comment or go to Contact Daniel form to send me a personal email.

Json Data in SQL Server


The rise of NoSQL database such as mongoDb is largely due to the agility to store data in non-structured format. A fixed schema is not required like traditional relational databases such as SQL Server.

However, NoSQL database such as mongoDb is not a full-fledged database system. It is designed for very specific use cases. If you don’t know why you need to use NoSQL in your system, chances are you don’t need to. For those who find it essential to use a NoSQL database, often they only use NoSQL database for certain portion of their system and then use another RDBMS for the remaining part of their system that have more traditional business use cases.

Wouldn’t it be nice if RDBMS is able to support similar data structure – having the ability to store flexible data format without altering database tables?

Yes, it is possible. For years, software developers have been storing various JSON data in one table column. Then, developers will make use of library such as Newtonsoft.Json within the application (data access layer) to deserialize the data to make sense out of the JSON data.

Reading / Deserializing JSON

This works. However “JsonConvert.DeserializeObject” method is working extremely hard to deserialize the whole JSON data to only retrieve a simple field such as Name.

Imagine there is a requirement for searching certain Genres on a table that has 1 million row of records, the application codes will have to read 1 million row of records, then perform filtering on the application side. Bad for performance. Now imagine if you have a more complex data structure than the example above…

The searching mechanism will be much efficient if developers can pass a query (SQL statement) for database to handle the filtering. Unfortunately SQL Server does not support querying JSON data out of the box.

It is impossible to directly query JSON data in SQL Server until the introduction of a library known as JSON SelectJSON Select allows you to write SQL statement to query JSON data directly from SQL Server.

How JSON Select Works

First you need to download an installer from their website. When you run the installer, you need to specify the database you wish to install this library at:


What this installer essentially does is to create 10 functions in the database you have targeted. You can see the functions at:

SSMS > Databases > [YourTargetedDatabase] > Programmability > Functions > Scalar-valued Functions


Next, you can start pumping in some JSON data in your table to test it out.

I create a Student table with the following structure for my experiment:


In my StudentData column, I enter multiple rows of records in the following structure:

For demonstrating the query purpose, I have entered multiple rows as following:


If you want to write a simple statement to read the list of student names in JSON data, you can simply write:

You will get result as following in SSMS:


How about more complex query? Does it work with Aggregate Functions?

If you want to find out about how many students come from each city and what is their average age, you can write your SQL Statement as following:

You will get result as following in SSMS:


It appears the library allows you to query any JSON data in your table column using normal T-SQL syntax. The only difference is you need to make use of the predefined scalar-valued functions to wrap around the values you want to retrieve.

Few Last Thoughts…

  1. The good about this library is it allows developers to have hybrid version of storage (NoSQL & relational database) under one roof – minus the deserialize code at application layer. Developer can continue using the classical RDBMS for typical business use cases and leverage on the functions provided in the library to deal with JSON data.
  2. The bad about this library is it lacks proven track record and commercial use cases to demonstrate the robustness and stability.
  3. Although the library is not free, the license cost is relatively affordable at $AU 50. However the library is free for evaluation.
  4. SQL Server 2016 provides native support for JSON data. This library is only useful for SQL Server 2005 to 2014 where upgrading to 2016 is not a feasible option.

Scrum Part 2 – Ceremonies



In Scrum Development – Part 1, we discussed the fundamental of Agile development which covers 4 Agile Manifesto. Lacking understand of Agile Manifesto often lead team members to practice Scrum ceremonies for the sake of practicing – without realizing the purpose of each ceremony. If your intention is to run a successful Scrum team, I highly recommend you to grasp a solid understanding of the 4 Agile Manifesto before moving to the prescriptive ceremonies in Scrum.

Guidelines for Scrum Team

  1. Each Sprint typically runs for 2 weeks. The duration of a Sprint could be from 1 week to 4 weeks. However through experience, 1-week Sprint is too short that leads to rushing and too much overhead while 3-week or 4-week Sprint is too long that leads to unnecessary idle time and big-bang implementation and deployment which makes Sprint harder to manage. A 2-week Sprint is ideal in most project.
  2. An ideal size of a Scrum team consists of 1 Product Owner, 1 Scrum Master and 7 (with +/- 2) team members. A team with less than 5 team members is too small to build anything significant; while a team of more than 9 team members are little too large to manage. Depending on the scope of the project and resource availability in organization, 7 (+/- 2) team members is not a stone-carved law to follow but rather a best-practice guideline.

Prescriptive Ceremonies

Sprint Grooming


Sprint Grooming is a pre-sprint activity that takes place before sprint starts. During grooming session, team members would clarify the stories written by Product Owner. Completed stories (or at least the majority part of the story) must have been completed by Product Owner, so that team members could clarify issues regarding implementation (either functional or technical or both).

The following is the format of how a story should be written:

As a <type of business user>,

I want <to perform certain task(s)>

so that I can <achieve some goals / benefits / values>

During this stage, the team might need some time to carry out research to evaluate whether a story could be implemented and how much effort is required. On the other hand, Product Owner could encounter edge scenario asked by the team. Product Owner potentially need time to check with the respective stakeholders to finalize a decision. Although such situation is expected, a matured Scrum team and a seasoned Product Owner who understand business use cases well would keep such get-back-to-you-later discussion to minimal. Sprint grooming could take place for multiple sessions depending on how productive each session went.



Before Sprint Planning begins, team members would have all questions in stories answered and have finalized the story point for each story. Note that estimation should NOT be hour estimation. Estimation done in hours defeat the purpose of Scrum. Scrum estimation is to be done using relative size. Tools such as Poker card or T-shirt size are good options to measure the complexity of story. In order for relative estimation to work, team members must have a common ground on the complexity of base point of 1. For example, if 1 point is equivalent to X amount of complexity, a story that carry 5 points means 5 times more complex than the 1 point story. 5 times more complex does not necessary means it takes 5 times the effort to complete it. Again, the base point of 1 is vitally important to form the base understanding among team members. Without defining base point of 1, story point estimation would be meaningless.

If you have a smartphone, you can download Scrum Poker Cards app from Google Playstore or iTune.

Sprint Planning


During Sprint Planning, team members and Product Owner would finalize which story to be committed in the Sprint. Depending on the team velocity, total story point and priority of each story, Product Owner (with the feedback and influence of team members) would select the best combination of stories to be committed for the Sprint. Note that requirement clarification should be kept minimum during Sprint Planning as requirement clarification should have been completed during earlier Sprint Grooming session. The more requirements was clarified during Sprint Planning session, the less accurate the story point estimation represents.

Daily Stand Up Meeting


The purpose of daily stand up provides a platform for the team to expose potential problem early enough. During stand up meeting, 3 questions would be covered by each team member:

  1. What did I accomplish yesterday?
  2. What will I do today?
  3. What impediment / obstacle am I facing?

Question #1 is designed to ensure team member accomplished what he has committed to the rest of the team on the previous working day. Having the team member to proactively report his progress is a much more motivating approach compared to having a traditional project manager to ask “Have you completed Task X?”. It gives a sense of pride to the team member for accomplishing tasks and announcing them to the rest of the team.

Question #2 is designed to ensure team members are moving forward everyday throughout the Sprint. By having team members to commit to story on their own will, they would naturally have a higher sense of ownership and accountability for the stories they have committed.

If everyone is moving forward together, then success takes care of itself. -Henry Ford

The purpose of Question #1 and #2 are to ensure the team members are being productive everyday. When Scrum Master notices a team member has been stuck with the same task for longer than expected, it is a potential signal that the team member is experiencing difficulty. A seasoned Scrum Master would spot this early to resolve it quickly rather than getting unpleasant surprises at the end of the Sprint.

Question #3 is designed for team members to raise any obstacle they are facing. Often in a less matured Scrum team, all members would say “No impediment” during stand up meeting. A “No impediment” response does not necessary mean no problem. The team members are sometimes too shy to raise the obstacle during stand up meeting. This is a common observation for newly established Scrum team or new team member joined the team. In more matured Scrum team, team member would proactively raise obstacles during stand up and quickly resolve them.

An important note to remember during daily stand up is the “people” element. The focus should be placed on the team members to allow them to keep moving forward. A poorly run Scrum team shift the focus away from the team members to the status of tasks completion. This is due to some traditional project managers are not familiar with Scrum approach and feel more comfortable getting tasks status update. Through experience, focusing too much on tasks status update is a less efficient approach to manage Scrum team which often lead to 11th hour additional tasks because team members are “trained” to close a task rather than to complete a task. A more efficient approach is to focus on the team members’ ability to progress. Highly motivated team members would go all the way to help each other and do whatever it takes to complete all committed stories. At times, the team would even resolve obstacles before the Scrum Master is aware of them.

One of the most important role of Scrum Master is to create a supportive and safe environment for team members to speak up during daily stand up. Blaming particular team member or finger pointing must be avoided at all cost. However, that does not mean being lenient while dealing with challenges. The guideline is be critical on issues, be kind to people. Scrum Master should not dominate daily stand up meeting discussion. Scrum Master is playing the role of a “referee” to ensure the team members are getting the most value out of Scrum ceremonies. A Scrum Master in a matured Scrum team would merely serve as an observer and only speak up when help needed. A Scrum Master is also known as a servant leader. (More on Scrum Master’s role will be covered in Part 3 of the Scrum article series.)

15 minutes

Daily stand up is designed to run for 15 minutes or less – that’s why team members should physically stand up during the meeting. Sitting comfortably encourages team member to drag the meeting to longer than require. The act of standing up to conduct the meeting encourages team members to finish the meeting in 15 minutes or less (because it would be too painful to stand for too long).

Often, team members are tempted to discuss specific issue with another team member during stand up, which potentially be a waste of other team members’ time. Story specific discussion should be taken offline among the team members working on the story.

Although all team members standing up together physically might not be feasible involving remote team members, the fundamental objectives of daily stand up should not be diverted due to physical limitation.

Sprint Review

Sprint Review

Sprint review is the activity where the whole Scrum team demonstrates the completed stories to Product Owner. The team member who implemented of the stories is responsible to demonstrate the respective stories. Stakeholders from various departments would often join the review / demo session to understand the newly implemented stories.

The objective of Sprint Review is to provide product incremental changes to Product Owner and stakeholders, so that there will be no big-bang surprises. Imagine in a non-Scrum approach of development (say Waterfall), a team works on a module for 6 months and when they finally demonstrate the working module to the customers, the customers feedback “No, this is not what we want”. 6 months of development effort would go down to drain. Sprint Review serves as a milestone checkpoint to allow Product Owner and stakeholders to acknowledge the team is moving towards the right direction and they are happy with each milestones.

At the end of Sprint Review, Product Owner would provide feedback whether he is happy with the work and decide whether to ship out the product. Remember, in Scrum, at the end of every Sprint there must be a potentially shippable product. However, whether to ship the product or otherwise is entirely up the Product Owner.

Sprint Retrospective


Sprint Retrospective is the meeting where team members reflect back how well did the Sprint go and discuss if there is any room for improvement.

The retrospective includes 3 main questions for discussion:

  1. What went well?
  2. What went wrong?
  3. What can be improved?

Question #1 is designed to acknowledge the good doing happened during the Sprint. A team member who has gone the extra miles to help another team member should be acknowledged. An unforeseen obstacle during the Sprint has been proactively resolved by the team should be acknowledged. The idea is to acknowledge the team members who has done an outstanding job during the Sprint and encourage the team to continue the good teamwork.

Question #2 is designed to identify the areas for improvement. No problems should ever be swiped under the carpet. If there was an unpleasant incident due to process inefficiency should be highlighted. If there was an external interruption that caused the reduction in team productivity should be highlighted. The idea is to identify what are the pain points for the Scrum team, so that the team could optimize and increase the team velocity.

Question #3 is a continuation of Question #2. The issues identified earlier should be discussed and identify a solution by the team, so that the same problem would not happen in the coming Sprint. The idea of Question #2 and #3 are to strike for continuous improvement in the Scrum team. No matter how good a Scrum team is, there is always room for improvement. This concept also known as Kaizen (改善) which means improvement for the better.

Often in less matured Scrum team, team members are less likely to speak up what’s in their mind. Scrum Master could consider enforcing every team member to take turn answering all three questions above to get team members be more comfortable speaking during Sprint Retrospective. Even if the team member says something like “Same with the other guy” is a good start. In more matured Scrum teams, team members would proactively prepared a list of topics (along the line of 3 questions) to bring into Sprint Retrospective for discussion.

One Last Thought…

While Scrum ceremonies are the activities that make a Scrum team, team that practices Scrum ceremonies for the sake of practicing gains little value for running project in Scrum. It is important to remember the underlying objective of each ceremony to gain the most out of these carefully designed ceremonies. In Part 3, we will discuss more about The Tools and The Team (people) in Scrum.

Scrum Part 1 – Agile Manifesto



Scrum is an iterative and incremental agile software development methodology for managing product (software) development. We cannot discuss about Scrum without understanding Agile. In fact, many people could not run Scrum successfully because they do not have a clear fundamental understanding about Agile manifesto. When we understand the idea of Agile, all the prescriptive activities / ceremonies in Scrum would make better sense. First let’s look the 4 manifesto for Agile software development:

  1. Individuals and interactions over processes and tools
  2. Working software over comprehensive documentation
  3. Customer collaboration over contract negotiation
  4. Responding to change over following a plan

Individuals and interactions over processes and tools

Individuals and interactions over processes and tools

In development team, Agile encourages team members to interact with each other such as talking face to face, instant messaging, or email communication – instead of heavily depend on project management tool such as Trello, Asana, JIRA, etc. For example, if a team member notice there is a defect in the code written by another team member, he is encouraged to talk the other team member directly (maybe to discuss the root cause and possible solutions) instead of creating a ticket, assign to him in JIRA and call it a day.


When individual interaction take precedence, team members build the bonding to cover each other back. When processes and tools take precedence, team members would only look at their own plate and teamwork rarely happens. Having said that, it does not mean processes are not important. They are important to facilitate the operation but processes and tools should not be the central of development team. It is the team members who are building the product (software), the processes and tools are merely there to facilitate them to get their job done.


Working software over comprehensive documentation


Logically speaking, would you rather to have a working software, or a pile of comprehensive documentation? Theoretically it is best to have both. Unfortunately projects often lacks the most precious commodity – time. If the team has to choose either one, it should always be working software (duh?). First, documentations are often outdated and team members are unconsciously trained to not rely on documentations because the perception is documentations are inaccurate anyway. Second, it is much easier to navigate a working software than to navigate a 200 pages functional specification document. Documentations are often the poor fella sitting at the shared drive that no one looks at. Always choose to build a working software over writing comprehensive documentation when facing the challenge of limited time. After all, what is the purpose of documentation if the software is not working?

Customer collaboration over contract negotiation

Customer collaboration over contract negotiation

This might not be directly relevant to the team members who are actively building the product (software). It is a mindset the business decision makers should have. When business negotiates a contract with customer (regardless internal or external), a set of objective or functional requirement is often agreed upon. However with today’s speed of change in market, circumstances tend to change faster than business could anticipate. It is impractically for business to re-negotiate a new contract whenever a change take place. Both the development team and business must understand that collaboration is what makes great software. Customer has the understanding of the market to describe the requirements; while development team has the skill to transform requirements into features.


Collaboration with customers is one of the fastest ways to make customers happy. There are some customers from hell but those are exceptions. Generally customers do find greater satisfaction if customers are aware that development team put their business success in their best interested over making money from them with a contract.

Responding to change over following a plan

Responding to change over following a plan

If Plan A doesn’t work, don’t worry, the alphabet has 25 more letters. The ability to respond to change is the core of Agile. Thanks to today’s speed of change, the only constant in the market is change. If internal speed of change is slower than external, the business is doom to fail. So if things tend to change so often, why bother to have a plan? A plan is made base on best knowledge and known facts at that point of time. It provides a guideline to keep team members on track. Team members might learn higher quality knowledge and identify invalid facts along the way. The team would make use of the new knowledge and more accurate facts to deal with the situation more efficiently.

Responding to change over following a plan.joke

Managers should be the leaders to head the change response but ironically it is also the managers who are often very reluctant to change and claim “We have a plan, we need to follow our plan”. This could happen for various reasons and the justification could be valid or otherwise. Not all changes happen for a better outcome however if the team could identify a change is happening (or has happened) and an appropriate response is required, the team should respond by introducing a new plan. There is little value in protecting the ego and refusing to change. Responding to change might be painful in the beginning, but it always yield better return for the project on the long term.

This is a quick introduction on the Agile. On Part 2, we will discuss more on the prescriptive activities in Scrum.

Dependency Injection for Web Service


Dependency Injection allows developers to cleanly inject a portion of concrete code implementation into the bigger scope of the system base on certain logical condition. Dependency Injection has been a preferred way of implementing code. In fact, ASP.NET 5 (Visual Studio 2015) supports Dependency Injection as 1st class citizen. Dependency Injection removes the need of having tons of if-else statement in code implementation and keep the codes clean. Some developers implement Dependency Injection even there isn’t such need at the moment as a standard practice because they might be needed in the future. Personally I’d prefer not to implement Dependency Injection unless there is a “good reason” for implementing it.

One of the “good reasons” is while designing a web service. Web service often serve multiple clients having various needs base on certain logical condition. It is web service responsibility to handle the various logic implementation. For example a web service is serving clients from various countries on the same endpoint might need to execute different codes to produce localized result depending on who is triggering the end point.

Consider this simple scenario:

We need to design a WCF web service that serve multiple countries on an eCommerce platform. There is an endpoint to accept a Product Id and the endpoint will return the formatted local price. The local price calculation depend on various factors such as tax, shipping, marketing promotion, and other business consideration to lower or higher the price for each country through custom discount mechanism.


Here is a good scenario to create a WCF web service that implements Dependency Injection to separate different calculation formula for respective country.

In the sample code, the Dependency Injection library that we are using is WCF (C#.NET) with Autofac.

Create a BaseService class. In BaseService class, we define a static container to register and store a list of logic. In this example, IProduct is an interface that get registered with different logic classes (UsProduct, UkProduct, MyProduct) depending on the logical condition.

We create a BaseService class for this purpose so that all the services in WCF could inherit BaseService class and access to BuildContainer method, which will be common among all services. In the following example, all public API method would be required to build the container to initialize the logic classes.

Base service class

Catalog service class

  1. Create a new Catalog service in WCF.
  2. Add a method GetProductPrice. It has UriTemplate as following which means Country and ProductId are parameters to be constructed in the Url endpoint.
  3. Inherits BaseService. Implements IProduct.
  4. BuildContainer is a method defined in parent class.

Define the respective logic class for each country…

United State product logic class

UsProduct class is also the parent class for rest of the country class. For example, the method GetProductBasePriceById is applicable for all countries implementation, hence this method stays at the parent class (UsProduct) so that it could be accessed by the child classes (UkProduct & MyProduct).

United Kingdom product logic class

UkProduct class inherits UsProduct and implements IProduct. GetProductBasePriceById could be accessed by UkProduct as UsProduct is the parent class.

Malaysia product logic class

MyProduct class inherits UsProduct and implements IProduct. GetProductBasePriceById could be accessed by UkProduct as UsProduct is the parent class.

All the product logic classes implement IProduct interface so that they all could be registered into Autofac container builder. (The following code is part of BaseService class shown earlier)

By registering the interface with the appropriate concrete logic class (base on the logical condition of country), Autofac would build a static container that allows the whole application know which concrete logic implementation to call.

If we put this into test using Postman, we would get the following result

Product price for United State

Country is specified by replacing {Country} parameter to “Us”. Result is returned base on UsProduct logic class implementation.


Product price for United Kingdom

Country is specified by replacing {Country} parameter to “Uk”. Result is returned base on UkProduct logic class implementation.


Product price for Malaysia

Country is specified by replacing {Country} parameter to “My”. Result is returned base on MyProduct logic class implementation.


Some consideration…

  1. Performance for building a container and inject dependency on runtime instead of direct initialization of concrete class. From the above 3 examples, we could see each request completed within 15-16ms. I ran a few more test switching between countries, most of the request completed in less than 20ms, which shows there isn’t any major overhead for builder the container.
  2. What are some other reasons to implement Dependency Injection? Answer is unit test. With Dependency Injection in place, the codes would be much testable. (If you currently have codes that is not testable, consider using Shim under Microsoft.Fakes. However this reason is arguable as we do not necessary need Dependency Injection to test our code. All we need is appropriate interfaces in the codes.

One last thought…

Dependency Injection is a clean way of separating codes implementation which conforms to a standard set of Interface. With Dependency Injection developer no longer need to separate which line to execute using complex if-else statement. It makes the code base much cleaner and easier to implement different codes base on logical condition. The drawback is Dependency Injection makes debugging more complicated as the developer need to first figure out which class has been injected during runtime. It is a recommended approach if you have a standard set of interface but requires different codes to be executed base on logical condition.

Multi-Country Address Database Design


Database design is one of the most critical elements in software development. Realizing that

  1. New programming design patterns and frameworks are introduced from time to time, codes get refactored and system get revamped;
  2. New UI frameworks are introduced from time to time, presentation layer get thrown away and redesigned;
  3. However database design stays the same for as long as possible because business simply do not want to risk valuable production data and development team do not want to deal with legacy codes to accomodate the new design.

A good database design can stand against the agility of business needs. The development team is not expected to frequenty adding or modifying database (for example adding new tables and altering relationship between tables) unless it is “totally necessary”.

While “totally necessary” could be subjective depending on how well a database is designed to handle the changes, a good database design is expected to handle various changes in business requirements without major redesign of the existing database.

Case Study: Handling addresses in multi-country database

I was working for a car classified MNC that have businesses in 3 countries. We were rolling out CRM for Malaysia, then Indonesia and finally Thailand. Each country has a different address format.







Following Agile principle of Just Enough and Just In Time (and a typical product manager would choose to deal with the other countries in a later time rather than taking the overhead now), development team would design a database schema to cater only for Malaysia user address format.

The following database schema design is efficient to get the job done. Address is the child of Customer. Section, District, Postcode, Locality, Province, and Country are the referencing tables for Address table. Among different location levels, the parent-child relationships have been defined to indicate the hierachy of the location levels. The database is fully normalized and has strong data integrity by defining appropriate foreign keys:


The development team proceed with loading data to the respective reference tables (Section, District, Locality, Postcode, Province, Country) and launched the CRM for Malaysia users. Soon production data start flooding into Customer table and Address table. Everyone is happy with the system.

2 months later business informed the development team, “Guys, it’s time to let our Indonesia users enjoy our amazing system!”, “Sure!” comes the reply.

When development team looked at Indonesia address format, it is quiet different from Malaysia address format. Leaving AddressLine1, AddressLine2 and CountryId field aside, the remaining fields are different.


The existing database schema cannot fit Indonesia data in any elegant manner due to the parent-child relationship between location level and obviously it is not a good idea to have a Postcode table filled with Malaysia Postcode data, Indonesia Locality data and Thailand Province data (refers to row #3).

Not only Indonesia, even Thailand has a different format compare to Malaysia and Indonesia. The mapping will only get more complicated when more countries join the party.

In order to support Indonesia, the existing database schema need to go through moderate design change to cater for Indonesia address format. Later when system need to support Thailand address, another database schema change would take place.

Instead of continue maintaning a structured table design to map one field in a table to one business entity property, while making changes to support Indonesia (or even better when designing database schema in the first place), a hierarchical relationship could be defined for maintaining the dynamic relationship of address location levels.

Simply put, a hierarchical relationship is self referencing mechanism to dynamically define n-level of parent-child relationship.

The most common usage for hierarchical relationship is defining Employee and Manager relationship. A database could have an Employee table. Within the Employee table there is a ManagerId field and the ManagerId field has a foreign key to the same Employee table Id field. The record could keep referencing within the table itself until hitting a record where his ManagerId is null, which indicate that record is the highest level in the hierarchy. In this case, most likely the record is the CEO record.

To put it into context of Address, take a look at the following database schema design:


In this design:

  1. Tables are much lesser and relationship is much cleaner
  2. Development team can be assured that this design could handle any address structure in any other countries.

The above database design schema shows:

  1. There is a Customer table.
  2. There is an Address table that could have multiple records for a particular Customer (one to many relationship). The Address table has a foreign key to Location table to tell which is the smallest level of location the Customer belong to.
  3. There is a Location table that has a hierachical relationship with itself. Base on the ParentId field, you can “figure out” the full set of address.

So how do we “figure out” the hierachy?

Let’s take a look at a sample set of data in Location table:


By using the following SQL query with Id = 27:

WITH LocationTree
AS ( SELECT Name, ParentId
FROM Location
WHERE Id = 27
SELECT Location.Name, Location.ParentId
FROM Location
INNER JOIN LocationTree ON Location.Id = LocationTree.ParentId)
FROM LocationTree
You would get result as following:
This result shows the child parent relationship between the location. Cheras is the parent of 56100 (postcode), Selangor is the parent of Cheras and Malaysia is the parent of Selangor. In address context, Postcode 56100 is inside Cheras, Cheras is inside Selangor and Selangor is inside Malaysia.
The Id = 27 is set in Address table LocationId field to represent the lowest level of location the application could identify for the particular user.
Now if we modify the query to Id = 28, you would get a different set of location hierachy:
Not only the hierachical relationship would work for single country, but also other countries with lesser location level. Assuming that we have a customer from Indonesia and the smallest location level we could identify is City, say Id = 22, where the Id = 22 is set in the Address record belongs the Indonesia user. By running the same query, we would get the following:
The mechanism for hierachical relationship is simple in the address context. Howeer at the application level, the code need to set the smallest location level into LocationId in Address table. At the application level the input would need to be in the form of selection rather than free text input (else it would defeat the normalized database implementation). For example:
Regardless of how many location level we have, or what is the sequence of the location level, a hierachical relationship will allow us to have a normalized yet flexible database schema that is resilient against business changes like supporting more countries.

An application developer that handles the programming codes for multiple countries might complain he could not map the list of address result hierachy into his object property. Imagine he has 4 levels for Malaysia, 3 levels for Indonesia and unknown level for Thailand. How would he design his codes to map what does each level means or represent for each country without having 300 lines of if-else statement? A trick for handling such complexity is through dependancy injection per country. After all, it is the application code job to give context to the data while the database job is to provide storage for the data.

One last thought…

Having a hierachical relationship would increase the flexibility of database schema, but it increases complexity in application codes and would not provide a clear one to one mapping on table field to object property. Such implementation is fundamentally different from how a typical development team would design the Address table. Such complexity is not required if a development team is designing a system to support only 1 country address format. However hierachical relationship should be given consideration if the database needs to handle multiple-country address with various combination of location level relationship.