Home > Articles > Data > SQL Server

📄 Contents

  1. SQL Server Reference Guide
  2. Introduction
  3. SQL Server Reference Guide Overview
  4. Table of Contents
  5. Microsoft SQL Server Defined
  6. SQL Server Editions
  7. SQL Server Access
  8. Informit Articles and Sample Chapters
  9. Online Resources
  10. Microsoft SQL Server Features
  11. SQL Server Books Online
  12. Clustering Services
  13. Data Transformation Services (DTS) Overview
  14. Replication Services
  15. Database Mirroring
  16. Natural Language Processing (NLP)
  17. Analysis Services
  18. Microsot SQL Server Reporting Services
  19. XML Overview
  20. Notification Services for the DBA
  21. Full-Text Search
  22. SQL Server 2005 - Service Broker
  23. Using SQL Server as a Web Service
  24. SQL Server Encryption Options Overview
  25. SQL Server 2008 Overview
  26. SQL Server 2008 R2 Overview
  27. SQL Azure
  28. The Utility Control Point and Data Application Component, Part 1
  29. The Utility Control Point and Data Application Component, Part 2
  30. Microsoft SQL Server Administration
  31. The DBA Survival Guide: The 10 Minute SQL Server Overview
  32. Preparing (or Tuning) a Windows System for SQL Server, Part 1
  33. Preparing (or Tuning) a Windows System for SQL Server, Part 2
  34. Installing SQL Server
  35. Upgrading SQL Server
  36. SQL Server 2000 Management Tools
  37. SQL Server 2005 Management Tools
  38. SQL Server 2008 Management Tools
  39. SQL Azure Tools
  40. Automating Tasks with SQL Server Agent
  41. Run Operating System Commands in SQL Agent using PowerShell
  42. Automating Tasks Without SQL Server Agent
  43. Storage – SQL Server I/O
  44. Service Packs, Hotfixes and Cumulative Upgrades
  45. Tracking SQL Server Information with Error and Event Logs
  46. Change Management
  47. SQL Server Metadata, Part One
  48. SQL Server Meta-Data, Part Two
  49. Monitoring - SQL Server 2005 Dynamic Views and Functions
  50. Monitoring - Performance Monitor
  51. Unattended Performance Monitoring for SQL Server
  52. Monitoring - User-Defined Performance Counters
  53. Monitoring: SQL Server Activity Monitor
  54. SQL Server Instances
  55. DBCC Commands
  56. SQL Server and Mail
  57. Database Maintenance Checklist
  58. The Maintenance Wizard: SQL Server 2000 and Earlier
  59. The Maintenance Wizard: SQL Server 2005 (SP2) and Later
  60. The Web Assistant Wizard
  61. Creating Web Pages from SQL Server
  62. SQL Server Security
  63. Securing the SQL Server Platform, Part 1
  64. Securing the SQL Server Platform, Part 2
  65. SQL Server Security: Users and other Principals
  66. SQL Server Security – Roles
  67. SQL Server Security: Objects (Securables)
  68. Security: Using the Command Line
  69. SQL Server Security - Encrypting Connections
  70. SQL Server Security: Encrypting Data
  71. SQL Server Security Audit
  72. High Availability - SQL Server Clustering
  73. SQL Server Configuration, Part 1
  74. SQL Server Configuration, Part 2
  75. Database Configuration Options
  76. 32- vs 64-bit Computing for SQL Server
  77. SQL Server and Memory
  78. Performance Tuning: Introduction to Indexes
  79. Statistical Indexes
  80. Backup and Recovery
  81. Backup and Recovery Examples, Part One
  82. Backup and Recovery Examples, Part Two: Transferring Databases to Another System (Even Without Backups)
  83. SQL Profiler - Reverse Engineering An Application
  84. SQL Trace
  85. SQL Server Alerts
  86. Files and Filegroups
  87. Partitioning
  88. Full-Text Indexes
  89. Read-Only Data
  90. SQL Server Locks
  91. Monitoring Locking and Deadlocking
  92. Controlling Locks in SQL Server
  93. SQL Server Policy-Based Management, Part One
  94. SQL Server Policy-Based Management, Part Two
  95. SQL Server Policy-Based Management, Part Three
  96. Microsoft SQL Server Programming
  97. An Outline for Development
  98. Database
  99. Database Services
  100. Database Objects: Databases
  101. Database Objects: Tables
  102. Database Objects: Table Relationships
  103. Database Objects: Keys
  104. Database Objects: Constraints
  105. Database Objects: Data Types
  106. Database Objects: Views
  107. Database Objects: Stored Procedures
  108. Database Objects: Indexes
  109. Database Objects: User Defined Functions
  110. Database Objects: Triggers
  111. Database Design: Requirements, Entities, and Attributes
  112. Business Process Model Notation (BPMN) and the Data Professional
  113. Business Questions for Database Design, Part One
  114. Business Questions for Database Design, Part Two
  115. Database Design: Finalizing Requirements and Defining Relationships
  116. Database Design: Creating an Entity Relationship Diagram
  117. Database Design: The Logical ERD
  118. Database Design: Adjusting The Model
  119. Database Design: Normalizing the Model
  120. Creating The Physical Model
  121. Database Design: Changing Attributes to Columns
  122. Database Design: Creating The Physical Database
  123. Database Design Example: Curriculum Vitae
  124. NULLs
  125. The SQL Server Sample Databases
  126. The SQL Server Sample Databases: pubs
  127. The SQL Server Sample Databases: NorthWind
  128. The SQL Server Sample Databases: AdventureWorks
  129. The SQL Server Sample Databases: Adventureworks Derivatives
  130. UniversalDB: The Demo and Testing Database, Part 1
  131. UniversalDB: The Demo and Testing Database, Part 2
  132. UniversalDB: The Demo and Testing Database, Part 3
  133. UniversalDB: The Demo and Testing Database, Part 4
  134. Getting Started with Transact-SQL
  135. Transact-SQL: Data Definition Language (DDL) Basics
  136. Transact-SQL: Limiting Results
  137. Transact-SQL: More Operators
  138. Transact-SQL: Ordering and Aggregating Data
  139. Transact-SQL: Subqueries
  140. Transact-SQL: Joins
  141. Transact-SQL: Complex Joins - Building a View with Multiple JOINs
  142. Transact-SQL: Inserts, Updates, and Deletes
  143. An Introduction to the CLR in SQL Server 2005
  144. Design Elements Part 1: Programming Flow Overview, Code Format and Commenting your Code
  145. Design Elements Part 2: Controlling SQL's Scope
  146. Design Elements Part 3: Error Handling
  147. Design Elements Part 4: Variables
  148. Design Elements Part 5: Where Does The Code Live?
  149. Design Elements Part 6: Math Operators and Functions
  150. Design Elements Part 7: Statistical Functions
  151. Design Elements Part 8: Summarization Statistical Algorithms
  152. Design Elements Part 9:Representing Data with Statistical Algorithms
  153. Design Elements Part 10: Interpreting the Data—Regression
  154. Design Elements Part 11: String Manipulation
  155. Design Elements Part 12: Loops
  156. Design Elements Part 13: Recursion
  157. Design Elements Part 14: Arrays
  158. Design Elements Part 15: Event-Driven Programming Vs. Scheduled Processes
  159. Design Elements Part 16: Event-Driven Programming
  160. Design Elements Part 17: Program Flow
  161. Forming Queries Part 1: Design
  162. Forming Queries Part 2: Query Basics
  163. Forming Queries Part 3: Query Optimization
  164. Forming Queries Part 4: SET Options
  165. Forming Queries Part 5: Table Optimization Hints
  166. Using SQL Server Templates
  167. Transact-SQL Unit Testing
  168. Index Tuning Wizard
  169. Unicode and SQL Server
  170. SQL Server Development Tools
  171. The SQL Server Transact-SQL Debugger
  172. The Transact-SQL Debugger, Part 2
  173. Basic Troubleshooting for Transact-SQL Code
  174. An Introduction to Spatial Data in SQL Server 2008
  175. Performance Tuning
  176. Performance Tuning SQL Server: Tools and Processes
  177. Performance Tuning SQL Server: Tools Overview
  178. Creating a Performance Tuning Audit - Defining Components
  179. Creating a Performance Tuning Audit - Evaluation Part One
  180. Creating a Performance Tuning Audit - Evaluation Part Two
  181. Creating a Performance Tuning Audit - Interpretation
  182. Creating a Performance Tuning Audit - Developing an Action Plan
  183. Understanding SQL Server Query Plans
  184. Performance Tuning: Implementing Indexes
  185. Performance Monitoring Tools: Windows 2008 (and Higher) Server Utilities, Part 1
  186. Performance Monitoring Tools: Windows 2008 (and Higher) Server Utilities, Part 2
  187. Performance Monitoring Tools: Windows System Monitor
  188. Performance Monitoring Tools: Logging with System Monitor
  189. Performance Monitoring Tools: User Defined Counters
  190. General Transact-SQL (T-SQL) Performance Tuning, Part 1
  191. General Transact-SQL (T-SQL) Performance Tuning, Part 2
  192. General Transact-SQL (T-SQL) Performance Tuning, Part 3
  193. Performance Monitoring Tools: An Introduction to SQL Profiler
  194. Performance Tuning: Introduction to Indexes
  195. Performance Monitoring Tools: SQL Server 2000 Index Tuning Wizard
  196. Performance Monitoring Tools: SQL Server 2005 Database Tuning Advisor
  197. Performance Monitoring Tools: SQL Server Management Studio Reports
  198. Performance Monitoring Tools: SQL Server 2008 Activity Monitor
  199. The SQL Server 2008 Management Data Warehouse and Data Collector
  200. Performance Monitoring Tools: Evaluating Wait States with PowerShell and Excel
  201. Practical Applications
  202. Choosing the Back End
  203. The DBA's Toolbox, Part 1
  204. The DBA's Toolbox, Part 2
  205. Scripting Solutions for SQL Server
  206. Building a SQL Server Lab
  207. Using Graphics Files with SQL Server
  208. Enterprise Resource Planning
  209. Customer Relationship Management (CRM)
  210. Building a Reporting Data Server
  211. Building a Database Documenter, Part 1
  212. Building a Database Documenter, Part 2
  213. Data Management Objects
  214. Data Management Objects: The Server Object
  215. Data Management Objects: Server Object Methods
  216. Data Management Objects: Collections and the Database Object
  217. Data Management Objects: Database Information
  218. Data Management Objects: Database Control
  219. Data Management Objects: Database Maintenance
  220. Data Management Objects: Logging the Process
  221. Data Management Objects: Running SQL Statements
  222. Data Management Objects: Multiple Row Returns
  223. Data Management Objects: Other Database Objects
  224. Data Management Objects: Security
  225. Data Management Objects: Scripting
  226. Powershell and SQL Server - Overview
  227. PowerShell and SQL Server - Objects and Providers
  228. Powershell and SQL Server - A Script Framework
  229. Powershell and SQL Server - Logging the Process
  230. Powershell and SQL Server - Reading a Control File
  231. Powershell and SQL Server - SQL Server Access
  232. Powershell and SQL Server - Web Pages from a SQL Query
  233. Powershell and SQL Server - Scrubbing the Event Logs
  234. SQL Server 2008 PowerShell Provider
  235. SQL Server I/O: Importing and Exporting Data
  236. SQL Server I/O: XML in Database Terms
  237. SQL Server I/O: Creating XML Output
  238. SQL Server I/O: Reading XML Documents
  239. SQL Server I/O: Using XML Control Mechanisms
  240. SQL Server I/O: Creating Hierarchies
  241. SQL Server I/O: Using HTTP with SQL Server XML
  242. SQL Server I/O: Using HTTP with SQL Server XML Templates
  243. SQL Server I/O: Remote Queries
  244. SQL Server I/O: Working with Text Files
  245. Using Microsoft SQL Server on Handheld Devices
  246. Front-Ends 101: Microsoft Access
  247. Comparing Two SQL Server Databases
  248. English Query - Part 1
  249. English Query - Part 2
  250. English Query - Part 3
  251. English Query - Part 4
  252. English Query - Part 5
  253. RSS Feeds from SQL Server
  254. Using SQL Server Agent to Monitor Backups
  255. Reporting Services - Creating a Maintenance Report
  256. SQL Server Chargeback Strategies, Part 1
  257. SQL Server Chargeback Strategies, Part 2
  258. SQL Server Replication Example
  259. Creating a Master Agent and Alert Server
  260. The SQL Server Central Management System: Definition
  261. The SQL Server Central Management System: Base Tables
  262. The SQL Server Central Management System: Execution of Server Information (Part 1)
  263. The SQL Server Central Management System: Execution of Server Information (Part 2)
  264. The SQL Server Central Management System: Collecting Performance Metrics
  265. The SQL Server Central Management System: Centralizing Agent Jobs, Events and Scripts
  266. The SQL Server Central Management System: Reporting the Data and Project Summary
  267. Time Tracking for SQL Server Operations
  268. Migrating Departmental Data Stores to SQL Server
  269. Migrating Departmental Data Stores to SQL Server: Model the System
  270. Migrating Departmental Data Stores to SQL Server: Model the System, Continued
  271. Migrating Departmental Data Stores to SQL Server: Decide on the Destination
  272. Migrating Departmental Data Stores to SQL Server: Design the ETL
  273. Migrating Departmental Data Stores to SQL Server: Design the ETL, Continued
  274. Migrating Departmental Data Stores to SQL Server: Attach the Front End, Test, and Monitor
  275. Tracking SQL Server Timed Events, Part 1
  276. Tracking SQL Server Timed Events, Part 2
  277. Patterns and Practices for the Data Professional
  278. Managing Vendor Databases
  279. Consolidation Options
  280. Connecting to a SQL Azure Database from Microsoft Access
  281. SharePoint 2007 and SQL Server, Part One
  282. SharePoint 2007 and SQL Server, Part Two
  283. SharePoint 2007 and SQL Server, Part Three
  284. Querying Multiple Data Sources from a Single Location (Distributed Queries)
  285. Importing and Exporting Data for SQL Azure
  286. Working on Distributed Teams
  287. Professional Development
  288. Becoming a DBA
  289. Certification
  290. DBA Levels
  291. Becoming a Data Professional
  292. SQL Server Professional Development Plan, Part 1
  293. SQL Server Professional Development Plan, Part 2
  294. SQL Server Professional Development Plan, Part 3
  295. Evaluating Technical Options
  296. System Sizing
  297. Creating a Disaster Recovery Plan
  298. Anatomy of a Disaster (Response Plan)
  299. Database Troubleshooting
  300. Conducting an Effective Code Review
  301. Developing an Exit Strategy
  302. Data Retention Strategy
  303. Keeping Your DBA/Developer Job in Troubled Times
  304. The SQL Server Runbook
  305. Creating and Maintaining a SQL Server Configuration History, Part 1
  306. Creating and Maintaining a SQL Server Configuration History, Part 2
  307. Creating an Application Profile, Part 1
  308. Creating an Application Profile, Part 2
  309. How to Attend a Technical Conference
  310. Tips for Maximizing Your IT Budget This Year
  311. The Importance of Blue-Sky Planning
  312. Application Architecture Assessments
  313. Transact-SQL Code Reviews, Part One
  314. Transact-SQL Code Reviews, Part Two
  315. Cloud Computing (Distributed Computing) Paradigms
  316. NoSQL for the SQL Server Professional, Part One
  317. NoSQL for the SQL Server Professional, Part Two
  318. Object-Role Modeling (ORM) for the Database Professional
  319. Business Intelligence
  320. BI Explained
  321. Developing a Data Dictionary
  322. BI Security
  323. Gathering BI Requirements
  324. Source System Extracts and Transforms
  325. ETL Mechanisms
  326. Business Intelligence Landscapes
  327. Business Intelligence Layouts and the Build or Buy Decision
  328. A Single Version of the Truth
  329. The Operational Data Store (ODS)
  330. Data Marts – Combining and Transforming Data
  331. Designing Data Elements
  332. The Enterprise Data Warehouse — Aggregations and the Star Schema
  333. On-Line Analytical Processing (OLAP)
  334. Data Mining
  335. Key Performance Indicators
  336. BI Presentation - Client Tools
  337. BI Presentation - Portals
  338. Implementing ETL - Introduction to SQL Server 2005 Integration Services
  339. Building a Business Intelligence Solution, Part 1
  340. Building a Business Intelligence Solution, Part 2
  341. Building a Business Intelligence Solution, Part 3
  342. Tips and Troubleshooting
  343. SQL Server and Microsoft Excel Integration
  344. Tips for the SQL Server Tools: SQL Server 2000
  345. Tips for the SQL Server Tools – SQL Server 2005
  346. Transaction Log Troubles
  347. SQL Server Connection Problems
  348. Orphaned Database Users
  349. Additional Resources
  350. Tools and Downloads
  351. Utilities (Free)
  352. Tool Review (Free): DBDesignerFork
  353. Aqua Data Studio
  354. Microsoft SQL Server Best Practices Analyzer
  355. Utilities (Cost)
  356. Quest Software's TOAD for SQL Server
  357. Quest Software's Spotlight on SQL Server
  358. SQL Server on Microsoft's Virtual PC
  359. Red Gate SQL Bundle
  360. Microsoft's Visio for Database Folks
  361. Quest Capacity Manager
  362. SQL Server Help
  363. Visual Studio Team Edition for Database Professionals
  364. Microsoft Assessment and Planning Solution Accelerator
  365. Aggregating Server Data from the MAPS Tool
  • Print
  • + Share This

