| A Simple Design Process Continue.. |
The Students table would normally contain each student's first name, last
name, address, age and other details, as well as the assigned ID. To keep things
simple, we'll restrict it to name and ID, and focus on the Activities table
structure.
Step 5: Test the table with sample data.
Step 6: Analyse the data. There's still a lot wrong with the
Activities table:
- Wasted space. Some students don't take a second activity, and so we're
wasting space when we store the data. It doesn't seem much of a bother in
this sample, but what if we're dealing with thousands of records?
- Addition anomalies. What if #219 (we can look him up and find it's Mark
Antony) wants to do a third activity? School rules allow it, but there's no
space in this structure for another activity. We can't add another record
for Mark, as that would violate the unique key field ID, and it would also
make it difficult to see all his information at once.
- Redundant data entry. If the tennis fees go up to $39, we have to go
through every record containing tennis and modify the cost.
- Querying difficulties. It's difficult to find all people doing swimming:
we have to search through Activity 1 and Activity 2 to make sure we
catch them all.
- Redundant information. If 50 students take swimming, we have to type in
both the activity and its cost each time.
- Inconsistent data. Notice that there are conflicting prices for
swimming? Should it be $15 or $17? This happens when one record is updated
and another isn't.
Eliminate recurring fields
The Students table is fine, so we'll keep it. But there's so much wrong with
the Activities table let's try to fix it in stages.
Step 7: Modify the design. We can fix the first four problems by
creating a separate record for each activity a student takes, instead of one
record for all the activities a student takes.
First we eliminate the Activity 2 and Cost 2 fields. Then we need to adjust
the table structure so we can enter multiple records for each student. To do
that, we redefine the key so that it consists of two fields, ID and Activity. As
each student can only take an activity once, this combination gives us a unique
key for each record.
Our Activities table has now been simplified to: ID, Activity, Cost. Note how
the new structure lets students take any number of activities � they're no
longer limited to two.
Step 8: Test sample data.
Step 9: Analyse the data. We know we still have the problems with
redundant data (activity fees repeated) and inconsistent data (what's the
correct fee for swimming?). We need to fix these things, which are both problems
with editing or modifying records.
|
