Building a Database for Dynamic Applications in Macromedia DreamWeaver MX 2004

This chapter is from the book

Macromedia Dreamweaver MX 2004 and Databases

Learn More Buy

This chapter is from the book

This chapter is from the book 

Macromedia Dreamweaver MX 2004 and Databases

Learn More Buy

Building Our Access Database

The first step in building our client's intranet application is to build the database that will fuel it. Based on the responses we received during our planning phase, we will be building four database tables to store our data. After the tables are created, we can define relationships between those that need them.

Creating the Tables

The four tables we are going to build are the Human Resources table, the Information Services table, the Departmental Site Management table, and the Username/Password table.

The Human Resources table will store all of the information that KrystalClear might have in a typical employee file. Demographic information like name, address, and contact information would be present along with organizational information such as title, department, location, and the name of the employee's supervisor. We are going to start with the basics and, if KrystalClear wants to, it can add fields at a later date.

Exercise 3.1 Creating the Human Resources Table

1. Open Microsoft Access on your workstation.

2. From the menu bar, select File, New.

3. In the New File panel, shown in Figure 3.9, select a Blank Database.

4. Using the File New Database dialog box, create a folder called KrystalClear in your My Documents folder. As shown in Figure 3.10, name the file kc_corporation_0493.mdb and click Create.

5. In the database window, click the Tables category and then choose Create Table in Design View.

Figure 3.9 Choose to create a blank database from the New File panel.

Figure 3.10 For security reasons, give your database file a unique name.

6. In the Design view, add the data using the parameters shown in Table 3.2.

Table 3.2 Sample Data for Human Resources Table

Field Name	Data Type	Field Size	Required	Allow Zero Length	Indexed
EmployeeID	Text	11	Yes	No	Yes (No Duplicates)
LastName	Text	50	No	Yes	No
FirstName	Text	50	No	Yes	No
HomeAdd	Text	50	No	Yes	No
City	Text	25	No	Yes	No
HomeState	Text	2	No	Yes	No
Zip	Text	10	No	Yes	No
HomePhone	Text	50	No	Yes	No
Title	Text	50	No	Yes	No
Department	Text	50	No	Yes	No
Extension	Text	10	No	Yes	No
HireDate	Date/ Time	N/A	No	N/A	No
EditPages	Yes/No	N/A	No		No




You should now have a table that looks like the one in Figure 3.11.

Figure 3.11 The structure of the Human Resources table.

7. Right-click on the gray box to the left of EmployeeID and choose Primary Key from the context menu. By indicating that EmployeeID is a primary key, no two records will be allowed to have the same Employee ID. This ensures that no duplicate entries are added to the database.

8. Save the table as tbHR and close the table.

We now have a table that contains the fields for our employees. For the most part, the fields pertain to their contact information and company status. The EditPages field, however, was created in response to our communication with KrystalClear. This field allows Human Resources to indicate who has access to edit pages. When combined with the Department field, not only do we know whether that employee can edit pages, but for what department.

The next step is to create the table that allows Information Services to assign an asset (for example, computer, phone, or pager) to a specific employee.

Exercise 3.2 Building the Information Services Table

1. In the Database window, select the Tables category and choose Create Table in Design View.

2. In Design view, add the data using the parameters shown in Table 3.3.

Table 3.3 Sample Data for Information Services Table

Field Name	Data Type	Field Size	Required	Allow Zero Length	Indexed
AssetID	AutoNumber	Long Integer	N/A	N/A	Yes (No Duplicates)
EmployeeID	Text	11	No	Yes	No
AssetType	Text	25	No	Yes	No
AssetBrand	Text	25	No	Yes	No
AssetModel	Text	50	No	Yes	No
AssetSerial	Text	50	No	Yes	No
Date Assigned	Date/Time	N/A	No	N/A	No
Date Returned	Date/Time	N/A	No	N/A	No
ReturnReason	Memo	N/A	No	Yes	No




You should now have a table that looks like the one in Figure 3.12.

Looking over the table, you should see a group of fields that have unique names, data types that match the data that will be stored in the records, and appropriate field lengths. Be careful when setting your field lengths because the sum of your field sizes directly affects the size of your database. It's a good idea to estimate as closely as possible how many characters are going to be necessary for a field. Although you don't want to devote too many characters and increase the size of your database, you also don't want to chop off pieces of data (which Access will do if the data submitted is too large for the field size).

In addition to the field name, data type, and size, we have also indicated that none of the fields in the database should be required fields and only the AssetID field should be an AutoNumber field. When building dynamic applications, it is better to use a technique called form verification to ensure that the information the user has entered meets your requirements. If you choose to allow the database to determine if a field is required, when the missing data is encountered, a very cryptic error message is displayed in the browser (which has a tendency to freak out users). If, however, you use form validation, you can customize your error message with a clear, polite message that lets the user know what is wrong and how she can remedy it.