In the last tutorial we completed our logical Entity Relationship Diagram (ERD) and began the process for creating a physical model from that logical diagram. We converted the entities to tables, and discussed the process of normalization as well as defining what a relationship is.

We also started the process of changing attributes to columns. The first thing we did was to take the names of the attributes and ensure that the concepts they represented were "atomic" enough — meaning that if an attribute expresses more than a single object, we must make more columns for those attributes. For instance, if we think of a "Person" object and include where they live, then we are expressing two thoughts for one object. We are connecting where the person lives to the person themselves – not a good idea. For one thing, the "Street Name" part of the address doesn't belong to the person, it belongs to the address. For another, more people might live at the same address, so it doesn't belong to the person at all — it stands alone and needs its own entity to describe it.

In our case, the attributes we've described are singular in nature and all we needed to do was change the names a bit. While SQL Server allows spaces in object names, you have to place brackets "[]" around the object name when we’re typing code, so we took them out.

Here is the diagram we have so far of our objects:

In this tutorial, we’ll take each table and convert the individual attributes to true columns, complete with keys and other constraints, data types, and more.

When we open the management tools (SQL Server 2000 here, SQL Server 2005 here) to create the columns, we’ll need a few pieces of information ready, such as whether or not the column is a key, what data type it is, whether it can contain a NULL value, and its default value.

