Posted on

weebe?s world seeds

Web Design Folsom, Web Design, Folsom.
Website Design Folsom is a go-to resource for web design, graphic design and branding in Folsom, CA.

Weebe?s world seeds

Wrongful Termination Lawyer, Lawyer, Los Angeles.
Robert Odell is a wrongful termination lawyer for employees representing Los Angeles clients in law suits against employers. Call for a free consultation.

Web Design Folsom, Web Design, Folsom.
Website Design Folsom is a go-to resource for web design, graphic design and branding in Folsom, CA.

Weinstein Retrofitting, Earthquake Retrofitting, Los Angeles.
The leading earthquake proofing company in the Los Angeles and the San Fernando Valley, CA area.

W San Diego, Hotel, San Diego.
W San Diego is located in the Columbia District of downtown San Diego, near Little Italy and just minutes from the vibrant Gaslamp Quarter. More than just a fabulous place to rest your head.

Table 7.5. The HelpDeskSubjects table

Creating Database Tables

Excerpt from Build Your Own ASP.NET 4 Website Using C# & VB, 4th Edition

Cristian Darie , Wyatt Barnett , and Tim Posey

Let’s launch into creating the tables for our intranet application. It’s helpful to think of tables as the drawers in a filing cabinet: just as we can separate different information into different drawers, we can break down information about employees, departments, and help desk requests into different tables. Tables can also be compared to spreadsheets, as they have rows and columns, but they have many other useful features. They know what kinds of data they’re allowed to store, can relate to data contained in other tables, and can be searched and manipulated with a very effective language called SQL (which you’ll learn about in Chapter 8).

You can organize the tables in your database using either Visual Web Developer or SQL Server Management Studio, depending on your preference. While SQL Server Management Studio is more powerful, both tools can be used for basic tasks such as creating database tables.

In just a minute, we’ll dive in and create our first table. Before we do, it’s worth giving some thought to how many tables our application will need, and exactly what they’ll contain. We can think of tables as lists of entities. Entities are the rows or records in our table. Drawing our tables and their entities on paper is a great way to plan the logical design of the database. The logical design shows what kinds of data our database will need to store, and outlines the relationships that we want to exist between specific pieces of data.

However, unlike a typical spreadsheet file, the tables defined in the logical design do not usually represent the way we’ll store the data in the database. This is taken care of in the physical design phase, in which we create a practical blueprint that allows us to improve database speed, enable relationships between different tables, or implement other advanced features—basically, to optimize our database in various ways.

Your database’s design has important consequences in terms of the way your application works, and how easy it is to extend, so it’s important to take the logical and physical design phases seriously. Let’s take a look at an example, so that you can see what this means in practice.

Let’s say that, in addition to a name, username, and telephone number, you wanted to keep track of the departments in which employees work at Dorknozzle. To do so, it may seem logical to simply add a column to the Employees table we discussed above; Figure 7.6 shows how this would look.

Figure 7.6. The Employees table

It looks good, right? Well, it’s okay in theory. However, if you went ahead and implemented this structure in your database, you’d likely end up in trouble, because this approach presents a couple of potential problems:

Every time you insert a new employee record, you’ll have to prov >update anomaly.

It would be natural for you to rely on your database to prov >my problem!), you’d remove any record that the department had ever existed (although, if you really did have to fire everyone, that might be a good thing … ). Database design experts call this a delete anomaly.

These problems—and more—can be dealt with very easily. Instead of storing the information for the departments in the Employees table, let’s create an entirely new table for our list of departments. Similarly to the Employees table, the new Departments table will include a column called Department ID, which will identify each of our departments with a unique number. We can use those department IDs in our Employees table to associate departments with employees. This new database layout is shown in Figure 7.7.

Figure 7.7. The Employees table with a new Department ID field

The Difference between Design and Implementation

As this example has shown, the way you’d naturally draw your database design on paper, and the best way to implement the design in practice, can be quite distinct. However, keep in mind that there are no absolute rules in database design, and expert database designers sometimes bend or break rules to meet the requirements of particular circumstances.

