COMPUTERIZING YOUR PROGRAM 
Databases: Managing Your Information
By Rick Curtis, Director, Outdoor Action Program, Princeton University

This material may be freely distributed and used by the anyone. However, if included in publications, written or electronic, attributions should be made to the author. 

	There are two basic types of databases, flat file and relational. A flat file database works much like individual pieces of paper in a file cabinet. All of the data goes into a single table made up of a number of fields. Each row in the table corresponds to a particular record (or piece of paper in the file cabinet analogy).  All of the data is physically linked. For example an Accident Database for an outdoor program might look something like this:

Incident Number	Injury/Illness	Date	Location	State	
001	ankle sprain	5/27/93	Wind Gap	PA	
002	laceration	9/14/93	Pine Barrens	NJ	
003	infected blister	9/25/93	Catskills	NY	

	In this case you are looking at the database in a table view. The field names are at the top of each column and each row is an individual record.  This row and column arrangement is very similar to a spreadsheet. Usually data is entered through a data form which corresponds to a single record. A blank data form for the Accident Database might look like the following:

ACCIDENT DATABASE

		Incident Number:____________	Injury/Illness:____________________________________

		Date:_______________		Location:________________________________________

Flat File versus Relational Databases

	Flat file databases are best for discrete data, that is information for which there is only one record for each event. Both flat file and relational databases allow you to perform calculations and basic analytical functions (such as count, total, subtotal, average, maximum, minimum) on data in the database. These allow you to do things like calculate such things as subtotals, sales tax, and totals for creating a billing invoice. Relational databases usually have additional statistical and mathematical functions that you can perform on you data.

	A relational database typically holds data in multiple, interrelated tables.  Data is connected not physically but logically. The logical connection between two tables is a field which both have in common.  An example of a Trip Database is defined below.  In this case there are three tables with related information all linked by the trip number field.

Trips Table			Trip Leaders Table		
Trip Number	001		Trip Number	001	
Location	Water Gap		Leader	Becky Johnson	
Income	$100.00		Leader	George Allan	
Expenses	$80.00				
Balance	$20.00				
					
Participants Table			Accident Table		
Trip Number	001		Trip Number	001	
Participant	Sarah Miller		Incident	1	
	Sam Smith		Incident	sprained ankle	
	Rowena Johnson		Victim	Sarah Miller	
	Taylor Kimberly				

	In this example you can enter your information for each trip and then call up a report of all trip participants for Trip 001, or you could ask for all of the accidents on Trip 001. The relational database does the work of collecting the requested data from across the tables using the field they have in common, Trip Number. A relational database allows you to have multiple entries tied to one event (such as multiple participants all with the same trip number).  

	In order to store the same trip information in a flat file database, you would have to have a  separate field for each participant, leader, accident, etc. (Participant1, Participant2, Leader1, Leader2, etc.). This can make both the table and the data entry form extremely large and cumbersome to work with. Especially when you have varying numbers of participants. If you have 5 participants on one type of trip and 20 on another, you might find it too much of a hassle to actually keep participant fields in the database.  

	A relational database is particularly useful when there records may have the same data in common. For example, say you keep staff training information in your database. If you keep a staff database and run a staff training, there are two ways keep the information depending on whether you use a flat file or a relational database. Lets look at sample data for a cliff rescue workshop. 

Staff Training	
Training Number: 005
Location: Water Gap
Activity: Cliff Rescue
Hours: 12
Continuing Ed Credits: 2

	In a flat file database, to keep all this information on each staff member, you would have to add it in to each staff member record (lots of duplicate data entry). With a relational database, all you need to do is have a training table that relates the staff member and the training number (which means only typing in 005 and the staff member’s id number). Then you can call up the details of the training by the relationship between the two tables.

Staff Training Table			Staff Table		
Training Number	005		Staff ID Number	14	
Location	Water Gap		Training Number	005	
Activity	Cliff Rescue				
Hours	12				
Continuing Ed Credits	2				

	Flat file databases often have lookup capabilities which means that using a unique value in a common field between two tables you can look up data in one table and import it into the other. For example, suppose you had a large mailing list that included employees as well as people outside your company. Using the social security number as the unique field value you could program your flat file employee list database to look up the address of an individual from your overall mailing list. This means that you only need to maintain the correct address in the mailing list table.

	In both flat file and relational databases one of the most critical aspects is reporting. Besides just holding your information, you want to be able to look at it through what is known as a query to look for trends etc.  A typical query might be how many staff have current EMT certification or what percentage of our participants were from New Jersey? Your database should have solid reporting capabilities in which reports are easy to define and frequently used report specs can be saved for future reference. The combination of reporting and mathematical calculations allow you to use either type of database for calculating and generating bills and invoices.  

	Which is better, flat file or relational?  This depends on your particular needs.  In many cases a flat file database will be sufficient for data like mailing lists, employee records, etc., items which are discrete data parcels. The advantage to flat file databases is that they are general inexpensive and fairly easy to learn.  Relational databases are best if you have more complicated interrelated data that needs to be kept and/or if you need to perform more complex statistical analysis on your data. Relational databases are far more powerful often allowing you to customize the program interface specifically for your needs. With this power comes a steeper learning curve and higher price. As you look for a database also think about how it will interface with your other programs including your word processor and spreadsheet. Can you do mail merges from the database into the word processor? Can you export database into the spreadsheet for more involved calculations?

Conclusion
	
	Setting up a good database will take you some time. In the end, a the time you spend will allow you to use your computer for more than word processing.

Rick Curtis is the Director of the Outdoor Action Program at Princeton University. He has been computerizing the program for the past eight years.