Relational Database

By far the most common use for computers during most of their existence has been to create and store [Page 908]databases. From large databases that manage a bank's account information to the common e-mail program, databases are the engines behind most of the software in use today. In simple terms, a database is computer software that contains organized data. The data are structured to allow a user to search for specific data, reorder the data, and create reports containing specified parts of the data. For the epidemiologist, a relational database can provide a tool to manage and maintain large data sets, create reports, and prepare basic statistical analyses.

In the earliest mainframe computers, databases were complex to create and maintain and remained the province of trained professionals. When the desktop or personal computer appeared in the 1980s, database software was introduced that allowed individuals without a background in computer science to create and use databases. Ashton Tate's dBASE was the first major commercial database software to be widely used on those early desktop computers. With the evolution of the graphic interface within Microsoft's Windows operating system and Apple's Macintosh computer, it became even simpler for noncomputer professionals to create their own database systems. Currently, the two dominant database systems on desktop computers are Microsoft Access and Filemaker Pro. These two programs offer both the database software and integrated tools to create data displays and reports and allow users to write basic computer programs necessary for data management, even for users with little or no experience in database design.

A structure of a database is referred to as a ‘schema.’ The basic structure is made up of records, each of which contains fields. To use the analogy of a patient's medical form, each form containing information about an individual patient is a record. The data on the form are contained within different fields, such as name, address, age, and gender. The database itself is comparable with a file cabinet that contains all the patient records. In this type of database, referred to as a ‘flat file,’ all the data are self-contained and could just as well be maintained on a spreadsheet as on a relational database.

The limitation of the flat file is that it may be inconvenient to have all the information about a given patient in a single flat file. For instance, you may want to keep a record of each of a patient's visits, to record specific information about that visit (such as blood pressure, temperature, height, and weight), to add a new field to a single flat file for each of these variables on each visit would be awkward. Neither do you want to reenter basic information about patients, such as their age and insurance company, each time they have an office visit. Another reason to not store all information in a single flat file is that certain information needs to be kept confidential. For instance, you would not want information about a patient's HIV status to be accessible to a staff member who needs to use the patient file only to perform billing operations. By using a relational database model, multiple databases, also referred to as ‘tables,’ can be linked so that different types of information can be entered into different tables, yet all the information about a single record, for instance, a particular patient, is linked and can be combined to create different reports. For instance, you might have one table that records basic demographic and contact information for each patient (age, home address, etc.) and a second table that records information about individual patient visits. Both would be linked by an identification number unique to a particular patient. This is referred to as a ‘one-to-many’ relationship, because one patient record may be linked to multiple visit records. The visits database can display the patient information from the patient file, and because this information is linked rather than reentered, the likelihood of error is reduced. By extension, other tables can be created containing data about drugs prescribed, treatments administered, insurance payments, and so on. These tables can all be linked to and can use data from the original patient information table.

...