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: 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:
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:
Remember I issued you a challenge: I told you to detail the Client entity. Let's see if you and I agreed:
- Start Date
- Primary Address
- Primary Phone
And now here are the rest of the entities defined, as I see them:
- Staff Member
- Years on Staff
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.