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: 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.
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:
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:
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:
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:
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!
Here's a database Diagram from a University with the thought-process explained.