SQL Server Reference Guide

In the database design process, the first thing you should do is completely understand the requirements created by the business or organization. Of course, that presumes that the business or organization has taken the time to actually create those requirements, but in any case you need to be able to completely understand what is required from the system to adequately design it.

Once you have that information, I’ve shown you how to create an Entity Relationship Diagram (ERD) in order to show the database design back to the organization, the developers, and the other data professionals. As I’ve mentioned, a graphical representation like an ERD is very useful at "dis-ambiguation" — in making things clear and easy to understand. If you design your database incorrectly, everything from data integrity all the way to performance can suffer, so ensuring that everyone agrees that the design captures the elements needed is essential. Also, creating a graphical representation of the database is also useful to check the work for errors — other data professionals and others involved can review the document and make any suggestions or corrections before the design is persisted into a physical database.

Normally the business requirements are delivered to the development and data teams in text. This is completely acceptable, and I’ve explained previously how to break down a narrative into nouns and verbs, and how to create entities and attributes (tables and columns) and the verbs into relationships. But that might not capture everything.

Understand that the businesspeople on the project don’t think in terms of tables and columns and relationships — they think of objects (like a purchase order), people, and activities. And it’s not just the businesspeople that think this way — remember that the data professional is only one part of a development team, and we are normally far outnumbered by the developers on a project. While some developers are quite familiar with a Relational Database Management System (RDBMS) design, others think more in terms of classes, methods and properties. So the business teams have one thought process, the developers another, and the data professionals yet another. So it’s important for the data professional to understand what the other teams use to describe their processes, so that you can communicate effectively with them.

In this article I’ll focus on what the business professionals use to describe their processes, using a graphical tool called Business Process Model Notation, or BPMN. I’ve already covered the tools the data professionals use, and in another tutorial I’ll explain the basics of the tools developers use. In all cases, I’ll map these back to the primary factors a data professional should be concerned with.

By no means are any of these articles complete and fully detailed. In the case of the ERD diagram, there are very few graphic elements that you can combine quickly to communicate complex information. In the base of BPMN, and the developer diagrams that follow, there are far more symbols, and combining them can make for a very complicated diagram. I’ll focus in these other diagrams on making sure that I use simple examples to introduce you to the concepts, but if you are asked to work with these diagrams there is far more to learn.

In this tutorial, I’ll give you a few tips for how to look at the diagram, then I’ll explain a few of the major elements, and then I’ll give you some references to find more information, and a handy PDF of a poster of all of these elements.

BPMN Explained With a Simple Diagram

So what is BPMN all about? It’s a matter of showing the actions of a business process against the objects within the process — and here’s the key part for the businessperson — the interactions each object, process or person has with another. It’s kind of an "impact diagram."

The direction you read a BPMN diagram is interesting because unlike an ERD or the tools used by a developer, the process might start right to left, left to right, or top to bottom — or bottom to top. It might consist of several "branching" symbols, which change the flow. Arrows dictate which direction one object, action or person interacts with the next. You can actually start reading the diagram anywhere, and just back up in the reverse direction of an arrow until you find the "head" or starting object, action or person.

When you see a BPMN diagram, you’ll notice symbols and text. The text is a refinement of the graphical element.

You’ll also notice that some BPMN diagrams, especially simpler ones, just have the symbols and arrows, while others group many of these into boxes. I’ll explain these groupings in more detail in a moment, but essentially they bundle together a system of tasks and flows, allowing them to interact with another. In that manner, you can "read" the larger box as something like "The purchase order process" and ignore (if you wish) the detailed steps within that box, focusing only on items you care about.

Those are the basics of reading a BPMN diagram. Here is a complete, although very simple, BPMN diagram of the same project I started with in the earlier database design tutorial:

You can probably tell from this simple example what is going on. I’ll add to this in a moment so that you can see how to create your own, but for now, I’ve diagramed one part of what BuckSoft consulting does. The firm provides consulting services, but of course that means there are several things that happen before a team of consultants goes out to work on a customer’s site.

First, notice that this is only one grouping — the Consulting Engagement Evaluation. That means that a sales or pre-engineering team goes out to talk with clients about retaining the services of the consulting firm. From there, the team schedules a meeting between the client and a consultant, both of whom will help decide whether there is a fit for the services. If not, the consultant returns and another process begins, a series of steps not shown in this diagram begins to find out why the deal was lost, and when that is complete, the engagement is cancelled.

