Studio MX 2004 makes extensive use of databases, simply because Macromedia has identified dynamic sites as being the wave of the future. This explains why the Studio products that have a web feature (Flash, Dreamweaver, and Director) are all able to incorporate dynamic data. Though the tools can do it, we still aren't at the point where the platforms?Mac and PC?can manage dynamic data in such a way that novices can easily get into the game regardless of whether they develop on a Mac or a PC.
In many respects, the question of using Mac or PC as the development platform comes down to the software that will be used. The problem is, of course, which is the right software? To arrive at that decision, you should ask such questions as:
Are you an experienced database developer, and if so, on which system are you the most comfortable? This will determine the database used.
If the team includes a database programmer, which system does he or she use? Database programmers do have their preferences.
Will your tables have a lot of records?
Will you be working locally and uploading to a server, or do you have a computer dedicated to being a database server?
What database software does the server use, and do you even have a choice?
Having answered the platform questions, you should next consider the databases systems themselves. The following is a brief review of the two most popular database systems.
With its roots in the UNIX world, MySQL can be used on both Macs and PCs. The great thing about this DBMS is its price?it's free. That's the good news. The bad news is, in its basic form, it uses a command line interface, so familiarity with the Macintosh Terminal or the Windows Command prompt utility is an asset. Still, there are several free graphical user interfaces (GUI) that have been developed for MySQL, making the need for command lines less important. It's fast, it's stable, and it can handle large amounts of data.
If you work for a corporation, government, or educational institution, the odds are very good you have a copy of or access to Microsoft Access. It is an integral part of Microsoft Office and is quite reasonably priced. Access doesn't have the power of MySQL under its hood because it's unable to accept a large number of people trying to access the same information simultaneously. If you were selling tickets to a J.K. Rowling reading of a Harry Potter novel, for example, Access won't work. Still, its sheer ubiquity and the capability to manage small- to medium-size databases make it ideal for those on the Windows platform. Those of you using Macs, Unix, or Linux, however, are simply out of luck.
As we said earlier, the Studio tools able to create files for web use or playback can use ColdFusion MX to obtain data from a database. Dreamweaver, being the assembly tool, has a robust suite of features located in the Application menu shown in Figure 8.1. Flash uses Flash Remoting to access a database, and Director can either store data gathered or use a Flash movie and the Flash Communication Server.
Having determined your DBMS, the next question is, "How does the data look?" That's a key question, and there are a number of excellent books out there that will answer it. Still, it is important that you understand a couple general database design principles before you go to work:
Group related items together in one table, not many. Use many, and you will have a lot of redundant data. For example, the Oakbridge membership table groups the tables describing the members, a facilities table groups all the facilities data about the facilities available to the members, and so on. If a table appears to describe more than one feature, consider splitting it into two tables.
Don't create tables that duplicate values many times in many rows. For example, if an Oakbridge member books a tennis court, place the order information in a separate table named Member_Bookings. Then link that table to the Members table by placing the primary keys from the Members table in the Member_Bookings table. If you treat the data as separate entities, you will only be duplicating the information.
Specify the data type for each column of the database. For example, you would store the number of tickets available for public skating in a number column. You would store a member's phone number in a number column. A member's address could go in a text column because the street number is an important text value.
The bottom line on the web is speed and efficiency. It is no different with databases. The more accessible the data in the database is to the application calling it, the faster it will appear on the page. Though we gave you some broad guidelines for designing data in the previous section, we thought we would let our resident expert, Jordan, offer a few excellent tips:
Categorize your tables to hold data relevant to what you are storing. You wouldn't want to store booking information in the tables that were defined for the tour.
Use relations when associating various pieces of data, such as membership information and booking information.
In the case of our tour that will be constructed in Chapter 10, "Assembling the Tour," we only need one table for the facilities because there is only one row of records per facility. We can relate other tables, such as the booking information, to the facilities table.
If you use the data dictionary template that we discussed in Chapter 4, "Planning the Data for a Dynamic Site," you will have a road map as to HOW you define your fields. We have provided a simple data dictionary for the facilities tour. It is in the Chapter 8 Exercise Folder that can be downloaded from the book's site. The file is Data Dictionary.pdf.
To get yourself started, make sure that you have MySQL installed and running. Full installation and testing instructions are out of the scope of this book. One of the better tutorials for installation and so on is by Minh Huynh of Macromedia. His article "Setting Up the PHP, MySQL and Apache Server Platform on Macintosh OS X for Dreamweaver MX" can be found at http://www.macromedia.com/devnet/mx/dreamweaver/articles/php_macintosh.html. You will also need to make sure that you have a database management application installed and functioning. We are using YourSQL on Mac OS X, which is freely available at http://www.mludi.net/YourSQL/. To create the Oakbridge database, follow these steps:
Launch YourSQL. When the main interface opens, click the Add server button, which opens the Logon dialog box. If your database server is on the same computer as YourSQL, enter localhost in the server text input box. If you have them, enter your User ID and Password and click the Logon button.
Click on the Create Database button to create a new database. When the dialog box appears asking for the name, enter oakbridge and click the Create Database button.
You will only need one table because we are only concerned with the tour. Click the Create Table button. When the Create Table dialog box opens, enter the word facilities in the name text input box and click the Create Table button.
Our naming convention is to use plurals because the table will be more than one facility. This is done by clicking on the Create Table button.
After the table has been created, a Design Table tab will appear at the bottom of the interface with the first field column already defined. This predefined column will have to be changed to an ID field. Double-click the name of the field. In this case, it is called new_column. Change the name to facilityID.
Double-click on the name in the Field column?varchar?and rename it int.
Double-click on the size and delete the number. YourSQL will then set the default int (short for integer) size for this field.MySQL uses a default integer size that is 11 bytes long, which is more than enough for our IDs.
Click on the Auto Increment check box to set it. We want this field to automatically assign an incremental value. You will get an error message asking you to create an index. Click the Create Primary Key button.
Deselect the Nullable check box. ID fields should never have a null value.
Click on the Create Column button and create three new columns using the following values:
Field name? facility
Field name? description
Field name? imageURL
When finished, your table should resemble Figure 8.2. Quit YourSQL.
Though the steps are the same as in the Mac, the interface is a bit a different on the PC. Before starting, make sure you have downloaded and installed MySQL and MySQL Control Center, which is the MySQL GUI. Both MySQL and the Control Center are available at http://www.mysql.com.
To launch MySQL, open the MySQLAdmin file located in C:\mysql\bin. This will open the WinMySQLAdmin file. Though it looks mysterious, it really isn't. In fact, you really don't need to use it. Simply right-click on the interface where indicated and select Hide Me from the Context window. The window will close, and a small Traffic Light indicating MySQL is running will appear on your taskbar. Keep in mind that the first time you open the WinMySQLAdmin file, you will be asked for a username and password.
Double-click the shortcut to the MySQL Control Center located on your desktop.
When the Control Center opens, right-click the databases folder on the left side of the screen. Select New Database from the context menu. When the Create Database dialog box opens, enter oakbridge and click OK. The dialog box will close and the new database will appear under the databases folder.
The first application to be created is the tour, which means you only need one table. Double-click the Oakbridge database icon. Three subheadings will appear: Databases, Server Administration, and User Administration. Expand the Databases heading and double-click on the Database icon. This creates a further subheading called Tables. Right-click on Tables and select New Table from the Context menu. This will open the Create Table dialog box.
Click the Table Properties dialog box at the bottom of the dialog box to open the table properties. Click once in the table name area and enter the word facilities. (Our naming convention is to use plurals because the database is holding multiple records of the same category. We are storing more than one facility in this table).
Click the Field Properties tab. We will change this predefined column to an ID field. Double-click in the text input box of the first field and enter facilityID. This field will be a primary key in our table. To make this into a primary key, click on the key button in the menu bar.
Click and hold on the Data Type pop-down. A list of choices will appear. Scroll down to int and select it. The number in the length field at the bottom will disappear. The application will set the default int size for this field (in MySQL, the default integer size is 11 bytes long, which is more than enough for our IDs).
Click the Auto Increment check box to set it. We want this field to automatically assign an incremental value. Uncheck the Allow Null checkbox. ID fields should never have a null value.
Create three more fields using these values:
Field name? facility
Field name? description
Field name? imageURL
When finished, your table should resemble Figure 8.3
When you are finished, click the table's close box. When you are prompted to save the changes, click OK. This will return you to the Console Manager window. Click the table name, and your table will be visible as a part of the Oakbridge database (see Figure 8.4). Close the Console Manager. Don't quit the MySQLAdmin.exe file. Leave it running. Your clue that it is running is a traffic light icon in the system tray.