Storing Structured Data

Recall from our earlier definition that a database is a collection of structured information that can be efficiently accessed and modified. Databases structure their data by storing it into tables. A table is a combination of columns and rows in the form of a two-dimensional grid. (Sometimes a table's columns are referred to as fields; in this book, we'll use the word columns exclusively; we'll also refer to a table's rows as records.) Each column corresponds to an attribute of the data, whereas each row corresponds to an actual data item. Furthermore, each table is assigned a unique name to differentiate it from other tables in the database.

To clarify this concept, imagine that we wanted to use a database to store information about customers. Because we plan to store customer information in this database table, let's name the table Customers. To decide how to store the customer information, we must first decide what information, specifically, describes a customer. For this example, assume that we need to store the customer's name, phone number, and ZIP code, as well as the date the customer made his first purchase from our fictitious company.

These customer attributes make up the columns of the Customers table. Figure 13.1 shows a graphical representation of a table designed to store information about customers.

Figure 13.1. The Customers table's columns represent attributes of the customer.

Now, imagine that our company has five customers (it's amazing that it stays in business!). These five customers and their associated data might be

• Jisun Lee, 858-321-1234, 92109, January 27, 2001.

• Dave Yates, 619-123-4321, 92101, October 10, 2000.

• Todd Callister, 630-555-9898, 60126, August 27, 1989.

• Marie Vogan, 314-555-1111, 65401, September 8, 1997.

• Kate Wiseman, 858-555-4343, 92108, November 24, 2000.

Each of these five customers would be represented by one record in the Customers table. Figure 13.2 graphically represents the Customers table after these five records have been added to the table.

Figure 13.2. The Customers table contains five records, one row for each customer record.

Name	Phone	ZipCode	DateBecameCustomer
Jisun Lee	858-321-1234	92109	January 27, 2001
Dave Yates	619-123-4321	92101	October 10, 2000
Todd Callister	630-555-9898	60126	August 27, 1989
Marie Vogan	314-555-1111	65401	September 8, 1997
Kate Wiseman	858-555-4343	92108	November 24, 2000

Examining a Table's Columns

The columns of a database table, like variables in Visual Basic, have a name and a type. In our Customers table example, the names of the four database columns might be Name, Phone, ZipCode, and DateBecameCustomer. In addition to their names, these columns each have a type, which specifies the type of data that can be stored in the column.

For example, the Name column would likely have a type of nvarchar(50); the Phone column, a type of nvarchar(12); the ZipCode column, a type of nvarchar(5); and the DateBecameCustomer column, a type of datetime. Despite the type name differences, table columns can have types quite similar to the types that Visual Basic variables can have. That is, the type nvarchar(50) is akin to a String type in Visual Basic, where the string can have, at most, 50 characters. The datetime table column type is synonymous with Visual Basic's DateTime type.

In addition to the nvarchar(n) and datetime types, there are a number of other types. Table 13.1 summarizes some of the more common table column types and their Visual Basic parallels.

Table 13.1. Commonly Used Table Column Types

Table Column Type	Description	Visual Basic Parallel
nvarchar(n)	A string of up to n characters in length	String
int	An integer	Integer
bit	Can be 0 or 1a Boolean	Boolean
datetime	A date and time	DateTime
money	A monetary value	Decimal
float	A floating-point number	Single

When we create a database table later in this hour, you will see that there are many more column types than those listed in Table 13.1. However, the types presented in Table 13.1 are the ones you'll find yourself using the most of the time. You can find a complete list of SQL Server 2005's data types at http://msdn.microsoft.com/library/en-us/tsqlref/ts_da-db_7msw.asp.

By the Way

When you are creating a database table, you may notice that in addition to the nvarchar data type, there are similarly named data types nchar, varchar, and char.

The difference between nvarchar and nchar and varchar and char is that the types with var in the name are variable length character columns. That is, a nvarchar(50) column can have up to 50 characters but will take up only as many as are used. A nchar(50) or char(50) typed column, on the other hand, will always take up 50 characters, regardless of whether the value stored there is one character long or 50.

The difference between the data types beginning with n (nvarchar and nchar) and those that don't (varchar and char) is that those prefixed with n store Unicode characters. Unicode is a character set that allows for a greater range of characters to be stored, enabling columns of this flavor to be able to store characters in any alphabet. Varchar and char data types, on the other hand, have a much smaller character set and are limited to the standard English alphabet.

Although each of the string data types has a time and place, the nvarchar data type is the one most universally used and is what we'll be using throughout this book to store string data in a table.

Regardless of the data type being used, database columns can store a special value referred to as Null. The Null value indicates an "unknown" value. For example, imagine that you had a database table that captured information about customers, such as their name, payment information, shipping address, and so on. If, when entering the customer's information, you didn't know her phone number, you could use a value of Null.

When defining a column, in addition to its name and data type, you can also specify whether the column can be set to the Null value. In some circumstances you might not want to allow an "unknown" value to be entered, in which case you can mark the column to not allow Nulls.

Primary Key Columns

In addition to the various table columns that store pertinent data, database tables often contain a primary key column in addition to their other columns. A primary key column is typically a column of type int that has some special flags set. (We'll see how, specifically, to add a primary key column to a database table in the "Creating Database Tables" section.) A column that is marked as a primary key requires that each record in the table have a unique value for that column. For this reason, primary key columns are often given the name TableName ID.

Primary key columns of type int can be marked as Auto-increment columns. An Auto-increment primary key column automatically assigns each record in the table with an increasing integer value. That is, when you add a new record to the table, you can't specify the value for the Auto-incrementing column; it will automatically be given the next sequentially increasing number.

To make sense of this information, let's return to our Customers table example and add a primary key. Because primary key columns are usually named TableName ID, let's call the Customers primary key CustomerID. With the addition of this new column, the Customers table would have the structure shown in Figure 13.3.

Figure 13.3. A primary key column has been added to the Customers table.

Now, if we were to insert the five records examined earlier, the table's rows would have the data shown in Figure 13.4. Note that the value in the Auto-increment primary key column is unique and increasing for each row in the Customers table. Furthermore, realize that when inserting data, we would not specify the value for the CustomerID column; rather, the database system would automatically do this for us.

Figure 13.4. Each row contains a unique CustomerID value.