Home > Articles

Essential ColdFusion: Database Basics

  • Print
  • + Share This
Learn about ColdFusion, the scalable rapid application development platform that offers you the ability to create databases. Effectively design relational databases with Microsoft Access, and learn how to use Structured Query Language(SQL)to interact with data.
This chapter is from the book


In This Chapter:

  • A Look at Relational Databases
  • Introduction to SQL
  • To Quote or Not to Quote?
    • Project I: Creating an Access Database File Creating a small relational Access file, which you will use for several projects in this book
  • Setting Up an ODBC Connection
  • Studio's SQL Builder
  • Query of Queries
  • Dynamic Datasources
    • Project II: Retrieving Data Pulling data from the database, and displaying it in the browser
  • New Functions
    SQL Statements
      • SELECT
      • FROM
      • INSERT
      • ORDER BY
      • WHERE
      • DISTINCT
      • INNER JOIN
      • Ascending and Descending (ASC DESC)
    • <CFOUTPUT>
    • <CFSET>
    • <CFQUERY>
  • Recap

Most major web sites use some sort of database on the back end to power their sites in one way or another. Even smaller sites have started developing with databases due to the amazing power available with a database. There's a somewhat short learning curve involved in understanding the basics of learning to use them. With applications such as ColdFusion, ASP, PHP, and countless others, integrating database functionality into a web site is becoming increasingly easier.

To effectively work with databases, you need to have some idea of how they actually work. And with a chapter called "Database Basics," you'll learn exactly that.

There are several types of database applications on the market that you can use, ranging from under $100 to several thousand dollars. An inexpensive and readily available application is Microsoft Access. For heavy-duty database work that needs to span multiple servers, use Microsoft SQL server, Oracle, or Sybase. These cost much more than Access, but they offer much more in functionality, scalability, and security. They are also designed to handle many more simultaneous requests than a product like Microsoft Access.

A Look at Relational Databases

What is a relational database? In simplest terms, it is a series of tables that have common fields linking related information. It's a way to relate pieces of information that have a common bond. For instance, a simple online store application might have three tables:

  • Customers table Stores personal information about customers

  • Lists information about products

  • Category table Contains the various product categories

The Customers table will be a stand-alone table, meaning that it will not relate to the other two tables. Only the Products and Category tables will be related. The relationship between these two tables could look like this:

Figure FIGURE 1–1 Relationship of the category field to the Category table.

You'll notice the Products and Category tables have primary keys associated with them (shown in bold). A primary key is used to uniquely identify a row in a table. For a relational database to function properly, primary keys cannot be duplicated. The relationship shown above demonstrates a "one to one" relationship. In the Products table, the sku field is the primary key, which has the data type of AutoNumber. This means that every time you add a new product into the Products table, the sku will AutoNumber itself in increments of 1.

TABLE 1–1 Common Datatypes Found in Access


Holds text up to 255 characters


Holds text up to 65,535 characters


Holds numerical characters only; used for mathematical calculations


Holds date and time values


Holds currency (numeric) values


Incremented value by 1 when a new record is added; can also be set to randomly autoincrement


Contains a value of 0 or 1; used for 'NO/YES', 'OFF/ON', 'TRUE/FALSE'

The categoryID in the Category table is the primary key for that table. The category field in the Products table holds the value of the categoryID in the Category table, so it uses the categoryName and the categoryID in the Category table. This might sound a little confusing at first, so take a look at Figure 1-2 to see how this works.

Figure 1-2FIGURE 1–2 The values of the category field of the Products table are related to the values of the categoryID field in the Category table.

Through the use of SQL, you can now interact with the data in your tables.

  • + Share This
  • 🔖 Save To Your Account