SQL Server Reference Guide

In this section, I'll introduce the process of database design. I'll follow this with a series of articles that take a deeper—dive into the things I introduce, but this will give you a good place to start — and a simple outline that you can use to design any database you need.

Before I get started, I'm assuming that you are already familiar with the database objects in SQL Server. If you aren't sure that you understand tables, views and other database objects, you can review them in a series of articles I have starting here: http://www.informit.com/guides/content.aspx?g=sqlserver&seqNum=49. 

Here is a simple outline you can use to design a database:

1. Create the business requirements
2. Simplify into sentences, identify nouns
3. Group nouns, apply data types
4. Define relationships, apply constraints

This outline will serve you well in designing databases both large and small. It's deceptively simple, however, as each of these steps can be quite involved. In this series on database design I'll expand on each of these sections. There are various tools I'll show you in this series that you can use to make this process easy to understand and to collaborate with others. This article serves as an overview of the entire process — I'll show you how to use those tools in the articles that follow.

Create the Business Requirements

The first place to start in a design effort is with the business requirements. Simply stated, the business requirements tell you the goals of the program. Those goals translate out to the way the code will work, and that drives out what data is persisted in a data store. Not all data is persisted — some of it might be a calculation, an object built from data and so on. For instance, if a business requirement states that you must display a person's age, then you would probably only store the date of birth, and then calculate the age from that programmatically. There's no reason to store that particular datum for each day, month year of age when you can calculate it from a single value.

Creating requirements is not the most glamorous of jobs. I've seen lots of development team skimp in this area, and it really shows in the end. When a data professional doesn't think the design through properly, you can end up with an "organic design," meaning that each part of the design is added on as the program develops. Those databases are painful to live with when they grow larger and as time goes on. In some cases you have to start over completely.

When you create business requirements, you're defining the data your system will store, and how it relates to your business. As you move through the steps I'm about to show you, keep those two things in mind.

To start, you should determine who your customers are. Even if you're not making a commercial product to sell, you're providing the database to someone, and those people should be treated like valued customers. Carefully search out this group of people and let them help you describe what they want done. These are the folks that will create the requirements. Sometimes they write them down, other times the development team does, but ultimately they are the source of the information.

Understand that the users of the product will have a different style of describing the process than you will. Often they'll just grab the paperwork or other output from the current system and say, "It does this." While the current system may capture a large part of what is needed, it may not capture all of the requirements. Often an older system forced the users to accommodate the system, creating strange requirements.

It's not very often, but sometimes a grocery checkout clerk makes a mistake. When that happens, the clerk pushes a button that turns on the light to summon a manager. The manager walks over, turns a key, and signs a little piece of paper, and the clerk gives me the refund. All this happens without a word. As I watched this one time, I asked the clerk why a manager was needed for the process, since they didn't seem to check anything important. The clerk just shrugged and said "That's just the process we have to follow. The computer handles all the refunds anyway." At some point in time this process probably made sense, but it sure doesn't seem to now.

So don't do that. Your database should store what's needed – nothing more, nothing less. To do that, you need to perform several interviews – group and private. Talk to both the users and producers of the data. Make sure that the people you interview are experts in the process, and include a couple that aren't. In the interviews, you might do the writing or have the users fill out a form, but in either case you need to end up with one—concept sentences that define the process as completely as possible.

Don't stop there. Gather as much information as you can about the process. Ask about documentation, observe the plant floor operations and so forth. Look for ways that the process really works. Getting it right won't be easy and it won't be painless, but it will be worth it. I've covered a series of questions you can use to work with the end customer to get the answers you need. You can find that here: http://www.informit.com/guides/content.aspx?g=sqlserver&seqNum=373.

In the tutorials that follow, I'll create a database that supports a program that tracks the hours and billings for a consulting company. I've chosen this type of example because it's fairly common to track billable hours. Other than inventory control, it's probably among the most common requirement types you'll run into. This system can handle everything from consultants to authors to contractors — just about anyone who bills for time.

