A Common Architecture for Database-backed Web Sites

Summary: Database-backed web sites can be created using a variety of technologies but a combination we use frequently is a Microsoft IIS web server (either dedicated or shared/virtual) combined with a Microsoft Access database and either Microsoft ASP or Macromedia ColdFusion as the scripting language.

Author: Bruce Hart
Tuesday, June 24, 2003

Database-backed web sites can be created using a variety of technologies but a combination we use frequently is a Microsoft IIS web server (either dedicated or shared/virtual) combined with a Microsoft Access database and either Microsoft ASP or Macromedia ColdFusion as the scripting language.

The database can be designed (a schema needs to be created describing how the data is structured) using Access running on a PC desktop machine and then periodically the database file (a binary *.mdb file) is uploaded to the server for testing via FTP. Once the site has gone live, this same approach can be used for maintenance (and if the client has someone in-house who knows Access then they can do their own data entry without needing an outside vendor).

We typically employ either Microsoft ASP or Macromedia ColdFusion as the scripting language (the server-side code that runs against the database and presents the information back to the web site visitor). We like to use ColdFusion when it's available because it has some built-in features that make it a bit faster to put together database-backed web sites. For sites with complex business logic, ASP provides a little more programming power.

The way this works in practice for the web site visitor is that they go to a web page that dynamically pulls information from the database (using an ASP or ColdFusion script that resides on the web server) and presents it to the visitor (as far as the visitor is concerned what they see looks just like a regular, static web page even though the page has been built on-the-fly). Information is pulled from the database via SQL queries embedded in the ASP or ColdFusion script. The SQL queries are tuned so that only the necessary information for a given web page is pulled from the database.

You can usually tell when a site is using this architecture by looking in the browser's address window. If the URL has a .asp or .cfm (often followed by a "?" character and a bunch of gibberish) then the site is using ASP (.asp) or ColdFusion (.cfm).

Database-backed web sites can be built very successfully using a UNIX server and technologies like Perl or PHP (as the scripting language) and mySQL (as the database) but the Microsoft/Macromedia approach outlined above can offer some significant advantages (especially when the database is fairly small, the site traffic is relatively low and the client knows Access (or is willing to at least learn how to modify an existing database) and would like to do their own data entry).

www.supplyknowledge.com is a good example of a site that is mostly driven from Access databases. We designed and built the site but the client is able to do much of their own maintenance using Access.

The following diagram attempts to illustrate how the various pieces fit together.

NOTE
This article can be reproduced elsewhere. All we ask is that you also provide a link back to our web site (www.crendo.com).

Back to list of articles.