What these tables show are four employees and three departments. The Department >relationship between the two tables, indicating that Zak Ruvalcaba works in Department 1, while Kevin Yank and Craig Anderson work in Department 3. Notice also that, as each department appears only once in the database, and appears independently of the employees who work in it, we’ve avoided the potential problems outlined before.

However, the most important characteristic of this database design is that, since we’re storing information about two types of entities (employees and departments), we’re using two tables. This approach illustrates an important rule of thumb that we must keep in mind when designing databases:

Each type of entity about which we want to be able to store information should be given its own table.

With this rule in mind, we can sit back and think about the Dorknozzle application we want to build, as it was described in Chapter 5. We need to think of the design in terms of the entities that we want to track, and come up with a preliminary list of tables. You’ll become more comfortable with this kind of task as you gain experience in database design, but it’s worth giving it a try on your own at this stage. When you’re done, compare your list to the one below, and see how you did. Here are the entities we think we’ll want to track:

This table keeps track of our company’s employees, each of which is associated with a department.

This table lists the departments in our company.

Help Desk Problem Reports

This table stores the problem reports that have been filed at Dorknozzle’s employee help desk. A category, subject, and status will be associated with each problem report.

Help Desk Categories

The categories that are available for help desk items (“Hardware,” “Software,” and so on) are stored in this table.

Help Desk Subjects

The subjects that are available for help desk items (“Computer crashes,” “My chair is broken,” and the like) are stored in this table.

Help Desk States

This table stores the various states in which a help desk item can exist (“open” or “closed”).

Breaking down and analyzing the items of information that need to be saved is the first step in determining the database’s design—this process represents the logical design phase that I mentioned earlier. Through this process, we work to build a high-level definition of the data that needs to be saved. This definition can then be transformed into a physical design structure, which contains the details required to implement the database.

As you analyze the data that needs to be stored, you may come across items that we overlooked when we designed the site in Chapter 5, such as help desk item categories, subjects, and states, which aren’t obvious entities in our application’s current design. However, remember that whenever you predict that your database will contain a field that should only accept values from a specific list, it makes sense to create a table to hold that list. This approach makes it easy to execute changes to the list in future; it also reduces the amount of disk space required by your database, and helps you to avoid redundancy, as you store only single instances of department names, strings like “I can’t print,” and so on.

This process of planning out the entities, tables, and relationships between the tables to eliminate maintenance problems and redundant data is called database normalization. Although we’ll talk a bit more about normalization before the end of this chapter, we’ll only ever discuss it in an informal, hands-on way. As any computer science major will tell you, database design is a serious area of research, with tested and mathematically provable principles that, while useful, are beyond the scope of this book.

So, we have our list of tables. In the next section, we’ll look at the columns within those tables, and discuss how we can ascertain their characteristics. Although we won’t go over the creation of all the tables for the Dorknozzle database, we will create one as an example: the Employees table. Once you understand how to create a new table, you can create the rest of the tables for the Dorknozzle application in your own time, based on the descriptions we’ll provide. Or, if you prefer, you can simply grab the finished database from the code archive.

Once you’ve outlined all your tables, the next step is to decide what pieces of information will be included within those tables. For instance, you may want to include a first name, last name, phone number, address, city, state, zip code, and so on, for all employees in the Employees table. Let’s see how we can define these columns as we create the Employees table for the Dorknozzle database.

Data Types

One of the differences between logical design and physical design is that when we’re planning the database’s physical design, we have to deal with details such as data types. That’s right—as with the data we’re storing in our VB.NET and C# variables, the data we store in each table’s columns has a particular data type.

SQL Server knows many data types—in fact, it knows too many to list here—but it’s worth our while to take a look at the most common ones. Below is a list of the common data types that we’ll use in this book:

Use the int data type when you need to store whole integers. This data type can store numbers from -2,147,483,648 to 2,147,483,647.

Use the float data type when you’re working with very large numbers or very small numbers. float can be used for fractions, but they’re prone to rounding errors.

The money data type should be used to store monetary data, such as prices for a product catalog. This data type is closely related to the int data type.

Use the bit data type when a condition is either true (represented as 1 ) or false (represented as 0 ).

As you might have guessed, the datetime data type is used to store dates and times. It’s very useful when you want to sort items in your table chronologically.

