Home > Articles > Data > SQL Server

SQL Server Reference Guide

Hosted by

Toggle Open Guide Table of ContentsGuide Contents

Close Table of ContentsGuide Contents

Close Table of Contents

Database Design: Finalizing Requirements and Defining Relationships

Last updated Mar 28, 2003.

I'm continuing a series on Database Design, and before this one you should at least review the article on Requirements, Entities and Attributes. It's important to understand that information before you start this section. I'll cover some ground here from the last article, but it's important enough to stand a little repeating.

To recap: in the last section I outlined the process of creating requirements. I spoke about the importance of carefully defining the data your system will store, and how it relates to your business. Recall that I came up with a simple set of business requirements for a business called BuckSoft Consulting.

I began the requirements gathering by stating what the business of the company is. This is the concise set of sentences that I came up with from various interviews and meetings with key users:

    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.

The sentence above doesn't constitute a complete requirements statement, of course, and not even enough to be the business requirements — but it will do for the examples I'll cover here. You can take this process and expand it against the full business requirements document. Again, I can't over-emphasize getting the organization to clearly define what they want the system to do, which you can use to create the data they need.

The sentence above represents a simple example of a segmented set of statements that will help us with the two things I need: the data to store, and the relationships between the sets of data.

In the previous article, I took these sentences and broke out the nouns into possible entities, which will eventually become tables:

  • Consultant
  • Client
  • Project
  • Team
  • Rate

Taking one noun (Project) as an example, I further broke that concept down by defining various attributes, which will become columns in the table. Once I parsed through the attributes a few times, I resolved only a few that are truly part of a project:

  • Project
  • Name
  • Phase
  • Budget
  • State

Remember I issued you a challenge: I told you to detail the Client entity. Let's see if you and I agreed:

  • Client
    • Name
    • Start Date
    • Primary Address
    • Primary Phone

And now here are the rest of the entities defined, as I see them:

  • Staff Member
    • Name
    • Years on Staff
  • Skills
    • Name
    • Classification
    • Level
  • Hours
    • Amount
    • Rate 

It's important to note that, while the entities I've defined above are the way I see it, you might come up with a different arrangement. That's okay! The business needs define the entity, and even how much detail you'll store. The important thing to do is capture all the data the business needs to store. Nothing more, nothing less.

I mentioned in the last article that I could resolve the parts that further defined a project with relationships. Relationships begin to define the business logic. There are two parts to the relationships: who owns what, and the action verbs in the definitions.

Here are the parts of a sentence that point out some possible relationships:

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.

By examining the words in bold, you'll begin to see who owns what and other relationships. I've explained here how these relationships will become Primary and Foreign Keys.

There are several parts to a relationship. The first part of a relationship is called cardinality. Cardinality involves the parent and child relationship — the part about who owns what. For instance, in our description above, I can see that Projects own Hours. After all, you can't bill hours unless you have a project to bill them against.

The next part of the relationship is the degree. The degree of relationship explains how many children the parent might have. Such values include one or more, one and only one, 10-15, and other possible values.

Optionality explains whether there needs to be a child. In our example, you need a client for a project; that relationship is not optional. However, I can have staff members who aren't assigned to a project, so the relationship between project and staff member is optional.

All of these conditions help enforce the integrity of the data, and are part of creating the Business Rules. This means even at the database level, you can help the system ensure that only valid data is entered into the system. For instance, in that last example, if the permissions are set correctly I've ensured that no one can enter a client for a project that doesn't exist. This is very important. Data Types also help enforce business rules, as you'll find later, as do other database constructs.

For practice, take the entities that you've defined (or use my description) and draw up the relationships you see based on cardinality, degree and optionality. You don't have to include the attributes for this exercise.

In the next article, I'll use a formal method to draw the entities and relationships: the ERD, or Entity Relationship Diagram. I'll also further define the entities.