SQL Server Reference Guide

This is the second article in a series on database design questions that you should be asking the business (or organization) before you start a database design. In the first article I explained the process of getting the organization on-board with helping you create the design. These “soft skills” are essential, because without them you’re left to designing the database without all of the information you need, and that means the design won’t be complete. At that point, you’re doomed to make mistakes, and like it or not, you’ll have to change the database after it’s in production — always a painful proposition.

I explained some techniques in the last article on explaining to the business why it is important to have their continued participation in the design process — it’s not just a “here’s what we need...goodbye” kind of thing. The success of the project is completely related to how engaged they are throughout the design.

At this point, you have a request from the business for a program design. Remember, they won’t put their request in terms of a database per-se, since non-technical audiences don’t know (and aren’t expected to know) the components of a computerized system, so they may not be aware of the separation of the database from the program.

The organization or business will start with what they want the program to do. But this is a bad place to start, since they are usually trying to fold what they know about the process into what they think the program can do. It’s better to have them state what they do, not what they want the program to do.

In the end, you should get a narrative or series of steps that explain what they do, and then you can translate that into what the program should do. From there, you can tease out what data the database will store — something I’ll show you how to do in the next few articles. For now, it’s important to get these “stories” from the business and then to refine them to make sure you’re getting everything you need for the program to be successful.

The narrative you get should be pretty long, but to illustrate the concepts in this article I’ll use the example from a previous discussion. Here’s a single paragraph from the fictional client that explains what they do at a high-level:

Again, this is actually far too broad to really work with, but I’ll use it as a start. In fact, many times the business person you’re dealing with will think and speak in either these high-level terms, or the opposite — the description of only one detail in this scenario. It’s actually OK — just start wherever they do, and using the techniques below, you’ll work up, down or across to fully define the process.

With that broad definition in place, let’s explore some of the options you have for working towards pulling out the definitions you need for the database.

Clarifying Questions

The first place to start is to ask questions based on what you’re told. And the best place to start is with some “clarifying” questions. These questions use the information you’re given to find out more areas that the business person might not remember are involved in the process. You simply take each sentence, and break out the parts of the sentence, asking questions about each part.

It’s important to ask questions even about things you think you understand — in fact, it’s essential that you do. What you assume you know what a particular verb or noun in the sentence might not be the same thing the business thinks it is.

The kind of questions you’ll ask should be driven by the database components you know you will need, for example:

• Optionality: How many of this noun can/must be stored by the database?
• Cardinality: How is this noun related to other nouns?
• Business Rules: What must happen for this action or verb to be valid?

In the example I’m using, here are some questions that help clarify what the user needs. There are several I could ask even in this simple example, but I’ll just ask three here to give you the idea. I’ll follow up with why I ask these questions:

1. When you say “Consultant”, is that a specific role, or is that a role someone “puts on” when needed?
2. Can you explain a little more about the project itself? Specifically, can a consultant work on more than one project at a time, or are they assigned to just one project for its duration?
3. Are the consultants interchangeable for a project, sort of a “role-based” approach, or is the person important to be on a particular task or project?

Take a look at question number one. This question is on the company’s side of the equation. BuckSoft might have multiple people with overlapping skills. Perhaps John is both a Certified Public Accountant and is also able to model business systems. If the answer is “role-oriented”, then one of the primary entities in the database will record roles as resources, with a “weight” for each one. That might mean a design like this:

Resources
	ResourceID
	EmployeeName
	...
Skills
	SkillID
	SkillName
	SkillDescription
...
ResourceToSkills
	SkillID
	ResourceID
	Level

That allows each person to have many skills, and a level of the skill such as “High” or “Nominal”, and allows skills to have multiple people associated with them. This, of course, is only one possible choice; there are many others. the point is that the answer relates directly to a design.

Now on to question number two. This question is actually related to the first one — it revolves around the project from the business’ standpoint. If the consultant is role-based, then the company can move them in and out of a project at will. That means the relationships between the project and the resources on it might have a many-to-many join on the resource type and level, and will need a date and time range.

The third question is yet another vector on the consultant question. In some cases, a consulting company makes its money from the “big names” that it has employed. Because of that, they will start the project with these industry-recognized experts, and then switch to a “B-Team” after the project starts. That way they can leverage the expensive resources multiple times in a given period. Their customers, however, may want to retain the bigger names on the project to ensure a more professional job. So question three involves whether the organization allows their customers to request (and pay for) a single resource throughout the project. Once again, this has design implementation considerations — most notably the ability to tie-up the resource based on the projects they are assigned to.

