A simple example of refining a single-file database’s structure

 

Figure 1: A simple – and flawed – students table

 

This fragment of a students table suffers from various defects. The combined name field makes it hard for us to locate students by using their last names, and also difficult to sort the table alphabetically by last name. Even worse, how do we uniquely identify each student? Currently, this table uses a combination of the Name and Date of Birth fields to identify each student. What if we happen to have two Jim Smiths, both born on 5th September 1982? It’s unlikely, yes, but not impossible.

The composite address field is also a problem: for instance, how do we pre-sort mail by postal code so we can do bulk mailouts at discount rates?

  

Figure 2: An improved students table

By breaking the fields down we end up with double the number of fields but a much simpler and more effective structure. The new Student ID field lets us uniquely identify any student, even when they share names and birthdates. We’ve split the Names field into first and last names so we can get an alphabetical listing. We’ve done the same to the address field, allowing us to sort by postal code or suburb.