SQL Server isn’t case-sensitive by default, but it’s always good practice to pick a methodology for naming conventions and stick with it throughout the enterprise. SQL Server can honor case, so be careful here!

As you can see, there are quite a few decisions to work out before you do the conversion. Let’s take our ERD and work through it a little at a time.

Starting with the Staff_Members table, let’s examine the attributes that it contains and convert them to columns.

We’ll need a primary key for this table, since it will be referenced by other tables. We’ll call this column Staff_Code.

I’m a big proponent of using a surrogate key. A surrogate key is a code or number which is independent of the meaning of any of the other attributes in the table. In contrast, a natural key is a column that is both unique and consistently available throughout the data domain, and is guaranteed to have a value at all times. One example of a natural key might be the Social Security number in the United States. It is supposed to be unique to one person, and in fact it is — when the person has one. First and last names certainly aren't always unique, and some people have more than one last name. Other numbering schemes and identifiers have similar problems. Since the natural key is hard to find, we’ll make up a standard way of numbering the record that identifies something or someone and stick with it.

If we’re going to use a code with alphanumeric values, we’ll have to use the varchar, char, or nvarchar data types. (If you need a refresher on data types, check my tutorial here.)

The other issue with using an alphanumeric value for the primary key is that we’ll need to create a value-generator in our code so that when we do inserts we create proper key values. Unless that code is well thought-out, we can quickly get into trouble here. Another argument against alphanumeric keys is that they don’t sort as well as numeric types.