Figure 3.12 The structure of the Information Services table.

3. Right-click the gray box to the left of AssetID and choose Primary Key from the context menu.

4. Save the table as tbIS and close it.

The third table that we are going to create allows each department to update information contained in its departmental intranet site.

Exercise 3.3 Creating the Tables for Departmental Site Management

1. In the Database window, select the Tables category and choose Create Table in Design View.

2. In the Design view, add the data using the parameters shown in Table 3.4.

Table 3.4 Sample Data for Departmental Site Management Table

Field Name	Data Type	Field Size	Required	Allow Zero Length	Indexed
PageID	AutoNumber	Long Integer	N/A	N/A	Yes (No Duplicates)
Department	Text	50	No	Yes	No
PageTitle	Text	100	No	Yes	No
NavLink1Text	Text	50	No	Yes	No
NavLink1URL	Text	50	No	Yes	No
NavLink2Text	Text	50	No	Yes	No
NavLink2URL	Text	50	No	Yes	No
NavLink3Text	Text	50	No	Yes	No
NavLink3URL	Text	50	No	Yes	No
MainPageData	Memo	N/A	No	Yes	No
LastUpdated	Date/Time	N/A	No	N/A	No




You should now have a table that looks like the one in Figure 3.13.

3. Right-click the gray box to the left of PageID and choose Primary Key from the context menu.

4. Save the table as tbPageMgmt and close it.

The last table we need to create stores the usernames and passwords that allow the users to log in and out of the intranet. We store this information in a separate table so that the employee can have her own username and password without it being a visible part of her employee record.

Figure 3.13 The structure of the Departmental Site Management table.

Exercise 3.4 Creating Username/Password Tables

1. In the Database window, select the Tables category and choose Create Table in Design View.

2. In the Design view, add the data using the parameters shown in Table 3.5.

Table 3.5 Sample Data for Username/Password Table

Field Name	Data Type	Field Size	Required	Allow Zero Length	Indexed
UserID	AutoNumber	Long Integer	N/A	N/A	Yes (No Duplicates)
EmployeeID	Text	11	No	Yes	No
Username	Text	50	No	Yes	No
Password	Text	50	No	Yes	No
LastLogin	Date/Time	N/A	No	N/A	No




You should now have a table that looks like the one in Figure 3.14.

Figure 3.14 The structure of the Username/Password table.

3. Right-click the gray box to the left of UserID and choose Primary Key from the context menu.

4. Save the table as tbUserID and close it.

The final step in building our database is to create the necessary relationships between our tables. Because each asset must be assigned to a user that exists in the HR table, we will create a relationship between those two tables. In addition, because a username and password can only be assigned to an employee with a record in the HR database, we will create a relationship between those two tables as well.

Exercise 3.5 Building Relationships Between Tables

1. With the kc_corporation_0493 database open, choose Tools, Relationships from the menu bar.

2. In the Show Table dialog box, shown in Figure 3.15, click on each of the four tables and click the Add button.

Figure 3.15 The Show Table dialog box allows you to specify which tables you would like to create relationships for.

3. Click the Close button on the Show Table dialog box. You should now see the four tables within the Relationships window. Expand each of the boxes so that you can see all the field names. To do this, click on the bottom border of the box and drag the mouse down. The windows should now look like Figure 3.16.

4. Click the EmployeeID field in the tbIS box and drag it onto the EmployeeID field in the tbHR box.

5. In the Edit Relationships dialog box, shown in Figure 3.17, check the Enforce Referential Integrity check box and then check the Cascade Update Related Fields and Cascade Delete Related Records. Click the Create button.

Figure 3.16 The Relationships window before adding the relationships.

Figure 3.17 The Edit Relationships dialog box allows you to enforce referential integrity.

By creating this relationship, we are creating what is called a one-to-many relationship. This means that the one record in the HR database (the employee) can have many records in the IS database (for example, a phone, a computer, or a pager). In addition, by applying referential integrity to the relationship, we ensure that when an employee leaves the company and is deleted from the HR database, his records in the IS database are deleted as well. This avoids having orphaned records in the IS database.

6. Click and drag the EmployeeID field in the tbUserID box onto the EmployeeID field in the tbHR box.

7. Select all three check boxes again and click the Create button. As shown in Figure 3.18, you should now have two relationships between three of your tables.

Figure 3.18 The relationships have been created.

8. Close the Relationships window and save the layout when prompted.

Congratulations! You have just created the back-end database for our upcoming intranet project. Before we break out Dreamweaver and start laying down code, there are a few more issues that we need to cover regarding Microsoft Access.