Home > Articles > Data > SQL Server

SQL Server Reference Guide

Hosted by

Toggle Open Guide Table of ContentsGuide Contents

Close Table of ContentsGuide Contents

Close Table of Contents

Migrating Departmental Data Stores to SQL Server: Model the System, Continued

Last updated Mar 28, 2003.

This is the third article in a series of a formal process you can follow to migrate data stored in “departmental data stores” (such as Excel, text files, XML documents and so on) into a Relational Database Management System (RDBMS) like SQL Server. The first article in this series is here, and the second is here.

In the first article, I explained what these data stores are, what they mean to your organization, and when they should be considered for migration. Some data doesn’t need to be stored in an RDBMS, and other data does. I also explained a few methods you can use to locate that data. That’s the first step.

In the second installment I explained how to take that data and model it so that you can tease out the requirements from the discovery you’ve done and how to model that data so that everyone agrees on its final format. I wasn’t able to complete the entire part of that process there, so I’ll finish that step in this article.

In the last tutorial, I explained how I use a combination of meetings and tools to create a formal Business Requirements document. I only alluded to that kind of design document, but it’s an integral part of what you’re doing. In fact, it’s probably the most important part — and I don’t say that lightly. So in this tutorial, I’d like to take a moment and cover a Business Requirements document in a little more depth before I move on to the databases, tables, fields and other database objects that I’ll create.

I won’t detail out the entire Business Requirements document; I’ll just explain what goes into it. Detailing even this simple example would be longer than I need to show you the overall process.

Re-visiting the Business Requirements

It’s important for the data professional not to design the data around the database system he or she uses. You should focus instead on the business processes, and take extra time on the very first question in my last installment: “What are you trying to do?” That question alone can end up with pages and pages of documentation.

Many times it’s helpful to use a modeling notation to ensure everyone is saying the same thing. For Business Analysts or formally trained business folks, that’s often a graphical system called Business Process Model Notation, or BPMN. I advise that you learn this simple system, since it will help you stay away from using the “FURPS” model we software types normally gravitate towards.

In any case, keep the complexity as far down as you can for the business folks. It’s OK if the IT department breaks all of this out into more complicated models, uses Unified Markup Language (UML) and lots of other documentation, but the end result should be a very clear, complete document of what the business is trying to do.

I’ll put some references at the end of this article that will help you develop a formal business requirements document.

It’s a good idea to take a look at a well-formed Business Requirements document. Here is one that you can review, and even modify for your own use.

With all of this created, I normally create a place to start keeping everything under source-code control. Since I work in SQL Server Management Studio, I’ll create a Solution and a Project in SQL Server Management Studio to have a simple directory structure for everything, then I’ll place that directory under source code control.

Normalize the Model

I’ll assume that you have a good business requirements document defined from the meetings you’ve held with the various teams and the source documents. Now it’s time for some standard database design work.

There are a lot of processes you can follow for this exercise, but I’ll stick with the one I described in my article on database design. Recall that I explained that there are a few simple steps to creating a design, and it’s simple enough now that you have the source documents and the questions answered from the business questions and subsequently the Business Requirements document.

I’ll use a modified version of my process I described in that other tutorial, which creates the following Entity Relationship Diagram (ERD) — in a moment I’ll break each of these down so that you can see where I’m at so far:

Step One: Pulling out the Nouns

Everything in the answers to my questions to the business, the source documentation and everything else revolves around Vendors. In fact, that was the entire reason given for the system. So obviously, the first noun I focus on is the Vendor. Secondly, I notice that they were using various contacts at that vendor, so Contact became the next noun I recorded. I continued this process with the rest of the nouns, such as “address,” “phone” and so on.

Step Two: Break down the Nouns into Groups

As I continued the design (and don’t worry, it will change a couple of times from that diagram), I noticed that some nouns “belong” to others. For instance, a Vendor has an Address Line 1, and Address Line 2 and so on. A Contact has a Full Name and other details. I placed these (duplicating the address, phone and so on at this stage) in my design documentation.

Step Three: Determining the Relationships (Pulling out the Verbs)

This is where things get interesting. From the requirements, I noticed that a Contact will always be part of a vendor — I verified this with the teams. That means I’ll need to add a column (or attribute) in the Contact noun (or entity) that “points to” a vendor. I verified that a Contact would only work at a single Vendor at a time, but many contacts might work at a single vendor. That determines the cardinality of the relationship between Contacts and Vendors. One Vendor; many people that work at that Vendor that are my contacts.

Step Four: Eliminate Repeating Values

There is, as you can see in the ERD graphic, more than just one relationship. From the requirements the team put together, we found that vendors can be of multiple types – we might, for instance, buy both software and office supplies from the same vendor. So whenever that happens, that information gets its own table and is related back to the “parent” table. Now the company can sort, arrange, display or edit a vendor based on the type of things we get from that vendor.

The contact’s information was an interesting situation. From our investigation, the contact was never contacted through their own address — just through the company’s. However, they might have a special box number or mail stop just for them. Also, the contact has other information (web sites and so on) that the users want to capture. So the answer was to create another table called “Reference,” and then tie that back to the contacts. That way a “ReferenceType” might contain the value “web site,” “other phone,” or whatever. The “ReferenceDesription” is a large field (or attribute) that can carry almost any kind of information, from a web site name to a phone number.

And why stop there? If a contact might need that information, perhaps a vendor might need the same thing. So I added a second optional field that could tie back to the Vendor table as well.

Step Five: Setting Data Types and other Constraints

Along the way, I examine the data type of each column. If the data that is stored is always in date or time, I use datetime. If it should be a number, I determine the lowest possible value (even the future) that would go there and use that type. This keeps the data consistent, speeds data entry and prevents data entry mistakes to some degree.

I also ensure that a value is required in the proper places using “Not Null” constraints. That means that I won’t allow a contact to exist in the database that doesn’t have a vendor that they work for, and so on. It’s these little steps that ensure that this database doesn’t run into the same issues present in the spreadsheet or departmental data store.

In the next articles, I’ll flesh out this design a little further and explain a few possible targets for the data.

InformIT Articles and Sample Chapters

My article series on database design in the SQL Server Reference Guide starts here with Database Design: Requirements, Entities, and Attributes.

Books and eBooks

Database Design for Mere Mortals®: A Hands-On Guide to Relational Database Design, 2nd Edition is a great book on database design.

Online Resources

You can learn more about Business Process Model Notation here.