Storing and processing information in a database

Storing data in separate files

If you keep separate files of data for each part of your organization, you can ensure that each program uses only the data it needs, but you must store a lot of data in multiple places simultaneously. Problems with keeping separate files are:

• Redundant data takes up space that could be put to better use
• Maintaining separate files can be difficult and complex

For example, suppose that a medical clinic keeps separate files for each of its departments, such as the clinic department, the accounting department, and the ophthalmology department:

• The clinic department keeps data about each patient who visits the clinic, such as:
  • Identification number
  • Name
  • Address
  • Illnesses
  • Date of each illness
  • Date patient came to clinic for treatment
  • Treatment given for each illness
  • Doctor that prescribed treatment
  • Charge for treatment
• The accounting department also keeps information about each patient. The information that the accounting department might keep for each patient is:
  • Identification number
  • Name
  • Address
  • Charge for treatment
  • Amount of payments
• The information that the ophthalmology department might keep for each patient is:
  • Identification number
  • Name
  • Address
  • Illnesses relating to ophthalmology
  • Date of each illness
  • Names of members in patient's household
  • Relationship between patient and each household member

If each of these departments keeps separate files, each department uses only the data that it needs, but much of the data is redundant. For example, every department in the clinic uses at least the patient's number, name, and address. Updating the data is also a problem, because if a department changes a piece of data, the same data must be updated in each separate file. Therefore, it is difficult to keep the data in each department's files current. Current data might exist in one file while defunct data remains in another file.

Storing data in a combined file

Another way to store data is to combine all the files into one file for all departments to use. In the medical example, the patient record that would be used by each department would contain these fields:

• Identification number
• Name
• Address
• Illnesses
• Date of each illness
• Date patient came to clinic for treatment
• Treatment given for each illness
• Doctor that prescribed treatment
• Charge for treatment
• Amount of payments
• Names of members in patient's household
• Relationship between patient and each household member

Using a combined file solves the updating problem, because all the data is in one place, but it creates a new problem: the programs that process this data must access the entire file record to get to the part that they need. For example, to process only the patient's number, charges, and payments, an accounting program must access all of the other fields also. In addition, changing the format of any of the fields within the patient's record affects all the application programs, not just the programs that use that field.

Using combined files can also involve security risks, because all of the programs have access to all of the fields in a record.

Storing data in a database

Storing data in a database gives you the advantages of both separate files and combined files: all the data appears only once, and each program has access to the data that it needs. This means that:

• When you update a field, you do it in one place only.
• Because you store each piece of information only in one place, you cannot have an updated version of the information in one place and an out-of-date version in another place.
• Each program accesses only the data it needs.
• You can prevent programs from accessing private or secured information.

In addition, storing data in a database has two advantages that neither of the other ways has:

• If you change the format of part of a database record, the change does not affect the programs that do not use the changed information.
• Programs are not affected by how the data is stored.

Because the program is independent of the physical data, a database can store all the data only once and yet make it possible for each program to use only the data that it needs. In a database, what the data looks like when it is stored is different from what it looks like to an application program.