The nvarchar data type stores strings of text. It’s the most commonly used data type because it stores names, descriptions, and the like. When we’re defining a column of this type, we also need to specify a maximum size in parentheses; longer strings will be trimmed to fit the defined size. For example, nvarchar(50) specifies a field that can hold up to 50 characters. The var part of the nvarchar name indicates that this data type can store strings of variable length up to the specified maximum.

The nchar data type is similar to nvarchar in that it stores strings, but a field of this type will always store strings of the defined size. If the string you’re saving is shorter, it’s padded with spaces until the specified size is reached. For example, if you’re working with an nchar(6) field (where the 6 in parentheses indicates that the field can hold six characters), and you add the word “test” to the field, two space characters will be appended to the end of the word so that all six characters are used. This data type is useful when you’re storing strings that have a predefined size—in such cases, it may be more efficient to use the nchar( n) type than nvarchar .

money , money , money

Sometimes, you may see poorly designed databases use float to store monetary data. As float is susceptible to rounding errors, this is a bad >money , on the other hand, is not susceptible to these errors and is a much better choice.

The SQL Server data types, as with the other SQL Server keywords, aren’t case-sensitive. nvarchar and nchar have non-Unicode cousins named varchar and char , which you can use if you’re sure you won’t need to store Unicode data. You may need to use Unicode (or a language-specific form of encoding) when storing non-English text, such as Chinese, Arabic, and others. Unicode is a very w >nvarchar and nchar .

The type of a column defines how that column behaves. For example, sorting data by a datetime column will cause the records to be sorted chronologically, rather than alphabetically or numerically.

Column Properties

Other than a column’s data type, we can define a number of additional properties for a column. Other properties you’ll use frequently include:

In database speak, NULL means “undefined.” Although we talk about it as if it’s a value, NULL actually represents the lack of a value. If you set an employee’s mobile telephone number to NULL , for example, it could indicate that the employee doesn’t have a mobile telephone.

However, it’s important to realize that allowing NULL s is often inappropriate. For instance, you might create a department with the name NULL to represent a mysterious department with no name, but this is far from >NULL , and which aren’t. In our example, we’d like every department to have a name, so we shouldn’t allow the Name column to allow NULL s.

SQL Server is capable of supplying a default value for a certain column if you don’t supply one when you add a new row. We won’t be using this feature when we create Dorknozzle, but it’s good to know you have this option.

>IDENTITY column, SQL Server will generate numbers automatically for that column as you add new rows to it. The first number in the column is called the >identity increment. By default, both the seed and increment have a value of 1, in which case the generated values are 1, 2, 3, and so on. If the identity seed were 5 and the identity increment were 10, the generated numbers would be 5, 15, 25, and so on.

IDENTITY is useful for >IDENTITY , the generated values will always be unique. By default, you can’t specify values manually for an IDENTITY column. Note also that the column can never contain NULL .

Understanding NULL

Be sure not to see NULL as equivalent to 0 (in numerical columns), or an empty string (in the case of string columns). Both 0 and an empty string are values; NULL defines the lack of a value.

NULL and Default Values

I’ve often heard people say that when we set a default value for a column, it doesn’t matter whether or not we set it to accept NULL s. Many people seem to believe that columns with default values won’t store NULL .

That’s incorrect. You can modify a record after it was created, and change any field that will allow it to NULL . Your columns’ ability to store NULL is important for the integrity of your data, and it should reflect the purpose of that data. A default value does make things easier when we create new rows, but it’s not as vital as is correctly allowing (or disallowing) NULL in columns.

Primary Keys

Primary keys are the last fundamental concept that you need to understand before you can create your first data table. In the world of relational databases, each row in a table must be >key, on which all database operations are based.

The tables in your databases could contain hundreds or even thousands of rows of similar data—you could have several hundred employees in your Employees table alone. Imagine that your program needs to update or delete the record for John Smith, and there are several people with that name in your organization. You couldn’t rely on the database to find the record for the particular John Smith that you were trying to work with—it might end up updating or deleting the wrong record.

We can avoid these kinds of problems only by using a system that uniquely identifies each row in the table. The first step toward achieving this goal is to add to the table an ID column that provides a unique identifier for each employee, as did the Employee ID column that we saw in Figure 7.1.

