| A database is simply a collection of structured information with a common theme. You actually use databases all the time. For example, a telephone directory is a database of information about people, including their name, address, and phone number. Your date book is structured information (hopefully) about your appointments and the things you need to do. In terms of software, databases are used for keeping track of such things as employee information, asset registers, and of course, product information. The advantage of a database system over using a word-processing document or spreadsheet to keep track of data is that information is easily manipulated, updated, and reused with a database system. For example, by using a database system to store employee information, we can easily perform tasks, such as sorting employees by name or department, searching for employees with more than five years of service, or updating employee contact details. There are many database products on the market for you to choose from. Microsoft Access is an easy-to-use, entry-level database system, and it is included in most versions of the Microsoft Office suite. We will be using Access for our sample databases throughout this book. Some other popular database products include high-end database servers such as Oracle and Microsoft SQL Server. There are also free open-source products that you can download and use such as MySQL and PostgreSQL. See the "Databases" section of the www.LearnColdFusionMX.com web site for more information and links to database products. Although most database products differ slightly, they all have similar features and structure. The following text uses a sample database called Staff.mdb, which comes with the example files you can download from www.LearnColdFusionMX.com. If you have a copy of Microsoft Access, you can open the database file and follow along. If you do not have Microsoft Access, don't worry. You will not need it to use the database with the sample web pages we will build. Tables Information in a database is stored in tables. A database usually consists of several tables, with each table containing data about one particular area. For example, a typical company database might have one table for product information, another for customer details, and another for order information. A table is very similar in appearance to a spreadsheet (see Figure 3.1). It consists of rows and columns. Each row contains a single line of information known as a record. Figure 3.1. The Microsoft Access display of the Employees table.  A record contains information about just one item. In our sample database, each record in the Employees table consists of information about just one employee. There is an additional table in the Staff.mdb database; it is a table called Departments. Each record in this table contains only information about each department. Each record is further broken down into smaller bits of data called fields or columns. Each field has a specific name and contains a specific piece of information about the record. In our Employees table, we have fields such as EmployeeID, FirstName, and LastName. These fields will always contain the same type of information for each record or employee. Just like ColdFusion variables, database fields contain one certain type of data. Data types for your fields might be text, numeric, Boolean, or date, among others. For a list of acceptable data types, see your database product's documentation because data types vary slightly from vendor to vendor. Relationships Tables are the basic organizational element in a database, and as previously mentioned, databases can contain more than one table. For example, our Staff.mdb database contains three tables: Employees, Departments, and Users. It is not uncommon for large-scale databases to contain hundreds of tables. Information in one table will quite often relate to information in another table. In our Staff.mdb database, for example, we have an Employees table and a Departments table, and each employee belongs to a specific department. For separate tables to relate to each other, they need to share a common piece of information to link them together. In Figure 3.2, you will see that a DepartmentID value appears in both the Departments table and the Employees table. Figure 3.2. The Employees and Departments tables.  Several kinds of relationships can exist between tables. The most common is a one-to-many relationship. In a one-to-many relationship, information appears only once in one table but many times in another table. For example, in our Staff.mdb database, one department has many employees. A DepartmentID code, such as 2, appears only once in the Departments table but many times in the Employees table. There are other types of relationships such as a one-to-one relationship or a many-to-many relationship. Microsoft Access has a feature that enables you to graphically see existing relationships between tables. In Figure 3.3, you can see the Relationships window, which illustrates the one-to-many relationship between tables in our Staff.mdb database. Figure 3.3. The Microsoft Access Relationships window.  This section of the book will not attempt to teach you database design. There are many good books on the market for that. Rather, we will concentrate on how to get at data already stored in a database and use that data in our web site. Sample Databases For the examples in the body of this chapter, we will be using a Microsoft Access database called Staff.mdb. The structure of this database is outlined in Table 3.1. Table 3.1. The Staff.mdb Database Structure| Tables | Fields Names | Data Type(s) |
|---|
| Employees | EmployeeID | AutoNumber, numeric | | | FirstName | Text | | | LastName | Text | | | DepartmentID | Numeric | | | Title | Text | | | Email | Text | | | Extension | Numeric | | | DateHired | Date/time | | Departments | DepartmentID | Numeric | | | DepartmentName | Text | | | ManagerFirstName ManagerLastName | Text Text | | Users | UserID | AutoNumber, numeric (a unique ID number for each user) | | | UserName | Text (the user's login name for the administration section of the site) | | | Password | Text (the user's login password for the administration section of the site) | | | Roles | Text (the level of administration they can perform on the site) |
NOTE For our makeover exercise at the end of this chapter and throughout the rest of the book, we will be using a Microsoft Access database called BBdata.mdb. The structure of this database is outlined in Table 3.7 at the end of this chapter. Both of these databases are available for download at the www.LearnColdFusionMX.com web site.
JDBC, ODBC, and DSN To get information out of our database and use it in our web pages, we first have to get the ColdFusion Server software to connect to that database. ColdFusion does this by using something called Java Database Connectivity (JDBC). When required, ColdFusion can also use JDBC in combination with a similar technology called Open Database Connectivity (ODBC). The type of database you want to connect with will dictate which of these options is used. As you will see shortly, the use of these technologies is transparent to the developer. JDBC and ODBC are very similar technologies. (One is Java based; the other is Windows based.) They are both what are referred to as Application Programming Interfaces (APIs). In short, JDBC/ODBC (or any API, for that matter) acts as a go-between, enabling any type of application (in this case, a ColdFusion application) to communicate with any type of database without having to worry about the subtle differences of each individual database program. Figure 3.4 illustrates this capability of JDBC/ODBC. Figure 3.4. Database connectivity using JDBC or ODBC.  This is similar to the way in which you can get any program to print to any type of printer. As long as you have the correct driver installed for that printer, you can print to it from any program on your computer. Your computer programs talk to the printer driver, which in turn translates requests into a format that a particular printer can understand. Individual database vendors, such as Microsoft and Oracle, create JDBC and ODBC drivers for their particular database program that enable it to "plug in" to JDBC or ODBC. ColdFusion Server comes with most of these drivers built-in, and as you will see shortly, it is quite easy to configure a database connection for use in your web application. When you create a connection to a database with ColdFusion Server, you give that connection a name. This is referred to as a Data Source Name (DSN). By doing this, whenever we want to use that database, we can just refer to it by its DSN rather than having to specify connection information (such as filename and location) every single time we want to use the database. The next section will walk you through setting up database connections and DSNs for our sample database. | 