If the client does select BuckSoft, this team’s work is done. They hand off the project to the implementation team, not shown on this diagram. 

A Few BPMN Elements Described

From that simple example, you can begin to examine the parts of the BPMN document. I’ll describe them in a little more detail here, but again, you’ll want to check out the references at the end of this article for more.

Collaboration and Events

A "master" BPMN diagram usually contains only broad tasks and flow. While that’s interesting to the general designer, it’s not normally something the data professional is interested in. We’re looking for anything that needs to be recorded as a data element, so you will want to drill in to the greatest level of detail that you can.

In a complex process, the entire diagram normally starts with a Pool (think Swimming Pool here).

This groups a series of activities. Again, the items in them might have any order, but I’ve typically seen a left-to-right arrangement here in the U.S.

When there are multiple actions within a process (which is normal), smaller boxes are added inside the Pool called Lanes (like lanes in a swimming pool).

This arrangement (as I’ll show you in a moment) allows for processes to send results to each other, and receive information or objects back.

Next comes a rounded box, a Task:

This is exactly what I showed in the very first graphic, and the task can contain the details of an activity. Some diagrams omit these, but on the "rolled up" versions this is a common view. It’s a great way to see what the business does at a high level, and then each of these Tasks, Lanes and Pools can be examined in further detail based on the need of the person reading the diagram.

Events

Events are the details of a process. There are really only three types of these:

Start:

These are the things you do to start the process. There should be at least one of these in a process, although you can "stack" them above each other to indicate that multiple things happen at the same time, say, when an e-mail from a customer arrives.

Intermediate:

These are the events that happen between the beginning and end of a process. They have two lines in the circle, and many times have a graphic that indicate the event type. The resources at the end of this article will show you what these are.

End:

End events look similar to start events, except the line weight is much heavier. There are successful ends and unsuccessful ends and so on. The end task (like all of the other tasks) is dependent on the Pool, Lane or Activity drawn around it. Just because the sales team is done, that doesn’t indicate that the entire process is complete.

Graphics and text are placed in these circles to further describe the process, but they aren’t required. The text alone can indicate what the event is, although the more icons you use, the less text is needed, and you allow the folks that care about e-mail, for instance, to quickly locate what they care about. The danger is that you use so many icons no one can follow what is happening, or that you aren’t consistent. If you use a graphic for e-mail, make sure you use it all the time or you’ll miss the point.

Gateways

A gateway indicates a branch, decision or choice. Its symbol is borrowed from standard flowcharts:

A Gateway diamond may also include a graphic, again, see the references at the end for those details. If you need to stack up more than three decision points, use multiple diamonds or just multiple lines from the diamond. The diamond shape is used regardless of how many actual decision answers there are, even just two.

Data

The data shape shows that some process, person or action created a data element that must be persisted:

It’s normally in the shape of a piece of paper, but you may also see an e-mail shape or the "can" shape used by a database diagram.

This is often the graphics the data professional is looking for in a BPMN diagram — but they can be very deceiving. For one thing, the business does not often know what should be kept as a datum. They think of a Purchase Order as a document, but not necessarily a data element. And BPMN diagrams almost never take into account "Meta-Data", or the descriptors of a process such as time of day and so on.

So you might think that the BPMN diagram is not as useful for the data professional as it could be — but the most value it has in my experience is that meta-data. Each of these shapes may have something that the business actually needs to be recorded. And being able to read those symbols helps you (and the developers) tease that out.

Conversations (sometimes called Communications)

Pools, Lanes and Tasks often need to send the results from one side to another. That’s indicated with a simple line (sometimes a broken line) drawn from the event in one lane to an event in another. It might look something like this:

You may also see a "crow’s foot" notation similar to an ERD diagram. This is used to indicate multiple actions that funnel to one communication.

Where to Go to Learn More

As you can see, even with these few symbols, a BPMN diagram can be quite complex. Done properly, it really helps clear up a complicated flow, but done incorrectly, it can actually make things more confusing. If you do decide to learn to use one, there are some great references to start with,

First, there’s a poster in PDF that is very helpful. You can find it here: http://www.bpmb.de/images/BPMN2_0_Poster_EN.pdf

The group that owns and manages the standard is here: http://www.bpmn.org/

Many shops that use BPMN take it to the next step of an "Execution Language" specification called BPEL. You can learn more about that in the article Achieving Separation of Concerns Using BPEL.