Useful Information

Which database?

From an article on CNET Builder.com By Brian Kotek (4/20/00)

Static HTML pages might have been adequate a couple of years ago, but today's Web sites need Web-database connectivity for dynamically generated pages. There are numerous benefits to data-driven Web sites. They offer much greater power, value, and effectiveness. Pages can easily be updated without causing Web builders repetitive strain injuries, and sites will be more consistent throughout, minimizing the chance of errors. Finally, there's a great range of Web databases available, from the powerhouse to the affordable. What Web database product is right for you? There are a number of factors to consider in making your choice.

Why use a web database?

Most companies have some sort of corporate database containing employee information, product inventory, customer orders, or other data. Until recently, this data was usually accessed using client-server systems or dumb terminals. Data might not even be shared over a network at all and might be available only on a specific user's system.

The World Wide Web, along with Web-database connectivity, offers far greater flexibility. Users can access data via the Internet through a Web server, with no geographical limitations. Salesmen in the field, employees in separate offices, and anyone else who is given permission can use the same pool of information. All they need is a Web browser and an Internet connection.

Bring your data to the world

While access to corporate information can be a huge benefit of a Web-enabled database, there can be even bigger advantages. The ability of the Web server to communicate with a database opens up an incredible array of possibilities. You can store e-commerce orders, customer information, user preferences, and discussion board postings. You can keep Web site content in a database, separating the content of your site from the layout and formatting.

This separation allows you to use one data-driven product information Web page to display every product in your inventory. Even if you sell 1,000 different products, you can use the same page to display every one of them. The layout and formatting remain the same, but the content of the page is obtained from the database for each different product. You've turned 1,000 static HTML pages into one data-driven page.

Consider the CNET Web site as an example. The whole CNET site contains thousands of separate documents--or so it seems. In reality, the site uses a comparatively small number of dynamic Web pages as templates to generate all those documents. Each news story, review, sidebar link, and header graphic location is actually stored in a database. The beauty of a system like this is that the pages practically create themselves. This system takes the emphasis off managing the site itself and allows a business to focus on the content

Potential hurdles

To connect your database to the Web, you'll need software that the Web server can use to make calls to the database. These intermediary programs are often called middleware. Examples of middleware include ColdFusion, Active Server Pages, PHP, Java, and Perl. Products such as ColdFusion use Web server APIs (Application Programming Interfaces) to interpret incoming requests for dynamic pages and query the database, while languages such as Perl can be used to write executable files that can be run to make queries. There are dozens of different middleware solutions available.

Middleware can access the database by using native database drivers or standards such as ODBC (Open Database Connectivity) and JDBC (Java Database Connectivity). Most databases support ODBC, so going this route is a safe choice. Once the middleware receives the data from the database, the middleware can process the data, format it, and return it to the user as an HTML document.

The SQL solution

In order to get the information you want from the database, you must use a query in a language the database understands. One of the most popular is SQL (Structured Query Language). There are many tools to help you write SQL queries, and most major databases include an SQL tool or wizard. In addition, learning to write SQL is not very difficult, although some complex queries can require an expert. Keep in mind that regardless of which database you choose, you'll need additional time for SQL training or learning to use an SQL tool.

The good news: Aside from getting the Web server and the database to talk to each other, there aren't many other obstacles. Virtually every major database available today supports ODBC, so the emphasis is really on selecting the right database and middleware for your needs.

Other concerns you must examine include performance requirements, capacity, cost, and platform availability. You must also consider any existing data that you want to Web enable. If your new Web database needs to contain a copy of existing data, further analysis is called for because issues such as replication and data synchronization come into play. It's a good idea to consult with the administrator of your existing database before you make your decision.

Choosing your level of technology

Before choosing a database, you'll need a general idea of what you want it to do. Will it be a small list of company employees, accessed by a few dozen users? Will it store content and customer information for an enterprise-level e-commerce site? How many simultaneous connections to the database do you anticipate? What are your security requirements? Databases come with a variety of functionality. Let's start with a few of the important features that databases can support.

Automatic queries

Stored procedures are precompiled SQL statements that run against the database. Because stored procedures are precompiled and because they reside on the database server itself, they execute much more quickly than standard SQL queries from a Web server. If you are planning to execute a query a great number of times, this speed difference becomes very important.

Triggers are stored procedures that run automatically and are initiated by other database queries or stored procedures. For example, if you are inserting a new customer record into a database, you may want to return that new customer's ID number or insert it into another database table. You can use a trigger to perform these queries automatically whenever a new customer is added to the database. This reduces the number of queries you need to run manually--and makes the developer's job easier. These automatic queries, or stored procedures, perform better than the same queries run from the middleware.

