Microsoft Office Tutorials and References
In Depth Information
Guidelines for Designing Databases
composite key . For example, a school grade table would use a combination of student
number and course code to serve as the primary key. For a third category of tables, no
single fi eld or combination of fi elds can uniquely identify a record in a table. In these
cases, you need to add a fi eld whose sole purpose is to serve as the table’s primary key.
For Oren’s tables, ContractNum is the primary key for the Contract table, InvoiceNum
is the primary key for the Invoice table, and CustomerID is the primary key for the
Customer table.
Include a common fi eld in related tables. You use the common fi eld to connect one
table logically with another table. For example, Oren’s Contract and Customer tables
include the CustomerID fi eld as a common fi eld. Recall that when you include the
primary key from one table as a fi eld in a second table to form a relationship, the
fi eld is called a foreign key in the second table; therefore, the CustomerID fi eld is a
foreign key in the Contract table. With this common fi eld, Oren can fi nd all contracts
for a particular customer; he can use the CustomerID value for a customer and search
the Contract table for all records with that CustomerID value. Likewise, he can
determine which customer has a particular contract by searching the Customer table to
fi nd the one record with the same CustomerID value as the corresponding value in
the Contract table. Similarly, the ContractNum fi eld is a common fi eld, serving as the
primary key in the Contract table and a foreign key in the Invoice table.
Avoid data redundancy. When you store the same data in more than one place, data
redundancy occurs. With the exception of common fi elds to connect tables, you
should avoid data redundancy because it wastes storage space and can cause
inconsistencies. An inconsistency would exist, for example, if you type a fi eld value one way in
one table and a different way in the same table or in a second table. Figure 2-3, which
contains portions of potential data stored in the Customer and Contract tables, shows
an example of incorrect database design that has data redundancy in the Contract
table. In Figure 2-3, the Company fi eld in the Contract table is redundant, and one
value for this fi eld was entered incorrectly, in three different ways.
Figure 2-3
Incorrect database design with data redundancy
Customer table
CustomerID
Company
FirstName
LastName
11067
Blossom Day Care Center
Christina
Garrett
11068
11070
11071
11072
Grand Rapids Housing Authority
Legacy Companies, LTD.
Blue Star Mini Golf
Sierra Investment Company
Jessica
Ropiak
Michael
Faraci
Vanetta
Walker
Rodrigo
Valencia
data redundancy
Contract table
ContractNum
CustomerID
Company
ContractAmt
SigningDate
3023
3040
3042
3073
3081
3085
3099
11070
Legacy Company
$39,000.00
$38,500.00
$48,500.00
$205,000.00
$21,000.00
$30,800.00
$6,500.00
3/22/2013
7/27/2013
6/3/2013
5/5/2014
5/10/2014
12/28/2013
7/25/2014
11068
Grand Rapids Housing Authority
11070
Legacies Co. Limited
Sierra Investment Company
Blue Star Mini Golf
Legacy Corp. Ltd
Blossom Day Care Center
inconsistent data
11072
11071
11070
11067
 
Search JabSto ::




Custom Search