Remember that when we discussed this Employees table, we noted that you may be tempted to use each employee’s username to uniquely >cand >nvarchar field to uniquely identify rows in your table will bring your SQL Server to a grinding halt much faster than if you chose a standard, numeric key.

The column that we use to uniquely >primary key. In the case of our Employee table, the Employee ID will always be unique, so it would be a suitable primary key.

Multi-column Keys

To make the concept of keys easier to understand, we have kept the definition simple, although, technically, it’s not 100% correct. A key isn’t necessarily formed by a single column—it can be formed by two or more columns. If the key is made up of multiple columns, the set of values in those columns must be unique for any given record. We’ll see an example of such a key in a moment.

Although we usually refer to primary keys as if they were columns, technically they’re constraints that we apply to the existing columns of a table. Constraints impose restrictions on the data we can enter into our tables, and the primary key is a particular kind of constraint. When the primary key constraint is set on a column, the database will refuse to store duplicate values in that column.

Constraints in general, and primary keys in particular, represent a means by which the database can maintain the integrity and consistency of data.

Primary keys composed of a single column, such as Employee >IDENTITY property. The primary key constraint guarantees that duplicate values cannot be inserted into the table. The IDENTITY property helps us by always generating a new value that hasn’t already been used in the primary key.

Primary Keys and the IDENTITY Property

Using the IDENTITY property for a column doesn’t mean we can avo >IDENTITY property always generates unique values, but it doesn’t necessarily enforce them.

For example, say we have a table with a number of columns, one of which has the IDENTITY property set. This table contains three records that are likely to contain the automatically generated values 1 , 2 , and 3 in the IDENTITY column. Prov >IDENTITY_INSERT property for this table is enabled (by default it’s disabled, but it’s easy to enable), it’s quite simple to insert another record with the value 2 . The IDENTITY column will continue to generate unique values ( 4 , 5 , 6 , and so on), but it doesn’t guarantee the column remains unique.

Creating the Employees Table

In this section, we’ll show you how to use Visual Web Developer or SQL Server Management Studio, but to create a new data table. If you’re using Visual Web Developer, expand the database node in Database Explorer, right-click Tables , and select Add New Table , as shown in Figure 7.8.

Figure 7.8. Adding a new table in Visual Web Developer

If you prefer SQL Server Management Studio, you need to follow a similar procedure. Expand the Dorknozzle database node, right-click Tables , and select New Table… , as illustrated in Figure 7.9.

Figure 7.9. Adding a new table with SQL Server Management Studio

The window that appears as a result of either procedure is shown in Figure 7.10—it looks the same in both Visual Web Developer and SQL Server Management Studio. The main editing window lets you specify the column’s three main properties: Column Name , Data Type , and Allow Nulls . To set additional properties, you need to use the Column Properties pane.

Figure 7.10. Specifying column properties

To add the IDENTITY property to a column, locate the Identity Specification row in the Column Properties pane and expand it. This will reveal the (Is Identity) drop-down list, which should be set to Yes for an IDENTITY column, as Figure 7.10 indicates.

To set a column as the primary key, we can select Table Designer > Set Primary Key , or click the little golden key icon in the Table Designer toolbar when the column is selected. When a column is set as a primary key, a little golden key appears next to it, as Figure 7.11 illustrates.

Figure 7.11. The Employees table

Now, let’s create a table called Employees by adding the columns described in Table 7.1.

Table 7.1. The structure of the Employees table

Column Name SQL Data Type Identity Allow Nulls Primary Key
EmployeeID int Yes No Yes
DepartmentID int No No No
Name nvarchar(50) No No No
Username nvarchar(50) No No No
Password nvarchar(50) No Yes No
Address nvarchar(50) No Yes No
City nvarchar(50) No Yes No
State nvarchar(50) No Yes No
Zip nvarchar(50) No Yes No
HomePhone nvarchar(50) No Yes No
Extension nvarchar(50) No Yes No
MobilePhone nvarchar(50) No Yes No

After you enter this information, press Ctrl + S to save the table. When you’re asked to name the table, type Employees and click OK . When you’re done, your table will resemble Figure 7.11.

