Creating the Database Structure

Most web applications use a database in some manner, and our photo album application is no exception. The precise structure of an application's databasewhat tables it consists of and the columns in those tablesdepends precisely on the design requirements. The second task in the design phase is taking the requirements collected in the first step and translating them into the structure of the database. What tables and columns will we need to create to support the data needs of the photo album application?

When you're devising the database structure, it often helps to refer back to the use cases. Typically, the nouns represent the tables that need to be created. For example, the "Supporting User Accounts" use case started with the sentence "Jisun wants to create a user account for our online photo album application." This sentence implies that we will need some way to represent a user account in the database.

By the Way

For user accounts, the precise set of tables and their columns is already defined for us by ASP.NET's membership feature, which was discussed in Hour 20, "Managing Your Site's Users." Therefore, we won't need to spend any more time defining the database requirements for user accounts.

In addition to the user account-related tables, we'll also need three additional tables:

• Categories Because users can organize their albums using categories, we need a table that captures the categories created by each user.

• Pictures As we will see in Hour 24, "Uploading and Displaying Images and Leaving Comments," when a user uploads a new picture to his album, the image file will actually be stored on the web server's file system; however, the meta-data about the pictureits title, description, and what category it belongs towill reside in a database. This table will have one record for each uploaded picture in the system.

• Comments Because users can leave comments for others' pictures, we need a table that stores these comments.

In the database examples we've examined thus far, we've focused on single table databases. However, most databases are composed of multiple, related tables. Two tables are said to be related if the data in one table is somehow dependent on the data in another table. For example, in the picture album data model, the Comments table's data is related to the Picture table's data because comments are left for a particular picture. There's a similar relationship between the Pictures and Categories tables.

Databases like SQL Server 2005 provide tools for enforcing relationships between tables. In the next section we'll examine some of these tools and their importance. Following that, we'll look at the individual table structure for each of the tables in the database.

Modeling Relationships in a Database

A good database design models each logical entity in the system as a table. In our photo album application, the entities in the system are categories, users, pictures, and comments; not surprisingly, our database will have a Categories, Pictures, and Comments table, along with the ASP.NET-defined tables for user accounts. Often, the entities in a system are related to one another in one of three ways:

• A one-to-one relationship

• A one-to-many relationship

• A many-to-many relationship

These relationships differ both conceptually and in implementation details. While the photo album application consists of only one-to-many relationships, let's take a moment to examine each of these three relationship types.

Examining One-to-One Relationships

If two tables share a one-to-one relationship, then for each record in the first table, there is at most one record in the corresponding table. A concrete example of this type of relationship can be seen between the ASP.NET-defined membership tables aspnet_Users and aspnet_Membership. The aspnet_Users table contains the UserId and Username values for each user account in the system. The aspnet_Membership table contains more detailed information about each account, including the user's password, email address, and security question and answer. As Figure 22.1 illustrates, for each record in aspnet_User there is precisely one matching record in aspnet_Membership.

Figure 22.1. The aspnet_Users and aspnet_Membership tables share a one-to-one relationship.

As Figure 22.1 shows, a relationship is set up by having the two tables share the same set of primary key columns. Recall that the primary key columns are the columns of a table whose values uniquely identify each row. Therefore, a given record in aspnet_Users can be associated with its related row in aspnet_Membership by finding the record in aspnet_Membership that has the same primary key column values.

By the Way

Although Figure 22.1 shows the UserId column as a column with an integer data type, in actuality this column uses the uniqueidentifier data type. The uniqueidentifier data type is a very, very large number that is computed in such a way that it is guaranteed to be globally and temporally unique. That means that the uniqueidentifier chosen to uniquely identify a user account on your web server has never been used by any computer system in the past and will never be used by any computer system in the future.

Looking at One-to-Many Relationships

A one-to-many relationship (see Figure 22.2) occurs when a given record in one table can have zero to many related records in another table. Our photo album application has a number of such relationships. The Pictures table has a one-to-many relationship with the Comments table because each picture may have zero to many comments. Likewise, the aspnet_Users table has a one-to-many relationship with Pictures since each user may have zero to many pictures in the system.

Figure 22.2. The Pictures and Comments tables share a one-to-many relationship.

