Web Databases, Continued

Choosing a Database

After you have decided on a platform, you'll need to choose a database to run. Important factors to consider are database cost and availability of staff who are familiar with the database, as well as speed and features.

Database Comparison Chart
Database	Cost on Unix	Cost on Windows NT	Comments
MySQL	Free	$200	Fast; stable; lacks some advanced features
miniSQL	$250	$250	Free for non-profits and educational institutions; easier to maintain, but less stable, than MySQL
SQL Server	Not available	$1200	Suitable for most Windows NT needs
Oracle	$1200	$1200	Very scalable; suitable for extremely large applications

Free/open-source databases
There are a number of free, open-source databases available. MySQL is a popular free database built by T.c.X, a company based in Sweden that offers paid support for the product. According to benchmark figures available at the MySQL website, MySQL is the fastest freeware database available for Unix machines. This speed, combined with its great stability, low cost (zero dollars), and variety of compatible tools, have made it very popular among web programmers. The fact that the combination of PHP and MySQL won the 1998 Web Tools Award in the Databases category is sure to increase the number of developers who use and can support MySQL.

MySQL does lack two important things you'd find in most commercial databases, however. One is stored procedures. The other is transaction rollback.

Stored procedures are small programs stored in the database. These programs are made up of a string of database commands that you expect you'll need to execute more than once. For example, in the case of an ad tracker program, this might be:

1. select the advertisement to be displayed
2. increment ad_views_count by one
3. if ad_views_count exceeds ad_views_bought set ad_expired to 1
4. return ad_url and ad_expired.

Transaction rollback also allows you to group a series of commands. In this case, however, you group them together as a series of actions which all need to succeed (or fail) together. For example, suppose you are programming an online stock exchange simulation. If Trader A decides to buy 10,000 shares of WidgetCo stock at $15 per share from Trader B, you need to make several updates to your database. These updates include removing $150,000 from Trader A's account and removing 10,000 shares from Trader B's account. If you can't remove 10,000 shares from Trader B's account (perhaps another buyer bought all of B's stock a millisecond earlier), you don't want the $150,000 to disappear from Trader A's account. Rollbacks let you tell the database not to commit the transfer of money unless the transfer of stock worked, or vice-versa.

Another potentially free database is miniSQL. This is a SQL database that is free for schools and non-profits (it costs $250 per year for other institutions). miniSQL is slower and less stable than MySQL, but is easier to install and maintain. MySQL is actually based on miniSQL. The folks at T.c.X built MySQL when they realized miniSQL wasn't providing all the functionality they wanted.

PostgreSQL is another free, open-source database system. It contains some functionality not present in MySQL—for example, transaction rollback—but is slower.

If you're using Windows NT and you're interested in going with MySQL or miniSQL for your database project, here's the good news: both of them have been ported to NT. Now here's the bad news: you'll need to pay if you want to use MySQL for NT. It costs $200, though miniSQL is still free to non-profits and educational organizations.

Commercial databases
A number of commercial databases exist for both Windows NT and Unix. The leaders include Oracle (for both Windows NT and various flavors of Unix), and Microsoft SQL Server (for Windows NT). Other robust commercial databases you might want to consider using are Sybase and Informix. There are also less robust commercial databases available—for example, Microsoft Access—which can be appropriate for low-traffic internal sites. These won't perform very well when your hits start to ramp up, though.

These commercial databases tend to have more features than the freeware databases listed above and are usually more stable. If you are dealing with database tables that will hold millions of records, you'll most likely want a high-end commercial solution such as Oracle. Oracle databases generally have a reputation for being the most robust and scalable. They're probably the way to go if both the dataset and your company's budget is large enough. Staffing may be an additional cost if you take this route, though. There are plenty of people who specialize in Oracle, but because their services usually go to huge institutions deploying huge systems, their services tend to be more expensive.

If you're running your database on Windows NT, Microsoft SQL Server will probably work well for you. It won't handle the millions of records which Oracle can deal with, but has much of the same functionality as Oracle does, as well as speed and stability. The number of developers familiar with it is high enough to guarantee you can find someone to maintain it.

What does Anchor use?
Anchor uses MySQL. We use it because it is free, fast, stable, and (like much open-source software) has a community of users willing to provide technical support for each other.

view a printable version of this article