After you create the table, you’ll see it appear under the Tables node in the Object Explorer , in SQL Server Management, or Database Explorer in Visual Web Developer. SQL Server Management Studio prepends dbo. to the table’s name; dbo is the default “database owner” user. Don’t worry about this for now—we’ll explore the topic of database users in some detail later.

If you close the table designer window, you can open it later by right-clicking the Employees table and selecting Open Table Definition in Visual Web Developer, or Modify in SQL Server Management Studio. You’ll be taken back to the screen that shows the structure of the table (shown in Figure 7.11).

Creating the Remaining Tables

Let’s create the rest of the database tables. Apply the process you used to build the Employee table to create the new data tables, using the data presented in Table 7.2 to Table 7.6. Later in this chapter, we’ll discuss how these tables work. For starters, though, you need to insert them into your database.

Table 7.2. The Departments table

Column Name SQL Data Type Identity Allow Null Primary Key
DepartmentID int Yes No Yes
Department nvarchar(50) No No No

Table 7.3. The HelpDesk table

Column Name SQL Data Type Identity Allow Null Primary Key
RequestID int Yes No Yes
EmployeeID int No No No
StationNumber int No Yes No
CategoryID int No No No
SubjectID int No No No
Description nvarchar(50) No Yes No
StatusID int No No No

Table 7.4. The HelpDeskCategories table

Column Name SQL Data Type Identity Allow Null Primary Key
CategoryID int Yes No Yes
Category nvarchar(50) No No No

Table 7.5. The HelpDeskSubjects table

Column Name SQL Data Type Identity Allow Null Primary Key
SubjectID int Yes No Yes
Subject nvarchar(50) No No No

Table 7.6. The HelpDeskStatus table

Column Name SQL Data Type Identity Allow Null Primary Key
StatusID int Yes No Yes
Status nvarchar(50) No No No

Using SQL Scripts

Yes, there’s a lot of data to type in! Whilst we recommend that you create the tables yourself by defining the fields outlined here, you can achieve the same goal using an SQL script that’s included in this book’s code archive. This script contains SQL code that SQL Server understands, and has instructions that create data structures (you’ll learn about SQL in Chapter 8). If you want to use the downloadable script, we recommend you have a look over the following tables to get an idea of the structures we’ll be creating, then read the section called “Executing SQL Scripts” that follows.

We already have a clear >Employees and Departments tables. The other tables will be used to store help desk requests; we’ll discuss these in more detail in the following pages.

Executing SQL Scripts

If you prefer not to create the data tables manually, you can use the CreateTables.sql script included in the book’s code archive to create the tables for you. This script is most easily used with SQL Server Management Studio. After you log in, click the New Query button on the toolbar (or select File > New > Query with Current Connection ). Paste the contents of the CreateTables.sql script into the window that displays, and press F5 to execute the commands. Note that if you have already created the Employees table, you should remove the CREATE TABLE command that creates this table before you hit F5 .

The SQL script included in the code archive contains all the commands required for this chapter; it even creates the sample data and table references that we’ll cover later.

Populating the Data Tables

If tables represent drawers in a filing cabinet, rows represent individual paper records in those drawers. Let’s imagine for a moment that our intranet web application is a real application. As people begin to register and interact with the application, rows are created within the various tables, and are filled up with information about those people.

Once the data structures are in place, adding rows of data is as easy as typing information into the cells in the Datasheet View of a table, which looks a bit like a spreadsheet. To access it, right-click on the table and select Show Table Data in Visual Web Developer, or Open Table in SQL Server Management Studio. You can use the dialog that opens to start adding data. Let’s add some sample data to the tables you’ve just created, so that we can test the Dorknozzle database as we develop the application. Table 7.7 to Table 7.11 represent the tables and data you should add.

Inserting Data and Identity Columns

If you correctly set the ID column as an identity column, you won’t be allowed to specify the values manually—the ID values will be generated for you automatically. You need to be careful, because an ID value will never be generated twice on the same table. So even if you delete all the rows in a table, the database will not generate an ID with the value of 1; instead, it will continue creating new values from the last value that was generated for you.

Keep in mind that a new row is saved to the database at the moment that you move on to the next row. It’s very important that you remember this when you reach the last row, as you’ll need to move to an empty row even if you aren’t adding any more records.