Microsoft Office Tutorials and References
In Depth Information
DATABASE DESIGN RULES
Two records in the Club table are shown in Figure A-6.
Club Name
(primary key)
Club Phone
Number
Address
Contact Name
Preferred Fee
Feed Band?
East End
1 Duce St.
Al Pots
981 444 8877
$600
Yes
West End
99 Duce St.
Val Dots
981 555 0011
$650
No
FIGURE A-6
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.
BOOKINGS
Field Name
Data Type
Band Name
Text
Club Name
Text
Date
Date/Time
Start Time
Date/Time
End Time
Date/Time
Fee
Currency
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.
Band Name
Club Name
Date
Start Time
End Time
Fee
Heartbreakers
East End
11/21/10
21:30
23:30
$800
Heartbreakers
East End
11/22/10
21:00
23:30
$750
Heartbreakers
West End
11/28/10
19:00
21:00
$500
Lightmetal
East End
11/21/10
18:00
20:00
$700
Lightmetal
West End
11/22/10
19:00
21:00
$750
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.
Search JabSto ::




Custom Search