You can see that I’ve asked basically the same question three times, from three different angles. The first is from the perspective of the consultant, the second from the perspective of the project manager, and the third from the perspective of the client or customer of the company you’re designing for. The reason I recommend this approach is that while the company might be thinking about one of those vectors, they might not consider all of them. My three questions drive a single design — any “yes” answer changes the consultant-to-project-to skill relationship.

What-If Questions

Once you’ve asked your clarifying questions, you need to begin to ask questions based on the situation. These questions usually drive multiple decisions, from the layout of the tables and their relationships to the business rules enforced in constraints and stored procedures.

In the example above, assume that the company states that on a single project, a resource “by name” is assigned. This means that John is assigned to the Bank project, and stays with it until the end. Before you lock that down into a design, however, ask situational questions like the following:

• What if John is unavailable for a period of time — such as a sickness?
• What if John is permanently unavailable, such as leaving the company?
• What if the project is cancelled? How is John returned as a resource to the company?

The answers to these questions are something that the business person usually keeps in their head — they intuitively know what to do in each situation. But if you don’t plan for them in your database design, the users will have to “work around” the system, causing issues later. Ask as many of these as you can think of, and have the business people come up with them as well. After a while they’ll catch on to the process and help you define the questions and answers.

Design Review and Explanation

The examples above are just that. My actual question and answer sessions make up the bulk of the design process. Since they take so long and require so much participation from the business, you’ll need those “soft skills” I mentioned in the last article, especially the part about making sure they understand how important the design process is.

When you’ve completed those questions, write up your design review and pass it around to everyone. Make sure you all agree on what the business questions and answers are, and how the processes work.

This is probably the most important tool you have for your design. In some cases, this is persisted as a “Design Specification”, but even if you don’t make it that formal, write them down and communicate them often.

Tools

To get to the questions and create the documentation, you have multiple tools available. I’ve waited to discuss them until now because of one very important consideration: a tool should help you with the process, not become the process. A simple piece of paper and a pencil or a whiteboard and a marker are sufficient to create a great design. The technology you use for the design process should enable the process and never become the reason you do something in a less-than-optimal way.

That being said, there are tools and processes that you can use to more fully define the system requirements. Here are some of the popular ones.

Narratives

The first tool isn’t a ‘tool” per-se, as there’s no software to install. It’s all about telling user stories and narratives. Of course, you can use software record them, but you can also just write them down.

There’s a complete process for learning to tell these stories, and I’ll direct you to a series of articles here on InformIT where you can learn more.

Unified Markup Language (UML)

The first tool that many software developers use to communicate between the business and the technical teams is the Unified Markup Language, or UML. UML is a series of graphical elements that you can use to explain a complicated process. In fact, a UML diagram, or model, isn’t a single object. There are over seven different kinds of UML diagrams, but the one the business will most likely understand is the Actor/Object or “Use-Case” diagram.

Figure 1

Although UML is a good choice, not all business people will be familiar with it. It does have the advantage of the developers knowing how to use it — but it may not be the best choice for the business team. If you do decide to use it for that, check out this reference to help. Microsoft also has a resource page if you’re using Visual Studio to create your program.

Object-Role Modeling

A hybrid of UML and a database Entity Relationship Diagram (ERD) is the Object-Role Modeling (ORM) paradigm. This is a fairly easy-to-understand diagraming system that explains what each person or object does to another person or object in the system. While it captures the relationship side of the process, it’s less descriptive on the elements needed in the database, in my experience.

Figure 2

You can learn more about ORM here.

Business Process Model Notation (BPMN)

The tool I’ve used most often in finding out the elements and relationships needed for an organization’s process is Business Process Model Notation (BPMN). It’s a very simple set of graphics that can be arranged vertically or horizontally to show the start and stop parts of a process, and any hand-offs to other processes.

Figure 3

You can arrange the processes into “swim lanes” that are simply boxes showing the boundaries of a process. Using simple icons, the business communicates the processes to you, the technical team. It has the advantage of wide use by businesses, and a very simple lexicon of graphics. You can read more here from the people who created the process.

Using any or all of these questions, methods and tools, don’t forget the point of the exercise: to discover as much as you can about the needs of the business for data storage. In the articles that follow I’ll show you how to take this process further and create your data elements.