Table of Contents
- Microsoft SQL Server Defined
- Microsoft SQL Server Features
- Microsoft SQL Server Administration
- Microsoft SQL Server Programming
- Performance Tuning
- Choosing the Back End
- The DBA's Toolbox, Part 1
- The DBA's Toolbox, Part 2
- Scripting Solutions for SQL Server
- Building a SQL Server Lab
- Using Graphics Files with SQL Server
- Enterprise Resource Planning
- Customer Relationship Management (CRM)
- Building a Reporting Data Server
- Building a Database Documenter, Part 1
- Building a Database Documenter, Part 2
- Data Management Objects
- Data Management Objects: The Server Object
- Data Management Objects: Server Object Methods
- Data Management Objects: Collections and the Database Object
- Data Management Objects: Database Information
- Data Management Objects: Database Control
- Data Management Objects: Database Maintenance
- Data Management Objects: Logging the Process
- Data Management Objects: Running SQL Statements
- Data Management Objects: Multiple Row Returns
- Data Management Objects: Other Database Objects
- Data Management Objects: Security
- Data Management Objects: Scripting
- Powershell and SQL Server - Overview
- PowerShell and SQL Server - Objects and Providers
- Powershell and SQL Server - A Script Framework
- Powershell and SQL Server - Logging the Process
- Powershell and SQL Server - Reading a Control File
- Powershell and SQL Server - SQL Server Access
- Powershell and SQL Server - Web Pages from a SQL Query
- Powershell and SQL Server - Scrubbing the Event Logs
- SQL Server 2008 PowerShell Provider
- SQL Server I/O: Importing and Exporting Data
- SQL Server I/O: XML in Database Terms
- SQL Server I/O: Creating XML Output
- SQL Server I/O: Reading XML Documents
- SQL Server I/O: Using XML Control Mechanisms
- SQL Server I/O: Creating Hierarchies
- SQL Server I/O: Using HTTP with SQL Server XML
- SQL Server I/O: Using HTTP with SQL Server XML Templates
- SQL Server I/O: Remote Queries
- SQL Server I/O: Working with Text Files
- Using Microsoft SQL Server on Handheld Devices
- Front-Ends 101: Microsoft Access
- Comparing Two SQL Server Databases
- English Query - Part 1
- English Query - Part 2
- English Query - Part 3
- English Query - Part 4
- English Query - Part 5
- RSS Feeds from SQL Server
- Using SQL Server Agent to Monitor Backups
- Reporting Services - Creating a Maintenance Report
- SQL Server Chargeback Strategies, Part 1
- SQL Server Chargeback Strategies, Part 2
- SQL Server Replication Example
- Creating a Master Agent and Alert Server
- The SQL Server Central Management System: Definition
- The SQL Server Central Management System: Base Tables
- The SQL Server Central Management System: Execution of Server Information (Part 1)
- The SQL Server Central Management System: Execution of Server Information (Part 2)
- The SQL Server Central Management System: Collecting Performance Metrics
- The SQL Server Central Management System: Centralizing Agent Jobs, Events and Scripts
- The SQL Server Central Management System: Reporting the Data and Project Summary
- Time Tracking for SQL Server Operations
- Migrating Departmental Data Stores to SQL Server
- Migrating Departmental Data Stores to SQL Server: Model the System
- Migrating Departmental Data Stores to SQL Server: Model the System, Continued
- Migrating Departmental Data Stores to SQL Server: Decide on the Destination
- Migrating Departmental Data Stores to SQL Server: Design the ETL
- Migrating Departmental Data Stores to SQL Server: Design the ETL, Continued
- Migrating Departmental Data Stores to SQL Server: Attach the Front End, Test, and Monitor
- Tracking SQL Server Timed Events, Part 1
- Tracking SQL Server Timed Events, Part 2
- Patterns and Practices for the Data Professional
- Managing Vendor Databases
- Consolidation Options
- Connecting to a SQL Azure Database from Microsoft Access
- SharePoint 2007 and SQL Server, Part One
- SharePoint 2007 and SQL Server, Part Two
- SharePoint 2007 and SQL Server, Part Three
- Querying Multiple Data Sources from a Single Location (Distributed Queries)
- Importing and Exporting Data for SQL Azure
- Working on Distributed Teams
- Professional Development
- Application Architecture Assessments
- Business Intelligence
- Tips and Troubleshooting
- Additional Resources
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.