Let's step through a sample database design process.
We'll design a database to keep track of students' sports activities. We'll
track each activity a student takes and the fee per semester to do that
activity.
Step 1: Create an Activities table containing all the fields:
student's name, activity and cost. Because some students take more than one
activity, we'll make allowances for that and include a second activity and cost
field. So our structure will be: Student, Activity 1, Cost 1, Activity 2, Cost 2
Step 2: Test the table with some sample data. When you create sample
data, you should see what your table lets you get away with. For
instance, nothing prevents us from entering the same name for different
students, or different fees for the same activity, so do so. You should also
imagine trying to ask questions about your data and getting answers back
(essentially querying the data and producing reports). For example, how do I
find all the students taking tennis?
Step 3: Analyse the data. In this case, we can see a glaring problem
in the first field. We have two John Smiths, and there's no way to tell them
apart. We need to find a way to identify each student uniquely.
Uniquely identify records
Let's fix the glaring problem first, then examine the new results.
Step 4: Modify the design. We can identify each student uniquely by
giving each one a unique ID, a new field that we add, called ID. We scrap the
Student field and substitute an ID field. Note the asterisk (*) beside this
field in the table below: it signals that the ID field is a key field,
containing a unique value in each record. We can use that field to retrieve any
specific record. When you create such a key field in a database program, the
program will then prevent you from entering duplicate values in this field,
safeguarding the uniqueness of each entry.
Our table structure is now: ID, Activity 1, Cost 1, Activity 2, Cost 2
While it's easy for the computer to keep track of ID codes, it's not so
useful for humans. So we're going to introduce a second table that lists each ID
and the student it belongs to. Using a database program, we can create both
table structures and then link them by the common field, ID. We've now turned
our initial flat-file design into a relational database: a
database containing multiple tables linked together by key fields. If you were
using a database program that can't handle relational databases, you'd basically
be stuck with our first design and all its attendant problems. With a relational
database program, you can create as many tables as your data structure requires.
| 
