Microsoft Office Tutorials and References
In Depth Information
Guidelines for Designing Databases
Guidelines for Designing Databases
A database management system can be a useful tool, but only if you fi rst carefully design
the database so that it meets the needs of its users. In database design, you determine the
fi elds, tables, and relationships needed to satisfy the data and processing requirements.
When you design a database, you should follow these guidelines:
Identify all the fi elds needed to produce the required information. For example, Oren
needs information about contracts, invoices, and customers. Figure 2-1 shows the
fi elds that satisfy these information requirements.
Figure 2-1
Oren’s data requirements
ContractNum
ContractAmt
CustomerID
SigningDate
Company
InvoiceDate
FirstName
ContractType
LastName
Phone
Address
InvoicePaid
City
InvoiceNum
State
InvoiceAmt
Zip
Organize each piece of data into its smallest useful part. For example, Oren could
store each customer’s complete name in one fi eld called CustomerName instead of
using two fi elds called FirstName and LastName, as shown in Figure 2-1. However,
doing so would make it more diffi cult to work with the data. If Oren wanted to view the
records in alphabetical order by last name, he wouldn’t be able to do so with fi eld
values such as “Tom Cotter” and “Ray Yost” stored in a CustomerName fi eld. He could do
so with fi eld values such as “Cotter” and “Yost” stored separately in a LastName fi eld.
Group related fi elds into tables. For example, Oren grouped the fi elds related to
contracts into the Contract table, which you created in Tutorial 1. The fi elds related
to invoices are grouped into the Invoice table, and the fi elds related to customers are
grouped into the Customer table. Figure 2-2 shows the fi elds grouped into all three
tables for the Belmont database.
Figure 2-2
Oren’s fi elds grouped into tables
Contract table
ContractNum
CustomerID
ContractAmt
SigningDate
ContractType
Invoice table
InvoiceNum
ContractNum
InvoiceAmt
InvoiceDate
InvoicePaid
Customer table
CustomerID
Company
FirstName
LastName
Phone
Address
City
State
Zip
Determine each table’s primary key. Recall that a primary key uniquely identifi es each
record in a table. For some tables, one of the fi elds, such as a credit card number,
naturally serves the function of a primary key. For other tables, two or more fi elds might
be needed to function as the primary key. In these cases, the primary key is called a
 
Search JabSto ::




Custom Search