Essential ColdFusion: Database Basics
This chapter is from the book
This chapter is from the book
This chapter is from the book
Introduction
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 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
|
Text |
Holds text up to 255 characters |
|
Memo |
Holds text up to 65,535 characters |
|
Number |
Holds numerical characters only; used for mathematical calculations |
|
Date/Time |
Holds date and time values |
|
Currency |
Holds currency (numeric) values |
|
Autonumber |
Incremented value by 1 when a new record is added; can also be set to randomly autoincrement |
|
Yes/No |
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–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.