A final issue with alphanumeric codes is that it becomes tempting to create a "database within a field" with these values. To imagine what I’m describing, think of a Work Breakdown Structure (WBS) code, used by many project management systems. In project databases, certain codes are used where the position and character has meaning. For instance, in the number 12345, the position of number 1 designates a certain company, the 2 means the department, the 3 is the project, the 4 is the employee’s code, and the 5 is the work type being performed. What we’ve got here is meaning within the characters of a field — a mini-database.

While this WBS code is usually unique, it doesn’t always work that way. Things change when the company is purchased, the client merges with another, the project stops and then re-starts, and more. I’m not saying you should never do this, but give it some thought before you do. We certainly don’t want our primary key to have this characteristic, because it isn’t guaranteed to stay constant. The only meaning that stays constant is something with no meaning to change to begin with, so the surrogate key is usually the way to go.

So, in our case we’ll just use a numeric data type. But which one? Recall from the Database Object tutorials that there are several number data types, each with its own use. My advice is to pick the smallest number type you can, but always err on the side of caution. That means that we’ll need to pick a small number type, but one large enough to encompass all possible values we can think we might need in this case.

The two candidates we might use are int and smallint. The int type covers numbers into the millions, and smallint values go to somewhere around thirty-two thousand. Since we don’t anticipate the number of staff members ever reaching into even the tens of thousands (even including using new numbers for new employees), smallint is the winner. The good part about this choice is that if we need the higher values later, we can always alter the column to have an int data type, and the values currently in that column of the table will convert properly.

