A database defines a structure for storing information. Databases are typically organized into tables, which are collections of related items. You can think of a table as a grid of columns and rows. ColdFusion works primarily with relational databases, such as Oracle, DB2, and SQL Server.

The following image shows the basic layout of a database table:

A column defines one piece of data stored in all rows of the table. A row contains one item from each column in the table.

For example, a table might contain the ID, name, title, and other information for individuals employed by a company. Each row, called a data record, corresponds to one employee. The value of a column within a record is referred to as a record field.

The following image shows an example table, named employees, containing information about company employees:

The record for employee 4 contains the following field values:

• LastName field is "Smith"
• FirstName field is "John"
• Title field is "Engineer"

This example uses the EmpID field as the table's primary key field. The primary key contains a unique identifier to maintain each record's unique identity. Primary keys field can include an employee ID, part number, or customer number. Typically, you specify which column contains the primary key when you create a database table.

To access the table to read or modify table data, you use the SQL programming language. For example, the following SQL statement returns all rows from the table where the department ID is 3:

SELECT * FROM employees WHERE DEPTID=3