So now I'll take this fictional consulting business and write out a simple example of the basic processes they use, gathered from various sources. Keep in mind that I'll make this extremely simple — far more simple than you'll face in your work environment. But this description will be enough to get a few concepts across:

“Here at BuckSoft Consulting, consultants work on projects for clients. Consultants have individual expertise and work in teams for each project, for a set period of time, and are billed out by the hour on a predetermined rate.”

For now, this will serve as my complete business requirements document — obviously in any kind of production database, even a simply one, the requirements document will span multiple pages of information. But I'll keep it to one paragraph to demonstrate the process.

Simplify Sentences, Identify Nouns

The next step is to take the sentences in the business requirements and convert the nouns and verbs to your database design. To begin, look for the nouns in the description. As you know, a noun is a person, place or thing that performs action. The nouns in a description will be mapped to entities, which will form tables later on.

Remember that you're looking for a single concept for each entity, so if you see a complex noun (such as projects), examine it to see if you need to break it into more parts. That goes back to those business questions I pointed to earlier.

Here's that same paragraph again with a few nouns highlighted (although not all):

Here at BuckSoft Consulting, consultants work on projects for clients. Consultants have individual expertise and work in teams for each project, for a set period of time, and are billed out by the hour on a predetermined rate.

From the above sentence, you can see a few of the nouns I've identified:

• Consultant
• Client
• Project
• Team
• Rate

...and so on. In a production environment, you'll identify which of these nouns are persisted into the database, and which can be calculated by a program. For now, just list them out as I have here. That's the first step.

Group Nouns, Apply Data Types

Next, I'll create attributes which will become columns or fields in the database. To do that I take the list of nouns that I've come up with and begin to describe them so that I can figure out which of these nouns belong with each other, which ones are repetitive, and which ones might actually break out into more than one noun.

Let's take Project as an example. That's a fairly complicated topic, so I needed more information on it. From an interview I had with the client, here's what I wrote in my notes:

“Projects are created for a client. Projects have a name. A project has phases, which are bounded units of work. Projects have a lifecycle, consisting of the request and initiation, planning, execution, control, and close. Projects have a budget. Projects are measured by the man—hour per work. Projects have a success or failure state. Projects have several stakeholders, such as various members of the client's staff and various members of BuckSoft consulting.”

What I'm looking for this time are the attributes that a project contains. Some items described in this list don't actually belong to a project; they are just part of the project. For instance, a consultant works on more than one project, so he or she doesn't really "belong" to a single project. This also holds true for clients, so those nouns get their own entity, or table. But I will relate them back to a project shortly.

Also, initiation, planning, execution, control and close aren't really attributes; they are possible values for the phase attribute. In a moment I'll show you how to handle all this. For now, I'll just sketch out a simple Entity (a table) with some possible attributes (columns):

Project:

• Name
• Phase
• Budget
• State

Notice that right now this is a pretty simple entity, and that's true because I asked questions about what really goes into a project. I'll have to add more as I go, but for now this works.

You might be wondering what happens with all the other parts of the description. I'll get to that, but first I need to complete the exercise of creating the rest of the entities as I did with the Project entity. It's important to be as diligent as possible, adding everything that fully describes each entity, and then removing what doesn't.

Define Relationships, Apply Constraints

In the next few tutorials I'll solve the dilemma of things that seem to be part of an entity, but really aren't. This involves defining relationships between entities. You can most often find them by looking for the verbs in the description. Let's go back to the Project entity:

Projects are created for a client. Projects have a name. A project has phases, which are bounded units of work. Projects have a lifecycle, consisting of the request and initiation, planning, execution, control, and close. Projects have a budget. Projects are measured by the man—hour per work. Projects have a success or failure state. Projects have several stakeholders, such as various members of the client's staff and various members of BuckSoft consulting.

Notice the possible relationships in bold. These relationships can be defined in a database by adding Primary Keys and Foreign Keys, and then putting the discrete tables back together with a JOIN operation in Transact—SQL. I'll cover that in greater detail in articles that follow.

To flex your mental muscle until we meet again, try this exercise: Create the Client entity from what I've shown you here. Think carefully about what really belongs to a client, and we'll see if your idea matches mine.