Macromedia Studio 8 [Electronic resources] : Training from the Source نسخه متنی

Understanding Databases

The topic of database theory is deep and entire books have been written on the subject. Serious application developers need to master a lot of database theory before they can create sophisticated applications. But, as with most technical subjects, you can get a lot done with only a basic understanding of the technology at hand. One goal in these lessons is to give you some experience working with databases, DSNs, and dynamic pages, so you can build common applications and are primed to take on more advanced books or training if you wish.

Databases are made up of a series of tables. A database table is a collection of rows and columns, and looks very much like an Excel spreadsheet. What makes database tables special is that they can link to one another. For example, imagine a small business that sells pet products online. This business's database might include three tables, as follows:

Customers: This table is for a list of all the company's customers: first name, last name, street address, city, state or province, postal code, country, phone number, and so on.

Inventory: This table contains a listing of all the company's products, including the name, quantity on hand, price, location, and so forth.

Transactions: This table contains a listing of all the sales through the Web site. It includes the date of the transaction, total price of the transaction, name and quantity of the items sold, and the name of the customer to whom they were sold.

The magic of this setup is that the Transactions table links to the Customers and Inventory tables. Thus, for transaction number 247, rather than listing Jane Smith, 112 Cheshire Lane, Toronto, Ontario, Canada, her phone number, and all her other particulars, the table simply lists a unique ID number associated with her, and pulls up the rest of the information as needed, on the fly. Likewise, for inventory, if Jane orders six bags of catnip, the inventory amount is automatically deducted. Each piece of information needs to be maintained in only one place, and is referenced as needed by other tables.

In the remaining lessons, you will not take advantage of the relational potential of databases (we're assuming that you do not have Microsoft Access). Even so, it's important to understand what tables are, that one database can have multiple tables, and that you can establish relationships between tables.

The following figure shows the users table of the database used in this lesson. Notice that every row (technically called a record) contains an instance of every column (database columns are called fields). Although each record canand usually doeshave different information in each field, they all share the same fields; only the values in them change.

[View full size image]

Each field has a unique name, which is how you specify the field. Records are not generally named; instead, each record has what's called a primary key: some piece of information, stored in one of the fields, that's unique and distinguishes it from every other record. In the figure you'll see a field named keyID, which is a field we created to store this unique value. You'll also notice in the last record of that field, (Autonumber) appears as the value. This means that the database table is set so that whenever a new record is created, Access automatically assigns a new number to that record, which can then be used as its primary key.

If you're working in Windows and have Microsoft Access, you should open and explore dante.mdb, which can be found in this lesson's Start folder. Don't worry if you don't have Access or if you are working on a Macintosh; the database is ready to use, and ColdFusion will be able to work with it. The Web application you create will be your interface to the data in this database.

Now that you understand what the database is, you need to understand how you configure ColdFusion to work with it. And that process requires working with Data Source Names.