Microsoft Office Tutorials and References
In Depth Information
DATABASE DESIGN RULES
Two records in the Club table are shown in Figure A-6.
1 Duce St.
981 444 8877
99 Duce St.
981 555 0011
Records in the Club table
You might wonder why Bands Booked into Club (or a similar name) is not an attribute of the Club table.
There are two reasons. First, you do not know in advance how many bookings a club will have, so the value
cannot be an attribute. Second, Bookings is the agency
s revenue-generating transaction, an event entity, and
you need a table for that business transaction. Consider the booking transaction next.
You know that the talent agent books a certain band into a certain club for a specific fee on a certain
date, starting and ending at a specific time. From that information, you can see that the attributes of the
Bookings entity are Band Name, Club Name, Date, Start Time, End Time, and Fee. The Bookings table and its
fields are shown in Figure A-7.
The Bookings table and its fields — and no designation of a primary key
Some records in the Bookings table are shown in Figure A-8.
Records in the Bookings table
Note that no single field is guaranteed to have unique values, because each band is likely to be booked
many times and each club might be used many times. Furthermore, each date and time can appear more
than once. Thus, no one field can be the primary key.
If a table does not have a single primary key field, you can make a compound primary key whose field
values will be unique when taken together. Because a band can be in only one place at a time, one possible
solution is to create a compound key from the Band Name, Date, and Start Time fields. An alternative
solution is to create a compound primary key from the Club Name, Date, and Start Time fields.
To avoid having a compound key, you could create a field called Booking Number. Each booking would
then have its own unique number, similar to an invoice number.