By the Way

The only type of relationship that is needed in the photo album application database is the one-to-many relationship. As we will see shortly, there are five such one-to-many relationships in our database.

A one-to-many relationship is implemented by having the "many" table include the columns that make up the "one" table's primary key. In Figure 22.2, the Pictures table's primary key column is PictureID; in the Comments table there is a PictureID column. This associates a particular comment with a particular picture. This design allows for a single picture to have multiple comments.

In Figure 22.2, two comments are associated with "The Ocean" picture, while one comment is associated with each the "Hawaii Shores" and "My pet dog" pictures. The "A very drunk Ed!" and "It's Dave!!" pictures have no comments associated with them.

By the Way

Note that the "many" table has its own primary key, just like the "one" table. In Figure 22.2, the Comments table's primary key column is CommentID. PictureID is just a normal column in Comments; it's what ties a record in the Comments table back to a record in the Pictures table.

Understanding Many-to-Many Relationships

The last type of relationship, many-to-many, occurs when there are two tables and the tables' records may be cross-related in various combinations. The canonical many-to-many relationship is that between students and classes. A class may consist of several students, and each student may take several classes. Many-to-many relationships are implemented using a third table, one that associates each record from each table with a set of records from the other table, as shown in Figure 22.3.

Figure 22.3. A many-to-many relationship is implemented using three tables.

The third tableClassesStudents in Figure 22.3contains the primary key columns from both of the "many" tables. Furthermore, the primary key columns in the third table are made the table's primary key.

A many-to-many relationship can be envisioned as two one-to-many relationships. For example, using Figure 22.3 you can easily construct a class's enrollment, which is a one-to-many relationship between the Classes and Students tables; similarly, you can also construct a student's schedule, which is a one-to-many relationship between the Students and Classes tables.

Enforcing Relationships with Foreign Key Constraints

The relationships between tables are encoded in the columns of the tables. In a one-to-many relationship, for example, the primary key columns of the "one" table are added as columns to the "many" table. While this enables a one-to-many relationship to exist between these two tables, it doesn't enforce referential integrity. Without care, the data in two related tables may become inconsistent. For example, in Figure 22.2 imagine that we changed the value of the PictureID column for the first comment from 1 to 100. After this modification, the first comment no longer is associated with a valid picture. Similarly, if we were to delete the first picture from the database, we'd still have comments in the Comments table that referred to the first picture.

Referential integrity ensures that the relationship between tables cannot become inconsistent; this integrity is implemented through the use of foreign key constraints. A foreign key constraint associates a column in one table with a primary key in another table. Once this constraint has been set up, whenever a new record is added or the foreign key column is updated, the value of the foreign key column must map to a value in the primary key table. If you attempt to insert a new record or change the foreign key column value to a value not in the primary key column, an error will occur and the change will not be accepted.

If you attempt to delete or change the associated primary column's value while there are foreign key values that map to this primary key column value, one of four things can happen, depending on how the foreign key constraint is configured:

• An error is raised and the deletion or modification of the primary key column value is rejected. This is the default behavior.

• The primary key column value is deleted or changed, and all corresponding records in the foreign key table are deleted. This behavior is referred to as cascading deletes.

• The primary key column value is deleted or changed, and the foreign key column values in the foreign key table are set to a value of Null. (For this option, the foreign key column must allow Nulls.)

• The primary key column value is deleted or changed, and the foreign key column values in the foreign key table are set to their default value. (For this option, the foreign key column must have a default value specified.)

To help hammer home this concept, let's look at a concrete example. In Figure 22.2 the Pictures and Comments tables were related with a one-to-many relationship. The PictureID column in the Comments table is a foreign key because its values should always map to the values of the primary key PictureID in the Pictures table. We would want to create a foreign key constraint between these two columns. After we did so, attempting to change the PictureID value in the Comments table to a nonexistent PictureID would fail. If we configured the foreign key constraint to cascade delete, deleting a picture would automatically delete the related comments.

When creating a database table through Visual Web Developer, you can indicate any foreign key constraints that exist along with the behavior when an associated primary key column value is changed or the record deleted. We'll see how to accomplish this when we look at how to create the database and tables.

