Microsoft Office Tutorials and References
In Depth Information
DATABASE DESIGN RULES
Rule 4: Look for attributes of each entity and designate a primary key. As previously mentioned, you
should think of the entities in your database as nouns. You should then create a list of adjectives that describe
those nouns. These adjectives are the attributes that will become the table
s fields. After you have identified
fields for each table, you should check to see whether a field has unique values. If such a field exists,
designate it as the primary key field; otherwise, designate a compound primary key.
In the talent agency example, the attributes, or fields, of the Band entity are Band Name, Band Phone
Number, and Desired Fee, as shown in Figure A-1. No two bands have the same names, so the primary key
field can be Band Name. The data type of each field is shown.
BAND
Field Name
Data Type
Band Name (primary key)
Text
Band Phone Number
Text
Desired Fee
Currency
FIGURE A-1
The Band table and its fields
Two Band records are shown in Figure A-2.
Band Name (primary key)
Band Phone Number
Desired Fee
Heartbreakers
981 831 1765
$800
Lightmetal
981 831 2000
$700
FIGURE A-2
Records in the Band table
If two bands might have the same name, Band Name would not be a good primary key, so a different
unique identifier would be needed. Such situations are common. Most businesses have many types of
inventory, and duplicate names are possible. The typical solution is to assign a number to each product to use as
the primary key field. For example, a college could have more than one faculty member with the same name,
so each faculty member could be assigned an employee identification number (EIN). Similarly, banks assign a
personal identification number (PIN) for each depositor. Each automobile produced by a car manufacturer
gets a unique Vehicle Identification Number (VIN). Most businesses assign a number to each sale, called an
invoice number. (The next time you go to a grocery store, note the number on your receipt. It will be
different from the number on the next customer
s receipt.)
At this point, you might be wondering why Band Member would not be an attribute of Band. The answer is
that, although you must record each band member, you do not know in advance how many members will be in
each band. Therefore, you do not know how many fields to allocate to the Band table for members. Another
way to think about band members is that they are the agency
s employees, in effect. Databases for organizations
usually have an Employee entity. Therefore, you should create a Band Member table with the attributes
Member Name, Band Name, Instrument, and Phone. The table and its fields are shown in Figure A-3.
BAND MEMBER
Field Name
Data Type
Member Name (primary key)
Text
Band Name (foreign key)
Text
Instrument
Text
Phone
Text
FIGURE A-3
The Band Member table and its fields
Search JabSto ::




Custom Search