Microsoft Office Tutorials and References
In Depth Information
DATABASE DESIGN RULES
Note in Figure A-3 that the phone number is classified as a Text data type because the field values will
not be used in an arithmetic computation. The benefit is that Text data type values take up fewer bytes than
Numerical or Currency data type values; therefore, the file uses less storage space. You should also use the
Text data type for number values such as zip codes, Social Security numbers, and so on.
Five records in the Band Member table are shown in Figure A-4.
Member Name (primary key)
Band Name
Instrument
Phone
Pete Goff
Heartbreakers
Guitar
981 444 1111
Joe Goff
Heartbreakers
Vocals
981 444 1234
Sue Smith
Heartbreakers
Keyboard
981 555 1199
Joe Jackson
Lightmetal
Sax
981 888 1654
Sue Hoopes
Lightmetal
Piano
981 888 1765
FIGURE A-4
Records in the Band Member table
You can include Instrument as a field in the Band Member table because the agent records only one
instrument for each band member. Thus, you can use the instrument as a way to describe a band member,
much like the phone number is part of the description. Member Name can be the primary key because you
can assume that no two members in any band have the same name. Alternatively, Phone could be the
primary key, assuming that no two members share a telephone. Or, you could assign an ID number to each band
member, which would create a unique identifier for each musician the agency handled.
You might ask why Band Name is included in the Band Member table. The commonsense reason is that
you did not include the Member Name in the Band table. You must relate bands and members somewhere,
and the Band Member table is the place to do it.
To think about this relationship in another way, consider the cardinality of the relationship between Band
and Band Member. It is a one-to-many relationship: one band has many members, but each member is in just
one band. You establish such a relationship in the database by using the primary key field of one table as a
foreign key in the other table. In Band Member, the foreign key Band Name is used to establish the
relationship between the member and his or her band.
The attributes of the Club entity are Club Name, Address, Contact Name, Club Phone Number, Preferred
Fee, and Feed Band?. The Club table can define the Club entity, as shown in Figure A-5.
CLUB
Field Name
Data Type
Club Name (primary key)
Text
Address
Text
Contact Name
Text
Club Phone Number
Text
Preferred Fee
Currency
Feed Band?
Yes/No
FIGURE A-5
The Club table and its fields
Search JabSto ::




Custom Search