This article, the first of two parts, looks at the different database applications available for use with ColdFusion. It is excerpted from chapter two of the book ColdFusion Web Development with Dreamweaver MX 2004, written by Jen and Peter de Haan et al. (Apress, 2004; ISBN: 1590592379).
Databases and Dreamweaver MX 2004 - Using an Access Database (Page 3 of 4 )
Let’s open up an Access database and look at its inner workings. When you installed ColdFusion, several databases were automatically copied into the install directory. Go to the folder where you installed ColdFusion (by default, this would be C:\CFusionMX\ on a Windows machine) and open the db folder. You should see three Microsoft Access Databases:cfexamples.mdb,cfsnippets.mdb, andcompany.mdb. We are interested only incompany.mdbright now, so double-click on it to open it.
NOTE If you get a warning about the version of the database being created in an earlier version of Access, simply click on OK to open the database. Be aware that you will be able to edit table content, but you won’t be able add additional objects, such as other tables, to the database.
To have full control over the database, you must convert it to a version usable by the version of Access you have. To do this, close the database and select Tools ->Database Utilities ->Convert Database, and choose the format you want to convert to. You then must browse to the database location and select it. The format conversion process is explained in further detail in this Microsoft Knowledge Base Article: http://support.microsoft.com/default.aspx?scid=kb;en-us;324702.http://support.microsoft.com/
Thecompany.mdbdatabase is broken down into three separate tables: Departmt, Employee, and LoginInfo. Let’s begin by double-clicking on the LoginInfo table to open it.
You can see from Figure 2-1 that the table is broken down into three separate columns: UserID, Roles, and Password. This allows us to build an authentication system where users can enter their login credentials (UserID and Password) through form fields on a web page and the action page can run a query to compare the entered details against the values stored in the database.
Figure 2-1. User details stored in the LoginInfo table
If this query returns a matching record, then you know that the UserID and Password supplied are correct. The roles that belong to the person logging in are also available in the one record that had both a matching UserID and Password. If you cannot find a match in the database, it means that the user may have entered an incorrect UserID and Password combination, or that the user is not in the database.
Let’s close this window and right-click the LoginInfo table. Select Design View from the context menu, and you will see the names of the three columns under the heading Field Name. You will also see their respective data types under a column called Data Type, as shown in Figure 2-2.
Figure 2-2. The LoginInfo table in Design View
Note that all three columns have the same data type: Text. This means that the column will accept any sort of textual input (numbers, dates, or Booleans), although it is considered bad form to store anything but strings in a text data type column. You should always choose the “native” data types for numbers, dates, and yes/no values whenever possible to avoid having your database convert data types automatically and making it difficult to retrieve the data at a later date. Try clicking on one of the columns in this database, such as UserID. Look at the attributes of this column in Field Properties (shown in Figure 2-2).
One of the most important attributes for Text data types is Field Size, which dictates the maximum number of characters that the column can hold. If you try to enter 60 characters into a field that supports a maximum length of only 50 characters, ColdFusion will throw errors and the query will fail. For more information on queries, refer to the “Getting Started with Queries” section later in this chapter.
Other important attributes in Access include:
Required: Controls whether a value is required for this field or not
Allow Zero Length: Controls whether you can enter an empty string for this field
Now close the LoginInfo table and look at the Departmt table instead. Right-click the table name and select Design View. You should see the table shown in Figure 2-3. This table also has three fields: Dept_ID, Dept_Name, and Location.
Figure 2-3. The Departmt table in Design View
Each department in this table has its own unique identifier (ID) that allows you to point to that row. Notice that this table has a different Data Type (that you haven’t seen before) called AutoNumber. AutoNumber is a sequential number inserted automatically by Access; every time you enter a new record into the database, Access will generate a unique Dept_ID for you. You should also notice a small “key” icon beside the Dept_ID field, as shown in Figure 2-4.
Figure 2-4. The Access primary key icon
This icon represents a primary key. Primary keys make sure that you cannot insert null values into that column, and that no two values in that column are ever the same. For instance, in this example, no two users can have the same UserID in the Departmt table. Each table must have a primary key that uniquely identifies each single row in the database table. If another unique identifier is not available for a record (such as a product ID or employee number), it is common to use an AutoNumber field as the primary key.
Close this table and look at the final table, Employee. Once again, you have a column called Emp_ID, which is both an AutoNumber data type and a primary key. You also have columns called FirstName, LastName, Dept_ID, StartDate, Salary, and Contract. FirstName, LastName, and Contract share a Text data type, and Dept_ID is a Number data type, which means it accepts only numeric input.
NOTE By default, numeric columns have a field size of long integer, which supports a range of roughly—2.1 billion to +2.1 billion.
The Dept_ID column in the Employee table holds the same values as the Dept_ID column from the Departmt table you looked at earlier. When a primary key in one table is found in a different table, we refer to it as a foreign key. So, we say that Dept_ID is a foreign key in the Employee table. If you store only the Dept_ID foreign key in the Employee table, you don’t need to save both a department name and location for each employee as well. This means that you are reducing the duplication of data, which minimizes the size of the database. Another benefit of breaking down tables this way is that if you change the value of a Dept_Name in the Departmt table, you don’t need to update the tens or hundreds of corresponding records in the Employee table. So, data would need to be changed in only one place. This process of organizing data to minimize redundancy by breaking it into several tables that use primary key–foreign key relationships is known as “normalization.”
A relational database stores data in tables and allows you to define relationships between the tables. The link between the tables is based on one or more fields/columns common to the tables, as shown in Figure 2-5.
Figure 2-5. A simple illustration of a relationship between two tables in the company.mdb database
You can see in Figure 2-5 that each employee in the Employee table at the top has a value for Dept_ID. That value corresponds to the Dept_ID on the Departmt table on the bottom. The Relationships window allows you to see which tables have relationships with one another.
The remaining columns in the Employee table are StartDate (which is a Date/Time data type), Salary, and Contract. In this database, Contract is a Text data type, although, because it stores a Boolean value (yes or no), the Access Yes/No data type might have been a better choice for it.