• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL

Chapter 4. Working with Databases > Understanding Databases

Understanding Databases

In one form or another, you use databases every day. If you visit any popular web site, you’re using a database through a web browser. If you use an ATM, you’re using a database. If you use an automated call system to check the arrival time of a flight, you’re using a database.

In short, databases are integral to all of our lives because of the enormous amount of information we access on a daily basis. Whatever form they take, databases store related information in an organized manner. This book uses a database installed with ColdFusion. To find the database, browse to the db folder of the CFusionMX directory, such as C:\CFusionMX\db, and find the cfexamples.mdb file.

If you have Microsoft Access installed, you can view the contents of the database file. As you will see as you explore the cfexamples.mdb file, databases store information in tables, which contain rows and columns. As shown in Figure 4.1, the tblEmployees table contains a row for each employee. Columns separate the rows by information category, such as FirstName, LastName, and Title. In the rows, individual pieces of data are stored in fields.

Figure 4.1. The tblEmployee table of the cfexamples database contains information related to employees . Notice that rows and columns organize the table.

Using Database Data Types

For storage efficiency, each column is assigned a data type, such as text, number, date/time, currency, and so on. Just like programming languages, databases use data types to maximize memory usage. Although you could store most information entered in a web browser as characters (text), using different data types increases database performance and promotes data validity.

The tblEmployees database table contains a variety of data types. For example, the FirstName and LastName columns are text data types. The IsTemp column is a Boolean (yes or no) data type, and the StartDate column is a date/time data type. Data types also restrict the information to the specified data type.

Understanding Relational Databases

You probably noticed that the cfexamples database contains multiple tables, including tblDepartments, tblEmployees, and tblParks. That’s because the cfexamples database is considered a relational database, meaning that information in multiple tables is related, and the tables have relationships with each other.

Relationships represent links between tables. Usually, primary and foreign keys define relationships. Primary keys, such as the EmployeeID column in the tblEmployees table, identify the record, in this case an employee. The data stored in the primary key field of a record must be unique because other database tables will use this value to identify an employee. Don’t worry, most database applications can automatically generate primary keys for you.

A foreign key lets you represent a primary key in another table. For example, the DeptIDFK column in the tblEmployees table contains foreign keys for the primary keys in the DeptID column of the tblDepartments table. Figure 4.2 shows the relationships among the tables of the cfexamples database.

Figure 4.2. You can use Microsoft Access’s Relationship feature to view a graphical representation of the relationships between tables. Notice that the links connect primary keys to foreign keys.

Database Naming

A set of informal database-naming conventions have developed over time. For example, the “tbl” preface to a name indicates that the name refers to a database table. If a column contains primary keys, the column name is prefaced by “PK.” If a column contains foreign keys, the column name is suffixed by “FK.”

Selecting a Database Vendor

Usually, you don’t get to choose the database vendor because ColdFusion is frequently used to put existing databases on the web. If you do get the chance to select a database vendor, you must carefully weigh the project requirements against the cost of purchase and maintenance.

If your project consists of creating a database for a company intranet, Microsoft Access is a popular choice, if for no other reason than most companies already own a license for Access through Microsoft Office. However, as a shared-file or desktop database, Access has some significant drawbacks, such as scalability and possible data corruption.

Client/server database programs, such as Microsoft SQL Server or Oracle 9i, provide robust, scalable enterprise database solutions. Client/server databases tightly control access to database information, offer excellent performance, and provide multiple safeguards against data corruption and failure. At the same time, client/server database systems are very expensive and are more difficult to set up and maintain. As an alternative, check out open-source databases, such as MySQL and PostgreSQL.

ColdFusion supports the majority of popular database programs used today, including Microsoft Access and SQL Server, Oracle, Informix, Sybase, IBM DB2, and MySQL. For a complete list of database programs that ColdFusion supports, check the Macromedia web site (www.macromedia.com).

  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint