This article, the first of two parts, looks at the different database applications available for use with ColdFusion. It is excerpted from chapter two of the book ColdFusion Web Development with Dreamweaver MX 2004, written by Jen and Peter de Haan et al. (Apress, 2004; ISBN: 1590592379).
Databases and Dreamweaver MX 2004 - Different Kinds of Databases (Page 2 of 4 )
You can choose from several database systems. Although some swear by Microsoft SQL Server, others praise MySQL or Oracle. There seems to be no consensus on what database system is the best, but we hope to shed light on the differences between the various products.
Databases fall into two main categories: file-based and server-based. File-based databases (such as Microsoft Access) reside in a single file and are easy to distribute, but often suffer from inferior performance compared to their server-based counterparts. Server-based databases (such as Microsoft SQL Server) offer better performance and can handle more concurrent users, but are often more difficult to set up and export and distribute to other servers.
Perhaps the most widely distributed and available product around is Microsoft Access, which ships with the Microsoft Office suite. This database is a great product for setting up a nice, quick database for test environments and for small sites, but for large-scale production environments with many concurrent users, we would recommend something a bit more robust, such as Microsoft SQL Server, Microsoft SQL Server 2000 Desktop Engine (MSDE 2000), MySQL, or Oracle. All have strengths and weaknesses. MySQL tends to be popular because it is inexpensive.
Microsoft Access comes bundled with the Microsoft Office Suite. Microsoft has made many improvements to Access in the XP version. The most notable improvements include XML support, a new SQL Server 2000 desktop engine that is compatible with SQL Server 2000, and the ability to link Access database tables to tables in SQL Server. One of the downsides to Access, however, is that it was not designed to be used with web sites. It cannot handle several concurrent connections efficiently, and database file sizes can get large very quickly. As we said earlier, you should really use Access databases only for small web sites and test environments, but not for large-scale production environments.
You can find more information on Microsoft Access at http://www.microsoft.com/office/access/.
Microsoft SQL Server
SQL Server (pronounced “Sequel Server”), is an enterprise-level product with a very impressive feature set. Some of its more notable features include XML support, stored procedures, views, triggers, and wizards that help you optimize indexes and primary keys. SQL Server is also highly scaleable for high-end servers—it allows multiple ways of performing multiserver replication, integrated backup and restore, and other useful management functions. Several different versions are available, including Enterprise, Standard, Personal, Developer, and even a version for Windows CE.
The main drawback of using SQL Server is the cost. Depending on your licensing needs, the price can get very high. For a pricing comparison between SQL Server and Oracle, check outhttp://www.microsoft.com/sql/evaluation/ compare/pricecomparison.asp. Another drawback is that SQL Server has very limited platform support; it is available only for Windows platforms at this time.
You can find more information on SQL Server athttp://www.microsoft.com/sql/.
Microsoft SQL Server 2000 Desktop Engine
The Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) is similar to Microsoft SQL Server in many aspects, but it is a low-cost alternative. If you meet licensing requirements, you are able to redistribute the royalty-free database engine without paying any royalties. MSDE 2000 supports various Microsoft operating systems ranging from Windows 98 to Windows XP. Find out more athttp://www.microsoft.com/ sql/techinfo/development/2000/MSDE2000.asp.
MySQL is a very good open source package that runs on a variety of platforms. It is an excellent alternative to Access, but it can be a little trickier to install and configure. In addition to this, you must have a greater knowledge of SQL in order to create tables and view data. MySQL lacks a graphical user interface, and administration is typically done through a console window similar to DOS or Telnet. However, many third-party applications exist to add a graphical front-end to MySQL.
MySQL is a powerful database product, and it is free only as long as you meet certain licensing requirements. Although it is not as feature-rich as SQL Server or Oracle, future versions promise to incorporate stored procedures, triggers, and views (all of which we discuss in Chapter 6). Overall, MySQL is a very popular package because of its low cost, speed, and power, and it is an excellent multiplatform product. Visithttp://www.mysql.com/products/mysql/for more product and download information.
Oracle is another extremely powerful large-scale enterprise solution. Like SQL Server, Oracle offers stored procedures, XML support, and is a secure and stable platform. Oracle also offers additional features not intrinsic to SQL Server, such as the ability to cluster a database across several computers and the ability to run on both Windows and Linux operating systems. Oracle’s most significant detracting factor would have to be the price, which is prohibitively high for many development houses. Find out more about Oracle athttp://www.oracle.com.
TIP A solid knowledge of SQL and databases is essential for building larger sites with dynamic content. Poorly designed databases tend to perform slowly and make it much more difficult to retrieve data easily later.