Relational Database Model Tutorial

The Flat-File Address Book

• We'll start with a simple address book, in a flat table. If you like, you can build this in any spreadsheet; a spreadsheet uses columns and rows to create a two-dimensional representation of your data, which is the definition of a flat file.
  
  Across the top, we'll create these labels: Name, Address, City, State, Zip, Phone and Email. This creates seven fields for our records, which we can then enter on subsequent rows of the database.
  
  What happens, however, if a single person has more than one email address, or if two people have the same name? In a flat file, this could easily cause problems in data storage or utilization; for example, if you shoehorn two email addresses into a single Email address cell, you will no longer be able to programmatically retrieve a single email address for each person. (This restriction on data entry--called "normalization"--is crucial to building good databases; a database where every field is a hodgepodge of information rapidly becomes unusable.)
  
  The easiest way to change this is to keep the flat file, but to add new fields (i.e., Email2, Email3). This is also bad database design; a report or script that attempts to retrieve an email address will fail if Email is blank, but Email2 or Email3 is filled in, unless increasingly complicated programs are used.
  
  

The Simple Relational Database Model

• Instead, create a relationship between two flat files. In the first table, add a field labeled ID, and fill it with sequential numbers. Each number must be unique in each record. In a second table, create two fields: ID and Email. The tables will look like this:
  
  Flat: (Name: "John Smith", Email: "jsmith@nosuchaddress.com")
  
  Relational:
  Table 1: (ID: "31415", Name: "John Smith")
  Table 2: (ID: "31415", Email: "jsmith@nosuchaddress.com")
  
  The ID number is the "key field" that relates the two tables. To add more email addresses to this record in table 1, you would add more records to the second table, each with the same ID as John Smith. Most database software will manage these relationships for you. Entering multiple email addresses into a form will create ID relationships behind the scenes, provided the database model is set up correctly.
  
  

Advanced Relational Techniques

• Sometimes you might want to use key fields that are themselves meaningful. Let's say you want to set up an employee table, but you have two people both named John Smith at the same company. You could use unique IDs for this, but you could also use other forms of unique data. Email addresses are frequently used for this purpose, as they are usually guaranteed to be unique. A community center, however, where some people may share an email address, would not be a good use of this technique.