beginners menu


Databases from scratch I: Introduction

When I was eighteen, while on convalescent leave from beautiful Vietnam, I had a holiday job in an obscure back office of a post office. It doesn’t take an overactive imagination to realize that a temporary position in this bureaucratic outpost was not the most exhilarating of experiences.

The work reached its low point when I was given the job of renumbering – by hand – 30,000 file cards. The cards were numbered from 1 to 30,000 and the powers that be wanted them renumbered 0 to 29,999 so that each batch of 100 cards would start with the same digits.

If only those file cards had been on a computer database! With a computer, this mind-numbing job, which took me several long days to complete, could have been done in a matter of seconds. In fact, it would have been an utterly trivial task.

The emergence of databases

Unfortunately, at the time I was numbering in the post office, in early 1970, computer databases as we know them today were in their pre-natal. Around 1970 a researcher called Ted Codd had developed the “relational data model”, which was to become the foundation stone of modern database technology. In the mid-70s, however, computer databases – particularly in the hands of end users – were not a common thing.

It wasn’t until the beginning of the ’80s, with the development of dBASE II (there was no dBASE I), that microcomputer-based databases started coming into their own. Although riddled with bugs, dBASE put enormous power into the hands of microcomputer developers and it remained the pre-eminent database program until the advent of Windows 3.x. With Windows 3 came a new breed of PC database, designed to be much easier to use than their DOS-based predecessors.

What is a database?

Let’s take a step back and define exactly what a database is. If spreadsheets are the ‘number crunchers’ of the digital world, databases are the real ‘information crunchers’. Databases excel at managing and manipulating structured information.

What does the term ‘structured information’ mean? Consider that most ubiquitous of databases – the phone book. The phone book contains several items of information – name, address and phone number – about each phone subscriber in a particular area. Each subscriber’s information takes the same form.

In database parlance, the phone book is a table which contains a record for each subscriber. Each subscriber record contains three fields: name, address, and phone number. The records are sorted alphabetically by the name field, which is called the key field.

Other examples of databases are club membership lists, customer lists, library catalogues, business card files, and parts inventories. The list is, in fact, infinite. Using a database program you can design a database to do anything from tracking the breeding program on a horse stud to collecting information from the Mars Rover. And increasingly, databases are being used to build Web sites (see an example of Database Web Publishing in Action).

Single and multi-file databases

A database can contain a single table of information, such as the phone book (click to see an example), or many tables of related information. An order entry system for a business, for example, will consist of many tables:

Each of these tables will be linked to one or more of the other tables, so that you can tie information together to produce reports or answer questions about the information you have in your database.

Multi-file databases like this are called relational databases. It’s relational databases, as we’ll see later in this series, that provide exceptional power and flexibility in storing and retrieving information.

A multi-file example

Relational databases are made up of two or more tables of information which are connected in some way.

The example below shows a database used to track reviews of Internet sites which we are developing at Australian PC User magazine.

A relational database containing three tables

There are three tables in the database. The first is the Internet Site Reviews table which includes information about each site, the company or person maintains the site, the review itself, and who wrote the review.

The Site Owners table contains contact details for each person or organization who owns a site listed in the Site Reviews table. The two tables are linked to one another via the Company Name field, which they have in common.

The Reviewers table contains contact details for each person who writes site reviews. It’s linked to the Site Reviews table via the Author field.

Database programs

To create and maintain a computer database, you need a database program, often called a database management system, or DBMS. Just as databases range from simple, single-table lists to complex multi-table systems, database programs, too, range in complexity.

Some, such as the database component of Microsoft Works, are designed purely to manage single-file databases. With such a product you cannot build a multi-table database. You can certainly create numerous tables for storing different types of information, but there’s no way to link information from one table to another. Such programs are sometimes called flat-file databases, or list managers.

Other database programs, called relational database programs or RDBMSs, are designed to handle multi-file databases. FileMaker Pro is a relational database that’s easy to use and fairly inexpensive.

The most popular relational databases are the offerings from the big three software companies. Lotus, Corel and Microsoft each produces a full-featured relational database application available both as a standalone program or as part of its integrated suite. Lotus has Approach, Corel has Paradox and Microsoft has Access.

Database program tools

A database program gives you the tools to:

Most of the more advanced database programs have built-in programming or macro languages, which let you automate many of their functions.

Using a database

If the mention of programming languages makes you feel you’re getting out of your depth, don’t worry! Most of the database programs you’re likely to encounter can be used at a variety of levels.

If you’re a beginner, you’ll find built-in templates, sample databases, ‘wizards’ and ‘experts’ that will do much of the hard work for you. If you find the built-in databases don’t quite work for you, it’s easy to modify an existing database so it fits your needs, and it’s not at all difficult to learn to create your own simple database structure from scratch.

For more advanced users, the more powerful database programs enable you to create complete, custom-built, application-specific systems which can be used by others in your organization or business.

When not to use a database

Even though you can use a database program to do anything from managing the inventory of a parts supply warehouse to managing your personal finances, sometimes the smart option is to not use a database at all. That’s because there’s no point in reinventing the wheel: If you want a personal financial manager, you’re far better off spending money on one of the commercial programs, such as Microsoft Money or Intuit’s Quicken, than slaving for weeks creating your own version of the same thing.

The same goes for vertical market applications. Before you spend months designing a church contribution application or that parts inventory system, take a look around the Web or at your local software supplier to see if something similar has already been created. Shareware libraries such as ZDNet’s Software Library are littered with such specialist applications.

Planning ahead

There’s one crucial thing you need to do whenever you create a database: plan ahead. Whether it’s a single table or a collection of tables, you need to look at the information you want to store and the ways you want to retrieve that information before you start working on the computer. That’s because a poorly structured database will hamstring you further down the track when you try to get your information back out in a usable form.

We’ll look at database design in detail in the next articles in this series.

top home beginners menu