SQL Server Reference Guide

Database Design: Creating The Physical Database

Last updated Jan 1, 2004.

We're ready to convert our completed physical model into a SQL Server database. Before we begin, let's recap how we got to this point.

The original request for a database came from the business community. While this is a common way to start the process, it's also common for developers to ask for a database, or the DBA team leader might be in on the original request. Wherever the appeal comes from, it will normally come in the form of business requirements. When we get them, we take those requirements and re-write the paragraphs into single-thought sentences. From there, we break down the sentences into nouns and verbs, which give us our entities, attributes and relationships.

Next we confirm our design with the business community in a design review process, and then create a logical Entity Relationship Diagram (ERD) to graphically represent the data store. From there we refine the ERD to a final logical design.

With the logical ERD in hand, we convert the logical design into physical database objects. Entities become tables, attributes become columns, and relationships become foreign and primary keys. In our case, the final deliverable before we create the physical database is this design:

Figure 87

In the last tutorial, we learned that the ERD is only one format for presenting the design. We might also use a spreadsheet with the same information. Either way, it's important to have a clear, understandable format to communicate what is needed to the DBA who will implement the database.

We're now ready to use Enterprise Manager to create our database. Just after this section, we'll cover how we can create the same database using Data Definition Language (DDL) in Query Analyzer. We could also use code, or another program that has the ability to send DDL commands such as ERWin or some other design tool. There is no "best" way, as all of these lead to a finished database.

Enterprise Manager

With our ERD to one side, we open Enterprise Manager. Once we've done that, we drill down to the Databases object. Right-clicking that object and selecting the New Database... menu item brings up the following panel:

Figure 88

Here, we name the database ProjectTracking. The name is really immaterial, but like other objects, we should pick a convention for our company and stick with it.

On this panel are two additional tabs, Data Files and Transaction Log, on which we can set two important groups of options for both the database and the transaction log files. The first option allows us to set the physical file locations, and how many we'd like to have.

On this database, we don't anticipate that much storage, so we'll leave the defaults right where they are. In future tutorials about performance, we'll learn that it's normally best to separate the transaction log file device from the database, but since this is a small system we don't anticipate that need here.

The final option to contend with on this panel is growth control. We have three basic choices: we can set a specific size, set the files to grow by a percentage, or set them to grow by a certain megabyte amount. We'll leave ours at the defaults, but you can learn more about these options here.

We close this panel with the OK button and move on to the table objects. Once we do so, we're presented with this graphic:

Figure 89

Notice that we set our view to Taskpad to get this particular screen.

Whenever we create a new database, SQL Server uses the model system database as a template. We need to check and set a few more options, in case they aren't what is needed for this particular database. We right-click the database name, select Properties from the menu that appears, and then click the Options tab.

Figure 90

Here we set the options for this database to a simple recovery model, and un-check the auto close and auto shrink options. These choices are especially important for an N-tier style database, such as the one we're designing. Once we complete that process, we close out this panel.

We'll stay on the database objects and begin creating tables with the Staff_Members table.

First, we right-click the Tables item and select New Table... from the menu that appears. Once we're there, we enter the name of the primary key for this table, and set it to a smallint type, with the identity value set. Finally, we right-click the name of the column and select Set Primary Key from the menu that appears. You can see what we've done so far in this panel:

Figure 91

Once we do that, we create the other columns as specified in the ERD. Here's the panel that shows the completed table:

Figure 92

After we complete the table, we click the disk icon in the upper left corner of Enterprise Manager and name the table. Here we have the completed table in Enterprise Manager:

Figure 93

We're almost there; we'll create one more table to demonstrate the process of creating a foreign key.

We start the Skills table creation the same way, and create the columns and primary key just as we did a moment ago. The completed table looks like this:

Figure 94

Notice that we changed the Level column name to Skill_level, since "Level" is a keyword in SQL Server and would cause programming issues if we left it that way. Small changes like this are common, and don't require a trip back to the design committee to verify.

So far, this process is a carbon-copy of the one we just performed for the Staff_Members table, but here's where it gets interesting. We now want to tie the two tables together. The ERD we saw earlier shows that Staff_Members is the parent table to Skills. Recall that a foreign key is just a field in one table that points to the primary key in another. To do that, we right-click the Staff_code column in Skills, and select Relationships from the menu that appears.

Figure 95

Next we click the New button on that panel and set the primary key table and column in the referenced table and the foreign key table and column in the referencing table. With that little piece of work accomplished, we're all done with the relationship between the two tables.

What this means is that we have to enter data into the parent table first, and then we can enter data into the Skills table.

The process is the same for the rest of the tables, so next we'll discuss Query Analyzer and the text-based commands.

Query Analyzer

While Enterprise Manager is certainly easy to use, there are some advantages to using a command-line tool to create a database. For one, you can create a script to do the job that can be saved and then duplicated, say in a test environment.

The script for this process is located here:

Figure 96

Most of this script is SQL syntax, so I won't bore you with the full explanation of that here. The interesting parts of this script are the foreign key constraints. Let's take a look at a sample:

ALTER TABLE [dbo].[Skills] ADD 
 CONSTRAINT [FK_Skills_Staff_Members] FOREIGN KEY 
 (
  [Staff_code]
 ) REFERENCES [dbo].[Staff_Members] (
  [Staff_Code]
 )
GO

This snippet is interesting for two reasons. The first is that it illustrates the ALTER TABLE statement. This powerful statement lets you make changes to an object that's already in place.

Why not just create all parts of the table while we're at it? Because the ALTER TABLE statement lets us create the objects in any order, and then add the constraints afterwards. This can come in handy on several occasions.

Another thing that makes this script snippet interesting is that it shows the syntax for the foreign key creation:

ALTER TABLE ChildTableName ADD 
 CONSTRAINT NameofConstraint FOREIGN KEY 
 (
  ChildTableColumn
 ) REFERENCES ParentTableName (
  ParentTableColumn
 )
GO

Just like the primary key, a foreign key is a constraint. You can see that the syntax sets the "from" part first.

Recall from the last tutorial that it's handy to create the foreign key fields in the child table with the same name as the primary key field in the parent table. This makes the process of tying the relationship together using a graphical tool very intuitive. The only difference is the upper-case "C" in the Staff_Code primary key, with a small "c" in the foreign key. That makes it easy to remember which is which; the small letter helps me remember the proper syntax. You'll develop little tricks like this to help you keep things straight, but it's important to keep to a standard.

That completes our physical database – for now. In future articles we'll add views and stored procedures to the database to flesh out the database side of the programming.

Online Resources

Michael Aubert has another view on using Data Defintion Language to create database object in his article called SQL Server 2000 Administration in 15 Minutes a Week: More Database Creation Topics.

Nigel Rivett has a great script for creating object scripts using Data Management Objects (DMO) using T-SQL. It's a great resource if you're a developer!

InformIT Articles and Sample Chapters

Ken Henderson has the definitive rundown on Data Definition Language in his book, The Guru's Guide to Transact-SQL.