Home > Articles > Databases > SQL Server

SQL Server Reference Guide

< Back Page 114 of 361 Next >

Database Design: The Logical ERD

Last updated Mar 28, 2003.

In this tutorial, we'll put to use all the concepts we've learned about database objects, business requirements, entities and relationships and Entity Relationship Diagrams (ERDs). Starting here, we'll begin the exciting process of building a database.

There are three types of ERDs: the conceptual, the logical, and the physical. The conceptual diagram displays only the main entities and their high-level relationships, and is useful during the requirements phase to help the business community understand how you're thinking about their database. The logical diagram communicates more details about these requirements to the database engineers and developers. The physical diagram is platform-specific, and goes into even further detail. We'll cover the physical ERD for SQL Server 2000 in a future tutorial.

In this tutorial, we'll take our business requirements (see the Business Requirements tutorial) and map them to a logical ERD. As you'll learn, the ERD is not only used to communicate the database design to others; it's also used to solidify the design in the first place!

So where do we start? The best place to start designing our ERD is with the primary entities. We have to ask ourselves: what are we trying to do? Taking a look at our business requirements, at the highest level we see that this database will store consultant billing hours for projects. The entities in that sentence are consultant, hours, and projects.

During our design process, we're mainly asking questions that the ERD will answer. Answering those questions completely and accurately creates our design.

To begin, let's take one of those high-level entities, projects. We've been defining this entity throughout the last three or four articles, and have fleshed it out a bit. As we move forward, we'll flesh it out even more. Placing the entity squarely in the center of our diagram, we begin our questions here.

Figure 75

We have to ask ourselves at this point if the projects own things – that defines our relationships. In our last tutorial we found out how to discover the "parent" entity – the one with the square box – by asking who owns what. Can I have a project without a client? No, since even if a consultant is working on an in-house project, there is a client – ourselves! So we believe that clients own projects.

But hold on a minute. Let's check our assumptions with the business community. It may be that projects can belong to more than one client. Perhaps we're working on a government project for the Army and Air Force. In that case, how do we track the numbers? Do we have to provide separate codes for each to keep billing on track? The answer to that question may turn our diagram around, making the project the owning entity to clients. Making snap judgments is a quick way to design a program you really like, and that the clients really hate!

After checking our assumptions on the client/projects issue, we discover that the business only has one client per project, making the client entity the parent. In other words, you can't have a project without a client first. So far, our logical ERD looks like this:

Figure 76

Do you remember all the symbols from the last article? The box type of the entities shows who owns what (cardinality), the cross-bar on the line shows what is required and the circle on the line shows what is not required (optionality) and the multiple "crow's feet" shows the many side (degree). Whew! This diagram snippet shows that the clients own projects, that if you have a project you must have a client, that you can have a client without a project, and that there can be many projects per client. Quite a bit of information from one small diagram, wouldn't you say?

To complete our diagram, we just continue this question-and-answer process. As a refresher, here are the entities we came up with a few articles back:

Projects
 Name
 Phase
 Budget
 State
Clients
 Name
 Start Date
 Primary Address
 Primary Phone
Staff Members
 Name
 Years on Staff
Skills
 Name
 Classification
 Level
Hours
 Amount
 Rate

Let's extend the diagram a bit. We have the Project and Client entities defined and related, so what's next? Well, we've got clients who own projects. Let's lay out the staff members who work on the projects.

Recall that we don't have to have a project or a client to have employees, so the Staff Member entity stands alone. Let's place it on the diagram, complete with attributes:

Figure 77

Staff Members have skills – and some of them have multiple skills. In our next tutorial, we'll learn more about normalization, but for now it's important to note that we only want to store data one time.

What that means here is that, because a staff member (potentially) has many skills, we need to create another entity for those skills. Not only does this resolve the many-rows issue, but it lets us re-use the same skill code for many consultants. Here's that part of the diagram added in:

Figure 78

You can also see I've added in a "level" on the skill, where values like "Senior" or "Advanced" or "Junior" might go.

Next, we ask ourselves another question. Where do the hours go, on the project or in regard to the staff member? The answer might go either way, but we said in our business requirements that hours are worked by consultants, not by projects. That makes the Staff Members entity the parent of the Hours entity:

Figure 79

Notice the "Rate" attribute, which allows us to set a rate for that consultant. I was tempted at first to place a "Rate" attribute in the Staff Members entity, but I remembered that the staff member might have several skills, at various levels, making him or her worth more or less for a particular role. That meant that the rate belonged to hours, not to the staff member. You can see from this example the iterative nature of the design process.

We seem to be finished – all the entities have been placed on the diagram, and all their relationships are defined. The problem is that there doesn't seem to be any link from the staff members and the projects. When we think it through we see that a particular consultant might work on many projects. We also see that a project might have several consultants. This is called a many-to-many relationship, and the only way we can resolve it is with another entity. This entity will store one row for each of the two entities, and allow the join. Here's that part of the diagram:

Figure <<Graphic: LogicalERD-6.tif>>

You can see the two attributes that tie the two entities together here and one additional attribute as well: "Role." This attribute fits here perfectly, as it only exists at the intersection of a staff member working on a particular project.

And that completes this simple database – at least on the logical level. While creating this diagram I've made some rather broad assumptions, some of which might not be correct. For instance, you'll notice that the Projects entity has no start date. On the surface, that might not seem correct – don't all projects have a start date? Yes, they do, but keep in mind this database tracks the fact that we're supplying consultants to the project, and the purpose of this database is to track their hours, not the project. It's often easy to build a system that does more than it should. During the design process, you should make frequent references to the business requirements with what the database needs to store.

So is this diagram the only correct one? Absolutely not! Your diagram might look totally different, and yet accomplish the same thing. By the way, I've purposely built-in a few mistakes here so that we can fix them along the way. See if you can spot them – but don't look too hard. We're going to find them fairly quickly!

Well, that's it for this week. Next week we'll review this design and begin mapping it to the physical ERD. See you then!