Build Your Own ASP.NET 3.5 Website Using C# & VB (50 page)

useful, are beyond the scope of this book. If you want more information on the

topic, stop by DataModel.org for a list of good books, as well as several useful re-

sources on the subject.1
In particular
, check out the Rules of Normalization in the

Data Modeling section of the site.
2

So, we’ve got 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.

1 http://www.datamodel.org/

2 http://www.datamodel.org/NormalizationRules.html

Licensed to [email protected]

Database Design and Development

275

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:

int

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.

float

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.

money

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.

bit

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

(represented as 0).

datetime

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.

nvarchar(
n
)

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

Licensed to [email protected]

276

Build Your Own ASP.NET 3.5 Web Site Using C# & VB

name comes from the fact that this data type can store strings of
variable
length

up to the specified maximum.

nchar(
n
)

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 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 idea. 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 casesensitive. 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 nonEnglish text, such as Chinese, Arabic, and others. Unicode is a very widely supported standard, so it’s strongly recommended you stick with 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:

NULL

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

Licensed to [email protected]

Database Design and Development

277

mobile telephone number to NULL, for example, it could represent the fact that

the employee doesn’t have a mobile telephone.

However, it’s important to realize that allowing NULLs is often inappropriate.

For instance, you might create a department with the name NULL to represent a

mysterious department with no name, but obviously, this is far from ideal. As

you create a table, you can specify which columns are allowed to store 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 NULLs.

DEFAULT

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

Identity columns are numbered automatically. If you set a column as an 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

seed
. To generate subsequent numbers, the identity column adds a given value

to the seed; the value that’s added 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 ID columns, such as Department ID, for which you don’t

care what the values are, as long as they’re unique. When you use 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.

Licensed to [email protected]

278

Build Your Own ASP.NET 3.5 Web Site Using C# & VB