Using a Database - macromedia COLDFUSION MX 7 Web Application Construction Kit [Electronic resources] نسخه متنی

Using a Database

Back to the example. At this point, you have determined that a film database will make your job easier and might even help preserve your sanity. You create a table with columns for movie title, tag line, release date, and the rest of the required data. You enter your movie list into the table, one row at a time, and are careful to put the correct data in each column.

Next, you instruct the database application to sort the list by movie title. The list is sorted in a second or less, and you print it out. Impressed, you try additional sortsby rating and by budgeted amount. The results of these sorts are shown in Figures 5.5, 5.6, and 5.7.

You now have two or more lists, but you had to enter the information only once; because you were careful to break the records into multiple columns, you can sort or search the list in any way necessary. You just need to reprint the lists whenever your records are added, edited, or deleted. And the new or changed data is automatically sorted for you.

A Database Primer

You have just seen a practical use for a database. The movie list is a simple database that involves a single table and a small set of columns. Most well-designed database applications require many tables and ways to link them. You'll revisit the movie list when we discuss relational databases.

Your first table was a hit. You have been able to accommodate any list request, sorted any way anyone could need. But just as you are beginning to wonder what you're going to do with all your newfound spare time, your boss informs you that he'll need reports sorted by the director name.

"No problem," you say. You open your database application and modify your table. You add two new columns, one for the director's first name and one for the last name. Now, every movie record can contain the name of the director, and you even create a report of all movies including director information. Once again, you and your database have saved the day, and all is wellor so you think.

Just when things are looking good, you get a memo asking you to include movie expenses in your database so as to be able to run reports containing this information.

You think for a few moments and come up with two solutions to this new problem. The first solution is simply to add lots more columns to the table, three for each expenses item (date, description, and amount).

But you realize this isn't a long-term solution at all. How many expenses should you allow space for? Every movie can, and likely will, have a different set of expenses, and you have no way of knowing how many you should accommodate for. Inevitably, whatever number you pick won't be enough at some point. In addition, adding all these extra columns, which won't be used by most records, is a tremendous waste of disk space. Furthermore, data manipulation becomes extremely complicated if data is stored in more than one column. If you need to search for specific expenses, you'd have to search multiple columns. This situation greatly increases the chance of incorrect results. It also makes sorting data impossible because databases sort data one column at a time, and you have data that must be sorted together spread over multiple columns.