Next we need to decide how the key will be generated. If the key uses an identity type qualifier, SQL Server will automatically choose the next number for us. While that is easy for the developer, there are times when it’s not a good choice (we’ll cover them in later tutorials). If we don’t use an identity value, we’ll have to generate the key in code, which causes the developer more work, and has the potential for error.

For this example, we’ll use the identity qualifier, since it will save us some coding. Even if we change our mind later, we can always turn off the identity value and create a numbering mechanism ourselves.

We also have to choose a "seed" for the identity, which is the value SQL Server uses to create the next value in an identity field. We’ll leave ours at 1, meaning that the numbering will start with 1 and move upward.

Normally we’d need to decide whether we would allow NULL values in the field, but for a primary key, we don’t have to make that decision. Primary keys must always have a value.

That may seem like a lot of work for just one field. We took our time with this one, but the rest of the primary keys in all our tables will work the same way.

Moving on to the rest of the field types, we have more choices. Since we’re describing the employees, we need to record their names. We’ll use a field called Name to hold those values. We might have broken out first and last names, but since the business requirements don’t really need the first and last names broken out, this choice is acceptable — but will the system ever change? It's something we need to consider. We'll leave it this way for this example.

The final issue we'll consider with using a single field for two parts of the name is indexing. If we provide a search function, the users will certainly want to search by last name. Depending on how many employees we have, we may in fact want to create the first and last field names, and then concatenate them in the program so that they look as if they are one field. After much discussion, we decide to keep the single field. (This choice will haunt us later.)