Enumerating the One-to-Many Relationships in the Photo Album Application Database

Before we start creating the database and tables, let's first take a moment to list the one-to-many relationships that exist in our database. As mentioned previously, there are five such relationships:

• aspnet_Users to Categories Because each user can create her own set of categories, each category in the Categories table needs to be associated with a particular user account.

• aspnet_Users to Pictures Each picture uploaded to the system needs to be associated with a user account.

• Categories to Pictures This application's requirements dictate that users should be able to categorize their photos into categories. Therefore, we need to associate each picture with a category.

• Pictures to Comments Each picture can have an arbitrary number of comments.

• aspnet_Users to Comments Each comment is associated with a particular user account.

The relationships among the tables of a database are often modeled using an entity-relationship diagram, also referred to as ER diagrams. ER diagrams show the entities in the database system (a fancy name for a database table), with lines between the tables highlighting the relationships. The ER diagram in Figure 22.4 shows the entities and relationships for the photo album application database.

Figure 22.4. The five one-to-many relationships are shown in the database's ER diagram.

Did you Know?

You can create ER diagrams through Visual Web Developer. From the Database Explorer, expand the Data Connections node and drill down into your database. Right-click on the Database Diagrams node and choose Add New Diagram. You will then be prompted for what tables you want to add to the diagram, after which the diagram will automatically be created. The diagram in Figure 22.4 was created in this manner.

Creating the User Account Tables

Before we can create the individual database tables for the photo album application database, we first need to create the actual database. In Hour 13, "An Introduction to Databases," we saw how to create a new SQL Server 2005 Express Edition database in the App_Data folder of our website. As we saw in Hour 20, "Managing Your Site's Users," when we configure an ASP.NET website to use user accounts, the ASPNETDB database is automatically created and placed in the App_Data folder. Rather than creating our own database, we'll instead use this autogenerated database. That is, the Categories, Pictures, and Comments tables will be added to ASPNETDB.

As we discussed in Hour 20, configuring the website to support user accounts involves launching the ASP.NET Website Administration Tool and changing the Security settings so that the authentication type used is for users coming over the Internet. Doing so creates the ASPNETDB database in our website's App_Data folder.

If you are following along, take a moment to complete these steps so that your website now has the appropriate user account-related database tables. Refer to Hour 20, if needed, for instructions on this process.

Creating the Categories Table

With the ASPNETDB database created, we're ready to start adding our tables unique to our photo album application. The first table we'll add is the Categories table, which will have one record for each category created by each user in the system. To add a table to the ASPNETDB database, go to the Database Explorer and drill down to the ASPNETDB database. Next, right-click on the Tables node and choose Add New Table.

The Categories table needs to capture the following information:

• A way to uniquely identify each category

• What user this category belongs to

• The name of the category

This translates into three columns:

• CategoryID An auto-increment primary key column of type int, which uniquely identifies each category.

• UserId A column of type uniqueidentifier that relates a category back to a user. This column must be of type uniqueidentifier because that's the type used to uniquely identify each account in the aspnet_Users table.

• Name A column of type nvarchar(50), used to store the name of the category.

None of these columns should allow Nulls. Recall that to mark a column as a primary key, you select the column and click the primary key icon in the Toolbar; to make a column an auto-increment, set the Identity Specification section in the Column Properties to Yes. Refer to Hour 13 for more information on these processes.

Figure 22.5 shows Visual Web Developer after this table's structure has been defined.

Figure 22.5. The Categories table has three columns.

Adding the Foreign Key Constraint

The aspnet_Users table has a one-to-many relationship with the Categories table. If we want to help protect the integrity and consistency of our database, it behooves us to add a foreign key constraint to ensure that the UserId values in Categories map to existing users. To accomplish this in Visual Web Developer, go to the Table Designer menu and choose Relationships. This will display the Foreign Key Relationships dialog box. Click the Add button to add a new foreign key relationship (see Figure 22.6).

Figure 22.6. Configure a table's foreign key constraints through the Foreign Key Relationships dialog box.

In the foreign key constraint properties on the right, a Table and Columns Specifications property includes a pair of ellipses. Clicking on the ellipses displays the Table and Columns dialog box, where you can indicate the primary key and foreign key columns participating in the constraint.

