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
Database Design: Adjusting The Model
Last updated Mar 28, 2003.
Database Design - Adjusting the Model
In this tutorial, we'll refine the data model that we created in the last few sections. In the last article, we turned the business requirements into entities, attributes and relationships on an Entity Relationship Diagram (ERD). To be more specific, we created a logical model.
This time, we'll examine the model closer, to ensure that our database design accurately reflects the application's business requirements. Once we've completed that step, we'll show this plan to the design team, which includes a representation from the business community. Without their input, even the entities are suspect (not to mention the attributes they contain). One primary factor in poorly received systems is the failure to include the end users in all phases of the design process.
You'll recall that we've gathered the business requirements from the business community. This is often presented in the form of a fairly lengthy narrative, checked and re-checked against current or proposed business processes. The next phase of the design process is to take that narrative and break it into single-concept sentences. Both of these steps might be done by a DBA, but in larger shops this falls within the purview of the Business Analyst.
We got to this step in an earlier article, and the final narrative looked like this:
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.
This set of statements is quite simple, to be sure, but it will serve for our tutorial purposes. Data Modeling is quite exponential, in that adding one more level to the requirements would explode the diagram into something that wouldn't fit on one screen!
We derived the entities and attributes from the nouns in the sentence. Here's another statement written from the one above regarding a project:
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.
From the verbs in the first set of statements and all subsequent descriptions, we derived our relationships.
With all this information, we arrived at the following logical Entity Relationship Diagram:
We now come to the purpose of this tutorial, the design review. It is important to understand that the model is a tool it assists us in the design, and is not the final outcome of the design. This is an important distinction; I have seen groups of professionals that feel once a design has reached the "on paper" stage that it is sacrosanct and cannot be tampered with. Nothing should be further from the truth. This graphical design should flush out any design flaws.
I mentioned in the last article that this design may have had a few errors in it. Let's examine the various entities, attributes and relationships to see if the errors rear themselves.
We'll begin with the entities. Do they capture all that is related in the business requirements? The stated goal for this project is to capture all efforts from an hours-standpoint on a given project. To do that, we need to house information for three main entities: Projects, Clients and Consultants.
Let's begin with the Clients entity. Does it contain all the information regarding a client?
We've got attributes in the Clients entity that you might expect such as name, address, and phone, but we also have another attribute, called Start Date, that you may not have considered. Since the requirements don't call for this information, why did we include it?
A start date on the Clients entity will help us determine when we signed the client to the first activities that we perform on a project for that client.
Now we move on to the Projects entity. Does it contain all relevant project information?
The model includes the attributes of name, phase, budget and state, in accordance with the business requirements. To be sure, there are several other attributes that we could have added but didn't. One glaring exception might be the lack of a start or end date for the project. Shouldn't that be a key entity here? Actually, the only part about the project that we're interested in is the work our consultants do on the project. Since that information will be stored in the Hours entity, there's no need to track it twice.
And, finally, we consider the consultants, or Staff Members entity. Does it capture what we need to know about the consultants working on the project?
Just in case we have two people with the same name, I've included a code to uniquely identify each staff member. The model also includes the consultant's name, so we can track who does the work. Notice that the model shows that the years of service as an attribute of the staff member entity. There is only one value for both name and years on staff.
Each consultant, however, may have more than one skill. To keep each entity separate, I've included a Skills entity to track not only the name, but the classification and level the consultant has for that skill. While this isn't technically included in the business requirements, we may need this information to adequately determine if we have the proper staff on the project.
We haven't included things like address, social security number, phone, and so forth for the Staff Member entity. The business requirements don't state that we need to store this information, so in this case we won't.
Using the same logic regarding consultant skills, I've attached the Hours entity to the Staff Members entity. Staff Members work hours, and we can see that relationship here.
I mentioned in the last article that there were entities which came into play only when relationships were considered. Referring back to our diagram, you can see I've created one such entity, called ProjectStaff. This entity stores the relating attributes between Projects and Staff Members. The logic that used for that decision was that staff members worked on projects.
While this is true, is this in fact the best way to model this relationship? The difficulty lies in the Hours entity. Do hours actually belong to staff members? At first glance, it might seem so. But when we re-read the business requirements, we see that from some perspectives, the hours are owned not by the consultant who works them, but the project and the staff member. For us to present hours for a given project using our current model, we would need to join the Projects entity to the Staff Members entity, and then derive the hours from there. This is a rather cumbersome method, considering the business requirements we were presented several articles ago.
Consider this revised model:
Notice that I've removed the ProjectStaff entity and moved the Hours entity into its place. I did this to show that hours are not independent of any other entity, and that they aren't really owned by the project or the staff members. It is the intersection of a project and a staff member that causes an hour to materialize.
Does this model more accurately represent the relationship? I believe that it does. Is this the only way to model that relationship? Certainly not! Just as in the Roman era, many roads lead to the same result. Often, the database design is considered from only one perspective.
In our next tutorial, we'll develop this logical model to a physical Entity Relationship Diagram. Again, we may discover an issue or two with the design. That's OK! We'll fix it and move on.
Note that I've left a couple of glaring issues here. Hint: Where do we track what the consultant did during his or her hours? For that matter, what were the hours? Stay tuned!