Microsoft Office Tutorials and References
In Depth Information
DATABASE DESIGN RULES
You can also think about this event entity in a different way. Over time, a band plays in many clubs, and
each club hires many bands. Thus, Band and Club have a many-to-many relationship, which signals the need
for a table between the two entities. A Bookings table would associate the Band and Club tables. You
implement an associative table by including the primary keys from the two tables that are associated. In this case,
the primary keys from the Band and Club tables are included as foreign keys in the Bookings table.
Rule 5: Avoid data redundancy. You should not include extra (redundant) fields in a table. Redundant fields
take up extra disk space and lead to data entry errors because the same value must be entered in multiple
tables, increasing the chance of a keystroke error. In large databases, keeping track of multiple instances of
the same data is nearly impossible, so contradictory data entries become a problem.
Consider this example: Why wouldn
t Club Phone Number be included in the Bookings table as a field?
After all, the agent might have to call about a last-minute booking change and could quickly look up the
number in the Bookings table. Assume that the Bookings table includes Booking Number as the primary key and
Club Phone Number as a field. Figure A-9 shows the Bookings table with the additional field.
BOOKINGS
Field Name
Data Type
Booking Number (primary key)
Text
Band Name
Text
Club Name
Text
Club Phone Number
Text
Date
Date/Time
Start Time
Date/Time
End Time
Date/Time
Fee
Currency
FIGURE A-9
The Bookings table with an unnecessary field Club Phone Number
they help define the booking. Band Name and Club Name are foreign keys and are needed to establish the
relationship between the Band, Club, and Bookings tables. But what about Club Phone Number? It is not
defined by the Booking Number. It is defined by Club Name
The fields Date, Start Time, End Time, and Fee logically depend on the Booking Number primary key
in other words, it is a function of the club, not
of the booking. Thus, the Club Phone Number field does not belong in the Bookings table. It is already in the
Club table; if the agent needs the Club Phone Number field, it can be looked up there.
Perhaps you can see the practical data-entry problem of including Club Phone Number in Bookings.
Suppose a club changed its contact phone number. The agent could easily change the number one time, in Club.
But now the agent would need to remember all of the other tables that contained the field and change the
values there too. In a small database, this task might not be difficult, but in larger databases, having
redundant fields in many tables makes such maintenance difficult, which means that redundant data is often
incorrect.
You might object by saying,
In a sense,
they are. But they are needed to establish the relationship between one entity and another, as discussed
previously.
What about all of those foreign keys? Aren
t they redundant?
Rule 6: Do not include a field if it can be calculated from other fields. A calculated field is made using the
query generator. Thus, the agent
s fee is not included in the Bookings table because it can be calculated by
query (here, five percent multiplied by the booking fee).
Search JabSto ::




Custom Search