Introduction to Database Design and Administration

Connecting a website to a database can be a daunting prospect. On Windows, the process is made easier by such standardized interfaces as Open Database Connectivity (ODBC), but that's not really available on FreeBSD. Nor does FreeBSD have access to native versions of the most popular high-end database packages, such as Oracle, MS-SQL, and IBM DB2, or to what have become the standard Windows-based dynamic-content pagelayout languages, ASP and ColdFusion. Creating dynamic web content in FreeBSD is accomplished largely through the use of open-source alternatives to those packages. MySQL, PostgreSQL, and other similar databases fulfill the role of the back end itself, and PHP, Perl, and Python provide tools that furnish us with the connectivity into those backend data stores.

Figure 29.1 illustrates the schematic layout of a website with dynamic content that interacts with a database back end. The user (the visitor to the site) opens the front-end content page in a browser. This content is rarely written in pure HTML. Instead, most web page content is written in a language such as PHP, ASP, ColdFusion, Perl, or a similar framework. These languages include both the HTML that lays out the page and the calls into the database that return the information the page's code uses to fill out the interesting parts of the page. For instance, a database might contain all of a bank customer's transactions in the past month. When the user accesses the page with the proper parameters to extract those transactions, the content page prints out standard HTML headers, images, and layout elements common to the site. Then, the page accesses the database and performs a query. The database returns the results of that query to the code in the content page, which then formats the customer's data appropriately (wrapping it in HTML, in most cases) and prints it into the page. The page then finishes the job by printing out more common HTML, such as footers and disclaimers.

Figure 29.1. A diagram of a website that uses dynamic database-driven content. The web page content and server (the front end) send queries to the database (the back end) using Structured Query Language (SQL) via a conduit layer such as ODBC, PHP, or Perl; the database returns the results of those queries through the same conduit to the front end, where it is formatted for display on the page.

The two large conceptual divisions of the website database system are in the back end (the database itself) and in the front end (the content engine and HTML output). We will turn our attention first to the back end so that you can become familiar with how database queries work because that is the foundation not only of the web applicability of databases but also of all other possible uses of databases.