Any of the character types will work for this field, but we’ll choose nvarchar. We’ll use this type because the nvarchar uses two bits for each character; that allows us to use other language characters in the names, in case we have employees from other countries.

We’ll make the length large enough to cover just about any name we can think of. Fifty (50) characters should do.

Next we need to decide the "nullability" of this field. Should we be able to create an employee record with no name? We decide that we can’t think of a reason that this would occur, so we make this field required. No NULLs here.

An important decision when we force a field not to accept NULL values is that we should provide a default value for it. When insert operations take place from the program and we don’t provide a default value, the developer has to work harder to make sure the user enters a value.

If we are not sure about which value to enter as a default, we can use "UNKNOWN" or "NOT ENTERED." If we’re going to enter such an ambiguous value, why not just allow NULL values? The reason lies with the concept of NULL, which I’ve covered in other tutorials. Suffice it to say here that we should never compare a value against the value NULL. We can, however, compare things against the text "UNKNOWN" all day long.

After having said all that, we won’t provide a default for this field. We want the user to enter a name here, so the developer will have to provide a mechanism to ensure that they do.

In the diagram, we have a field called Years_on_staff. An important axiom to remember is that in an Online Transaction Processing (OTLP) database, we normally don’t want to store a computed value. Instead we should store the base data, and let the reporting systems decide how to show the data. We’ll change this field name to Employeement_date, make it a datetime type (of course) and make it a required field. The length is automatically determined by the datetype type. We’ll default it to today, using the GETDATE() function.

Moving on to the Skills table, we’ll repeat the process for the fields we find here. Remember that the Skills table is used to store the various skills that an employee has.

