Dabbling in Live Databases: MSAccess

September 13, 1999

Data-basics

We need databases because we need a way to structure a big mess of data. The very simplest database is concerned with only one category, or "field", of data, such as a list of client names. But a list of names on its own is not very useful, unless you simply enjoy looking at a bunch of names. What we need is more information to relate to these names. For instance, the total bill on our last invoice. Now we have a database with two fields--two fields which are related to one another:

If you read any article about databases you'll undoubtedly see the term "relational database" -- which is exactly what we are building above. Relational databases are extremely common and the most likely database you will want to query from a web page. All of the major database software packages, including Microsoft Access, FileMaker Pro, Oracle, Informix, and so on are geared towards creating and managing relational databases.

In real life, our database above would likely contain even more fields -- more information we can relate, the richer the database. For instance, it would make sense to store an e-mail contact for the client, the invoice number, and whether or not the client has paid up. In fact, we can visually represent this database as a table ... literally:

The above is a table, both on the screen and in database lingo. Each record in the database is called a row, which makes a whole lot of sense. And, of course, each row is made up of fields, which are in some way (mathematical, conceptual, philosophical) related to one another.

We can quickly see how we might formulate requests of this database, for example:

• "Please list all client names and their e-mail addresses".
• "Please list all client names who were billed at least $500."
• "Please list all client names and e-mail addresses who are unpaid."

Pretty simple so far. Of course, the more fields in each row, the more complex your requests could become. It shouldn't be tough to imagine applying this example to many types of databases, such as basic retail inventory, or birthdays, and so on.

Thus far we've created a basic database with one table -- in fact, a single database can contain several or more tables. Because this is a relational database, the tables should be related to each other somehow. Technically speaking, the tables should relate to each other via a common, unique field. Consider a second table that we might include in this database:

The first table, billing, contains a unique invoice code for each row, which relates the client name, address, total, and paid status to that invoice. Yet, in the invoices table, we store more detailed information about each individual invoice ... namely, the number of hours worked and the rate of pay. We can not only relate data fields within a table, but we can relate data across two tables because they share a common unique key (the invoice code). Now we can formulate requests such as:

• "Please list all invoices where I worked at least 10 hours."
• "Please list all invoices where I charged at least $50 per hour."
• "Please list the names of all unpaid clients for whom I worked at least 10 hours."
• "Please list the names and e-mail addresses of all clients for whom I charged at least $70 per hour."

There you have it -- the basic ins and outs of your garden variety relational database. Our example database, which we could call clients, consists of two tables, billing and invoices. We've seen the types of requests you could foist upon this beast -- but how to foist them? Ultimately, we're going to use Perl to request data from the database, receive the data, and even format it for output to a Web page. However to do that we need to understand how general requests are made from a database, Perl notwithstanding.