Table of Contents
- Microsoft SQL Server Defined
- Microsoft SQL Server Features
- Microsoft SQL Server Administration
Microsoft SQL Server Programming
- An Outline for Development
- Database Services
- Database Objects: Databases
- Database Objects: Tables
- Database Objects: Table Relationships
- Database Objects: Keys
- Database Objects: Constraints
- Database Objects: Data Types
- Database Objects: Views
- Database Objects: Stored Procedures
- Database Objects: Indexes
- Database Objects: User Defined Functions
- Database Objects: Triggers
- Database Design: Requirements, Entities, and Attributes
- Business Process Model Notation (BPMN) and the Data Professional
- Business Questions for Database Design, Part One
- Business Questions for Database Design, Part Two
- Database Design: Finalizing Requirements and Defining Relationships
- Database Design: Creating an Entity Relationship Diagram
- Database Design: The Logical ERD
- Database Design: Adjusting The Model
- Database Design: Normalizing the Model
- Creating The Physical Model
- Database Design: Changing Attributes to Columns
- Database Design: Creating The Physical Database
- Database Design Example: Curriculum Vitae
- The SQL Server Sample Databases
- The SQL Server Sample Databases: pubs
- The SQL Server Sample Databases: NorthWind
- The SQL Server Sample Databases: AdventureWorks
- The SQL Server Sample Databases: Adventureworks Derivatives
- UniversalDB: The Demo and Testing Database, Part 1
- UniversalDB: The Demo and Testing Database, Part 2
- UniversalDB: The Demo and Testing Database, Part 3
- UniversalDB: The Demo and Testing Database, Part 4
- Getting Started with Transact-SQL
- Transact-SQL: Data Definition Language (DDL) Basics
- Transact-SQL: Limiting Results
- Transact-SQL: More Operators
- Transact-SQL: Ordering and Aggregating Data
- Transact-SQL: Subqueries
- Transact-SQL: Joins
- Transact-SQL: Complex Joins - Building a View with Multiple JOINs
- Transact-SQL: Inserts, Updates, and Deletes
- An Introduction to the CLR in SQL Server 2005
- Design Elements Part 1: Programming Flow Overview, Code Format and Commenting your Code
- Design Elements Part 2: Controlling SQL's Scope
- Design Elements Part 3: Error Handling
- Design Elements Part 4: Variables
- Design Elements Part 5: Where Does The Code Live?
- Design Elements Part 6: Math Operators and Functions
- Design Elements Part 7: Statistical Functions
- Design Elements Part 8: Summarization Statistical Algorithms
- Design Elements Part 9:Representing Data with Statistical Algorithms
- Design Elements Part 10: Interpreting the Data—Regression
- Design Elements Part 11: String Manipulation
- Design Elements Part 12: Loops
- Design Elements Part 13: Recursion
- Design Elements Part 14: Arrays
- Design Elements Part 15: Event-Driven Programming Vs. Scheduled Processes
- Design Elements Part 16: Event-Driven Programming
- Design Elements Part 17: Program Flow
- Forming Queries Part 1: Design
- Forming Queries Part 2: Query Basics
- Forming Queries Part 3: Query Optimization
- Forming Queries Part 4: SET Options
- Forming Queries Part 5: Table Optimization Hints
- Using SQL Server Templates
- Transact-SQL Unit Testing
- Index Tuning Wizard
- Unicode and SQL Server
- SQL Server Development Tools
- The SQL Server Transact-SQL Debugger
- The Transact-SQL Debugger, Part 2
- Basic Troubleshooting for Transact-SQL Code
- An Introduction to Spatial Data in SQL Server 2008
- Performance Tuning
- Practical Applications
- Professional Development
- Application Architecture Assessments
- Business Intelligence
- Tips and Troubleshooting
- Additional Resources
Business Questions for Database Design, Part One
Last updated Mar 28, 2003.
Teasing out requirements for a database design should be a straightforward affair, but it most often isn’t. You might think that the organization asking you for a database design is aware of what they need to store for a program to be successful. And you might also think that the technical team assigned to the project would be able to take the request from the organization directly and translate that to a set of tables, fields, and other database objects.
But the disconnect comes because the organization doesn’t always know all of the data that a program requires to operate properly, and the technical team doesn’t always know all of the data the organization needs to store. In other words, each side doesn’t know what the other needs — at least not completely.
The answer is pretty obvious to both the organization and the technical team: the “other side” needs to learn more. The organization feels that the technical team should know more about the business and how it runs, and the technical team feels that the organization needs to learn more about how a database is put together. And of course in both cases that means time, money, and effort.
For the project to be successful there has to be a meeting in the middle somewhere. And there are a few methods that you, as the technical team, can use to bring both sides together. The reason you have to make the first move is simple — the business doesn’t know how the parts of technology work together, and since they are the “customer”, you’ll need to help them help you.
Explaining the Problem
The first thing to do is to explain to the key members of the business what you need to know to make a successful system. If you are fortunate enough to have a good Business Analyst (BA) or two in the company, you’re in luck. This is precisely what those folks do — they know both the business and technology, and can help pave the way.
But more and more often, organizations don’t employ these useful resources. The business team and the technical team have to figure it out on their own, and since you’ll own the design and the changes if the design doesn’t work, it’s in your best interest to get it done as right as possible the first time. In fact, it’s also in the best interest of the business as well, and the first thing you should do is help them understand that.
I don’t normally advocate meetings. I think there are far too many of them in our day already, and I don’t think they accomplish all that much. But if a meeting is the best way to get this information across quickly, then call only the people directly involved in the process and run that meeting as effectively as possible. My personal preference is an e-mail exchange or even better a wiki site that everyone can update. Whatever method you choose, find the fastest, most complete way you can. And don’t let the tool or process become more frustrating than useful. I’ve used wiki software successfully multiple times, but I had one instance where the team just hated it. So I dropped it and use d more traditional meeting approach. The key is to get the job done, not use some favored process.
You’ll need to identify the team members for the meeting. You should include the development lead and the database designer from the technical team, and the representatives from the business side who know the processes, rules and regulations best for each section of the program where you’ll store data. That might be one person or many, and it might be different people at different phases of the process.
I’ll put the rest of this article in terms of a meeting, but whether you call a meeting or use a wiki, this group should agree to be as clear as possible with each other, to save each other time and produce the best outcome possible. Each team member should sign their part of the design. While this sounds a bit much, it really brings a sense of how important the design process is to the business and ultimately the code.
The first thing to convey in the meeting is the reason for the meeting itself. To the business people in the room, it may be completely obvious what they need to store in the database. But they are not always aware of meta-data or the way data is broken down. It’s your job to explain that to them as clearly as you can, without talking down to them, being arrogant, or trying to teach them to be computer scientists. I normally take the approach of using a very simple example of a requirement, and then I map that out to a small set of tables to help them understand what we need to know.
Depending on how technical the folks in the room are, or want to be, you can make that example more or less complex. Here’s an example of an exchange for keeping the discussion simple.
“Hello, my name is Buck, and I’m part of the technical team. We’re writing a program to help with the XYZ process, and I’ve been told that you can help us do that — we know how to write databases and software, but we don’t know the business as well as you do.
“We could use your help at this point on creating the database. We find that what we need to store for the program is often different than what you might need on a paper form or some other tracking system. We’d like to get your help finding out all of the things we need to store so that when you end up using the program it will be correct, and easy for you to use.
“Here’s a simple statement that might help you understand what we need. Let’s assume we’re going to write a recipe database for cooking. The cooks might tell us that they store the following things:
“These are the only things they currently have on their recipe cards. To them, that’s all they need. But if you were going to write a database for them, it is a bit more complex than that. For instance, could it be possible to have a recipe with only instructions? For the ingredients, is that a set list from a certain location (like what is in the larder at the moment) or can they type anything in? Are the amounts in ounces, units, grams, pounds, or all of the above? Do we need to remove those amounts from the ingredient list kept in the larder? Are the instructions free-form, or are they a certain series of very defined steps, like in a restaurant?
“As you can see, even these simple questions can’t be answered by the technical team writing the cook’s database. And that’s why we need your help. We’ll ask a lot of pesky questions, so that you don’t have to learn to be a computer programmer to get this project done.”
That usually does the trick on my projects. On some projects, there’s either a Business Analyst present or the businesspeople have been through a design session before, so it’s pretty easily done. But in the case where they haven’t, a simple explanation like this one can help the business understand what they need to help you with.
I can’t over-emphasize the “soft-skills” of working with the customer on getting the design correct. You should show the utmost respect, even when you don’t get that level of respect back. In the end, you’ll have to live with the design, regardless of how well the organization worked with you on getting it right or not, so it’s up to you to find the best way to get that assistance That being said, a design session can be a lot of fun. I’ve had business people that really enjoyed being able to see the other side of the process, and a few of them even learned things like how to create a simple Entity Relationship Diagram. While that’s not expected of them, it was interesting to see how many really want to learn to do that.
After you set the tone for the process, you can begin to set up the logistics of how the design sessions will go. For a simple design, the entire process might take only two or three “sittings”. For a more complex design, more time is required. In any case, resist the temptation to knock out the design in one go. Time and again I’ve seen teams close out the design after only one meeting, and almost every time they have to make painful changes later because one part of the design did not get thought through properly. At the very least you should have two sessions, and they should be separated by at least a day. Whichever methods you’re using, make sure the businesspeople know the level of effort you need from them, and the kind of information you’ll be looking for so that they can plan their day.
If you’re using the wiki or e-mail approach, make sure you “time-box” the process, setting deadlines for when you need each part of the information. While an offline process like one of these two is attractive, it requires more watchfulness on your side. It’s easy for the businessperson to get busy and give this process a short frame of attention when you don’t have them off by themselves somewhere.
One last thing on the soft skills — when the process is over, write the person’s boss and thank them for allowing that person to help, and for their time. Sure, it’s part of their job just like it’s part of yours, but this kind of thing shows your maturity in the process and pays enormous dividends later in your career.
In the next installment I’ll show you a few methods of getting the information you need, sing a few “hard” technical skills.