We need a primary key for this table, so we’ll use the Skill_Code column with the same setup as we did earlier.

This table is a child to another, so we’ll reference the Staff_code field in the Staff_Members table with the same one here. Recall that this process is called a foreign key, and we certainly don’t want this field to be unique. It does, however, need to be the same type and length as the primary key it references.

We’ll need a Name field to store the name of the skill. We’ll use a varchar type, and 30 characters should suffice. NULL’s are OK in this field. We’ll use the same logic for the Classification and Level fields. As you can see, the process goes quicker once you get started.

The Clients table is a parent table, and holds the information for all the clients to which projects belong. Again, we’ll make a Client_Code field that acts as the primary key, just as we have for the other tables.

We’ll follow a similar process for the Name and Primary_Address field that we have for the other varchar types. The Primary_Phone field deserves a bit more attention, though.

There are a few schools of thought regarding phone number fields. If we are going to have more than one phone number for the company (normally a good practice), then we are looking at many records to identify one company, or many phone fields in a record. Whenever that kind of situation arises, we need to add another table. To keep this tutorial manageable, we’ve opted not to do that, but you should be aware of it since that situation is a bit more realistic.

Another factor with phone numbers is the data type. Phone numbers are just numbers, right? Well, not always. Some phone numbers are used with special codes or characters. This requires that the field is a character type. Because we might also work with overseas customers and have to dial special codes to reach them, we’ll set this field to a varchar type with 30 characters. We’ll also allow it to contain NULLs.

The last field in this table is the Start_Date, with a data type of datetime. We’ll require a value here, and set a default of the SQL Server function GETDATE(). That function will grab the current date from the system and store it in the field automatically if the user doesn’t enter one, which is a good default to have for this kind of data. It saves the developer some work.

Normally during the design we wouldn’t spend so much time on the field definitions. The process usually involves the use of a table-like structure, which we’ll use now for the Projects and Hours tables. Let’s take a look at how that would lay out:

Projects

Field Name

Use

Type

Length/ Precision

Null

Default

Project_Code

Primary key

Smallint (identity)

N/A

No

N/A

Client_code

Foreign key to Clients table

Smallint

N/A

No

N/A

Name

Name of project

Varchar

50

No

"UNKNOWN"

Phase

Current phase of project

Varchar

30

No

"Initial"

Budget

Budgeted hours for project

Smallint

N/A

No

0

State

Active or inactive

Varchar

30

No

"UNKNOWN

Hours

Field Name

Use

Type

Length/ Precision

Null

Default

Hours_Code

Primary key

Smallint (identity)

N/A

No

N/A

Project_Code

Foreign key to Projects table

Smallint

N/A

No

N/A

Staff_Code

Foreign key to Staff_Member table

Smallint

N/A

No

N/A

Role

Role filled by staff member

Varchar

100

Yes

N/A

Start _Time

Start date and time

Datetime

N/A

No

GETDATE()

End_Time

End date and time

Datetime

N/A

Yes

N/A

Rate

Rate charged

Smallmoney

N/A

No

0.00

Description

Activity performed

Varchar

255

No

"DESCRIPTION"

You can also represent the data with a graphic representation like this one:

What we’ve learned so far is the process for creating the database design manually. In the next tutorial, we’ll implement this design using the SQL Server management tools.

We’ve been using manual methods to design our database. There are many software products that will help you automate the definition of the entities, designing the relationships, creating an ERD, and even creating physical database with graphical tools. These products include everything from Microsoft Visio all the way to full design tools such as ERWin and the Embarcadero suite of tools. Even if you invest in one or more of these useful tools, I still recommend you make your first few small database designs by hand using the methods we’ve discussed here.

In the next tutorial we’ll create the Data Definition Language (DDL) to make our database.

InformIT Articles and Sample Chapters

I’m not sure who the author of this site is, but the author has a good design exercise similar to ours, and this section discusses the process used to change their attributes to columns. Note — you’ll need a postscript reader to read the file; Visio can work for that if you have it.

Online Resources

David Besch has a good excerpt from his book called MCSE Training Guide: SQL Server 7 Database Design on implementing the physical design.

  • + Share This
  • 🔖 Save To Your Account

Related Resources

There are currently no related titles. Please check back later.