When I was eighteen, I had a holiday job in an obscure back office of the New South Wales Stamp Duties Office. It doesn’t take an overactive imagination to realise 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 filecards. 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 filecards had been on a computer database! With a computer, this mind-numbing job, which took me several weeks 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 suffering in the Stamp Duties Office, in the mid-70s, computer databases as we know them today were in their infancy. 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.
Today, there are many database products to choose from, ranging from programs designed for developers to simple databases for average computer users to niche databases designed for specific purposes, such as library catalogues and classroom scheduling.
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 one of the most familiar 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. Each field contains a specific piece of information about the subscriber. 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, class schedules 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 websites.
Single and multi-file databases
The simplest form of database consists of a single table of information about a particular topic. If you visualise the phone book as a table consisting of rows and columns, each row represents one phone subscriber’s record. Each column represents a field, which contains the same type of information for each subscriber.
A database can contain a single table of information, such as the phone book, or many tables of related information. An order entry system for a business, for example, will consist of many tables:
- an orders table to track each order
- an orders detail table for tracking each item in an order
- a customer table so you can see who made the order and who to bill
- an inventory table showing the goods you have on hand
- a suppliers table, so you can see who you need to re-order your stock from
- a payments table to track payments for orders
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 very simple example shown here depicts a database used to track reviews of Internet sites published by a computer magazine.
There are two tables in the database. The first is the Reviews table which includes information about each site, the review itself, and who wrote the review. The second is the Categories table containing a list of site categories. This table contains just two fields, the category ID and the category name, and it is linked to the Reviews table via the CategoryID field.
You might wonder, why don’t we put the category information directly in the Reviews table?
One reason is to maintain consistency of the information stored in the database. If you enter the category information directly into the Reviews table, it’s easy to end up with one site’s category listed as, say, “news commentary” while a similar site is listed as “news opinion” and yet another as “news columnist”, when really they all belong to the same category. This is not much of a problem in a small database, but if you have thousands of site reviews in your table, it’s very easy for such problems to multiply. If you try to do a search for “news commentary” on this table, you won’t find the sites categorised as “news opinion” or “news columnist”, even though you’d hope to include them in the result.
On the other hand, if you store your category information in its own table, discrepancies such as this become immediately apparent and it’s a simple matter to eliminate misnamed categories. By then linking the two tables, you can pull information from the Categories table into the Reviews table (it’s known as performing a lookup).
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. Some of the most popular relational databases are Microsoft Access, FileMaker Pro, Bento (on the Mac) and Alpha Five.
Database program tools
A database program gives you the tools to:
- design the structure of your database
- create data entry forms so you can get information into the database
- validate the data entered and check for inconsistencies
- sort and manipulate the data in the database
- query the database (that is, ask questions about the data)
- produce flexible reports, both on screen and on paper, that make it easy to comprehend the information stored in the database.
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 organisation 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 Quicken or MoneyDance, 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, do a Google or check your local software supplier to see if something similar has already been created.
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.