Transaction locking

Transaction locking ensures that a series of related database queries all execute successfully. If you have just received an e-commerce order on your Web site, you will probably have to execute a number of queries. You might store the customer's information in a customer table, update your inventory table to reflect the sale, and add the order and credit card number to an orders table. Obviously, it is vital that all three of these queries occur. What if only the customer and inventory inserts go through, but the order insert fails for some reason? The whole order would be worthless, and your data's consistency would be compromised. Using transaction locking on these three queries would prevent this problem. If the first two queries go through but the third doesn't, the first two queries are "rolled back." The inserts are "undone" and removed from their respective database tables. It's an all-or-nothing proposition: either all three go through successfully, or none of them do. Different databases offer various levels of transaction locking, so it is important to look at this feature carefully.

Security

Security is always a concern when the Internet is involved. Most databases support some level of security, but features vary widely. Some databases integrate with existing security systems in the operating system on which the database runs. Others contain a security framework within the database itself. The bottom line is that access to a Web-enabled database must be made available only to those who need it. This restriction is imperative for databases that contain sensitive data such as credit card numbers. If your database runs on its own machine, separate from the Web server, then you have an extra layer of security. Even if the Web server is compromised, a malicious user doesn't yet have your data.

Database size

Storage capacity is probably the most nebulous feature simply because software makers often overstate a database's maximum capacity. Pushing a database to its storage limits is a sure way to cause data corruption. Even with this limitation in mind, most modern database products can store huge amounts of information. The most robust databases can store terabytes of data, so finding a database that can handle your storage needs should not be difficult.

Future concerns

One of the benefits of storing your data in a database is the ability to upgrade. Since virtually all databases use SQL, it is not difficult to write a query to extract all the data from one database and insert it into another. Many databases even have built-in import and export features. If you outgrow your database, you can often move up to a more robust solution. The great thing about using a Web server to access the data is that it doesn't matter what database you use or how often you change databases. As long as you don't change the structure of your database--that is, as long as you retain table and field names, relationships between tables, and so on--you can keep using the same queries.

Database options and solutions

Microsoft Access 2000

Access is inexpensive and very easy to use, but it is not meant as an enterprise-level database. It is still a viable solution for small amounts of data and a small number of concurrent users. Small, of course, is relative, as its specifications state that Access can hold up to 2 gigabytes of data per table. However, it only supports a few concurrent connections, limiting its usefulness in an environment with a large number of users. Access is best for offices that have standardized on Microsoft's Office suite; Linux or Macintosh users are out of luck. Access supports basic transaction locking but does not allow stored procedures or triggers. It also supports user name and password security on its databases.

MySQL

A robust client-server database that supports a wide range of platforms, MySQL runs under Linux, OS/2, Solaris, HPUX, AIX, and most other Unix flavors, as well as Windows. The Unix version is free, while the Windows version goes for $200. Some may question the choice to use a freeware database, but MySQL is an excellent solution for a business with a small budget. The only drawback is that the product has limited official support. MySQL supports stored procedures and triggers but does not yet support transaction locking (it uses a different system called atomic operations). Transaction locking is planned for a future version. Under most operating systems, MySQL can store 4 gigabytes of data per table.

IBM DB2

Another powerful client-server database solution, DB2 offers good performance at a reasonable cost. It runs on Windows and most Unix flavors. You also get the service and support that have made IBM such a respected company. Lots of third-party applications and add-sons for DB2 are available, and it enjoys a strong user community. DB2 supports transaction locking, stored procedures, and triggers.

Microsoft SQL Server 7.0

Microsoft's enterprise-level database solution, this client-server database is gaining popularity quickly and has been adopted by many large companies such as Dell, Nasdaq, and Monster.com. It is very powerful and easy to use. The only real drawback is that SQL Server runs only under Windows, so you must be running a Microsoft OS in order to use it. For businesses using Windows, SQL Server is the most popular enterprise-level database. SQL Server supports 4 terabytes of data per database, transaction locking, stored procedures, and triggers. It also integrates directly with Windows NT and Windows 2000 user accounts and security.

Oracle 8i

Internet giants such as Yahoo use Oracle, generally regarded as the most powerful and secure database currently available. Oracle runs under Windows and most Unix variants, and it supports unlimited database sizes. Oracle also supports powerful transaction locking, stored procedures, and triggers.