Home > Articles > Databases > SQL Server

SQL Server Reference Guide

Hosted by Buck Woody

Guide Contents

< Back Page 115 of 361 Next >

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:

Figure 81

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:

Figure 82

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!