For this particular foreign key constraint, the primary key is the UserId column in the aspnet_Users table, while the foreign key is the UserId column in the Categories table. Make these selections (as shown in Figure 22.7) and click the OK button.

Figure 22.7. Map the Categories table's UserId column to the aspnet_Users table's UserId column.

After adding the foreign key constraint, click Close to close the Foreign Key Relationships dialog box. Finally, save the Categories table by going to the File menu and choosing Save Categories.

Defining the Structure of the Pictures Table

As we will see in Hour 24, when a user uploads a picture to the photo album site, the actual image file is saved on the web server's file system. The picture's additional informationthe title, description, category, and so onneeds to be saved in the database. Therefore, the Pictures database will have the following columns:

• PictureID An auto-increment, primary key column of type int, which uniquely identifies each picture.

• UserId A column of type uniqueidentifier that relates a picture back to a user. Again, this column must be of type uniqueidentifier because that's the type used to uniquely identify each account in the aspnet_Users table.

• CategoryID A column of type int that relates a picture back to a category. For this field, leave the Allow Nulls check box checked because assigning a picture to a category is optional.

• Title An nvarchar(50) column used to store the title of the picture.

• Description An nvarchar(1000) column used to store a description about the picture.

• UploadedOn A column with a datetime data type that indicates when the picture was uploaded to the site.

Only the CategoryID column should have the Allow Nulls check box checked. Figure 22.8 shows the Pictures table after its columns have been created.

Figure 22.8. Each picture added to the system will be represented by a row in the Pictures table.

In addition to the columns, we need to add two foreign key constraints. The first constraint is identical to the sole foreign key constraint in the Categories table; it associates the primary key column UserId in the aspnet_Users table with the foreign key column UserId in the Pictures table.

The second foreign key constraint associates the primary key column CategoryID in the Categories table with the foreign key column CategoryID in the Pictures table. Recall that, by default, when a primary key column value is changed or the record is deleted, the change or deletion fails if there are existing foreign key records. With this default behavior, a user would be unable to delete a category if pictures were currently associated with that category.

For this application, let's allow users to delete categories, even if there are existing pictures associated with that category. If the user deletes such a category, we'll reset the associated pictures' CategoryID values to the Null value, which indicates that it does not belong to any category. To specify this through the foreign key constraint, select the INSERT and UPDATE Specification property in the Foreign Key Relationships dialog box and change the Delete Rule from No Action to Set Null (see Figure 22.9).

Figure 22.9. When a category is deleted, all associated pictures revert to the default category.

Creating the Comments Table

The final table in the photo album database is the Comments table, which will have one row for each comment left by a user for a picture. The Comments table has the following six columns:

• CommentID An auto-increment, primary key column of type int, which uniquely identifies each comment.

• UserId A column of type uniqueidentifier that relates a picture back to a user.

• PictureID A column of type int that relates a comment back to a picture.

• Subject An nvarchar(50) column used to store the subject of the comment.

• Body An nvarchar(1000) column used to store the body of the comment.

• CommentMadeOn A column with a datetime data type that indicates when the comment was made.

All columns in the Comments table should have the Allow Nulls check box unchecked. Figure 22.10 shows the table after its columns have been created.

Figure 22.10. When a user leaves a comment, the comment will be added to this table.

In addition to the columns, we need to add two foreign key constraints. The first constraint is identical to the sole foreign key constraint in the Categories table and the first constraint we added to the Pictures tables; it associates the primary key column UserId in the aspnet_Users table with the foreign key column UserId in the Comments table.

The second one associates the primary key column PictureID in the Pictures table with the foreign key column PictureID in the Comments table. This constraint, by default, will prohibit a user from deleting a picture that has comments. Because a user can't edit or delete comments through the online photo album, it makes sense to have the comments for a picture automatically deleted when the picture is deleted. To accomplish this, set the Delete Rule for this foreign key constraint to Cascade (see Figure 22.11).

Figure 22.11. When a picture is deleted, all associated comments are automatically deleted as well.

At this point we have created the entire database structure for the photo album application. The only step in the design process that remains is creating mockups for the application's user interface.