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

Database Design and Development

303

Figure 7.28. The properties of a foreign key

Advanced Foreign Key Options

Unless you really know what you’re doing, we recommend that you use the default

foreign key options for now. However, it’s good to have some idea of the features

available through the Properties window, as they may well come in handy later

in your database development career.

The most significant setting here is
Enforce Foreign Key Constraint
, which, when set

to
Yes
, prevents users or applications from entering inconsistent data into our

database (for example, by inserting into the Employees table a DepartmentID value

that doesn’t have a matching entry in the Departments table). In our application,

every user must be associated with a valid department, so we’ll leave this option

enabled.

The options available under
INSERT And UPDATE Specification
can be used to tell your

database to update the tables itself in order to keep the data valid at times when a

change in a given table would affect a related table. If, for some reason, we changed

the ID of a department in the Departments table, we could set the database to

propagate this change to all the tables related to that department, keeping the relationships intact. Similarly, we can set the database to automatically delete all the employees related to a department that’s deleted. However, these are quite sensitive

options, and it’s best to avoid them unless you have good reason not to. The cases

in which an ID changes are very uncommon (the ID doesn’t have any special

meaning itself, other than being an unique identifier), and letting the database delete

data for you is a risky approach (it’s safer to delete the related records yourself).

Licensed to [email protected]

304

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

If these concepts sound a bit advanced at the moment, don’t worry: it will all become

clear as you spend some time working with databases.

Many-to-many Relationships

Many-to-many relationships occur between two tables, when records from either

table can be associated with multiple records in the other table.

Imagine that you wanted a single employee to be able to belong to more than one

department—someone who works in “Engineering” could also be an “Executive,”

for example.
One
employee can belong to
many
departments, and
one
department can contain
many
employees, so this is a many-to-many relationship.

How do we represent it in our database? Faced with this question, many less-experienced developers begin to think of ways to store several values in a single column, because the obvious solution is to change the DepartmentID column in the Employees

table so that it contains a list of the IDs of those departments to which each employee

belongs. One those good old rules of thumb we discussed previously applies here:

If you need to store multiple values in a single column, your design is probably

flawed.

The correct way to represent a many-to-many relationship is to add a third table,

named a
mapping table
, to the database. A mapping table is a table that contains

no data other than the definitions of the pairs of entries that are related.
Figure 7.29

shows the database design for our employees and departments.

Figure 7.29. Using a mapping table to implement a many-to-many relationship

Licensed to [email protected]

Database Design and Development

305

The EmployeeDepartment table associates employee IDs with department IDs. If we

added this table to our database, we could add Zak Ruvalcaba to both the “Executive”

and “Engineering” departments.

A mapping table is created in much the same way as any other table. The only difference lies in the choice of the primary key. Every table we’ve created so far has had a column named
something
ID that was designed to be that table’s primary key.

Designating a column as a primary key tells the database not to allow two entries

in that column to have the same value. It also speeds up database searches based

on that column.

In the case of a mapping table, there’s no single column that we want to force to

have unique values. Each employee ID may appear more than once, as an employee

may belong to more than one department, and each department ID may appear more

than once, as a department may contain many employees. What we
don’t
want to

allow is the same
pair
of values to appear in the table twice (it wouldn’t make sense to associate a particular employee with a particular department more than once).

For this reason, we usually create mapping tables with a multi-column primary key.

In this example, the primary key for the EmployeeDepartment table would consist

of the EmployeeID and DepartmentID columns. This enforces the uniqueness that

is appropriate to a look-up table, and prevents a particular employee from being

assigned to a particular department more than once.

If you’d like to learn more about many-to-many relationships, or about anything

else related to SQL Server programming, I recommend you download and use the

product’s excellent documentation,
SQL Server Books Online.3

Summary

This chapter has introduced the fundamental concepts of relational databases. You

learned about the underlying structure of a modern relational database, which is

composed of tables, columns, and rows, and about crucial concepts that can aid in

database performance, maintenance, and efficiency. You’ve also learned how to

implement and enforce table relationships, and you have a solid understanding of

good relational database design.

3 http://msdn2.microsoft.com/en-us/library/ms130214.aspx

Licensed to [email protected]

306

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

Chapter 8 goes beyond data storage and introduces you to the language used to access

and manipulate the data you hold in your tables. That language is the Structured

Query Language, or SQL.

Licensed to [email protected]

Speaking SQL

So your database has been created, and you’ve defined all of the tables you’ll need,

and all of the columns for your tables—you’ve even defined the relationships

between your tables. The question now is, “How will you get to that data?” Sure,

you can open the database, look at the data contained in the tables, and manually

insert and delete records, but that does little to help your web users to interact with

that data. Mary in Accounting isn’t going to want to download and learn to use SQL

Server Management Studio just so she can retrieve an employee’s mobile phone

number—this functionality has to be provided by the Dorknozzle intranet web site,

which, after all, is supposed to enable staff members to access data easily. In fact,

the functionality can be created using web forms, web controls, a little code, and a

useful database programming language known as Structured Query Language (or

SQL).

SQL has its origins in a language developed by IBM in the 1970s called SEQUEL

(which stood for Structured English QUEry Language), and is still often referred to

as “sequel” or “ess-que-el.” It represents a very powerful way of interacting with

current database technologies and the tables that constitute our databases. SQL has

roughly 30 keywords and is the language of choice for simple and complex database

Licensed to [email protected]

308

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

operations alike. The queries you’ll construct with these keywords range from the

very simple to extremely complex strings of subqueries and table joins.

SQL is an international standard, and almost all database products, including SQL

Server, Oracle, DB2, and so on, support the standard to a certain degree. The dialect

of SQL supported by SQL Server is named Transact-SQL (or T-SQL). This chapter

cannot begin to cover all there is to know on the subject, but we hope it will provide

you with an introduction to beginning and advanced SQL concepts.

In this chapter, you’ll learn:

■ the basic SQL commands

■ the expressions that SQL supports

■ the most important SQL functions

■ how to perform table joins and subqueries

■ how to create stored procedures

This may sound like a lot of work, but you’re certain to enjoy it! Let’s get started.

Reading Data from a Single Table

Information that’s contained within a database is useless unless we have a way to

extract it. SQL is that mechanism; it allows quick but sophisticated access to database

data through the use of
queries
. Queries pose questions to the database server, which

returns the answer to your application.

Table 8.1. Sample contents from the Employees table

EmployeeID

Dep'tID

Name

Username

City

(Primary Key)

1

5

Zak Ruvalcaba

zak

San Diego

2

9

Jessica Ruvalcaba

jessica

San Diego

3

6

Ted Lindsey

ted

San Diego

4

6

Shane Weebe

shane

San Diego

5

9

David Levinson

david

San Diego

6

1

Geoff Kim

geoff

San Diego

Licensed to [email protected]