Microsoft Office Tutorials and References
In Depth Information
Designing a Database
Customer First Name
Customer Last Name
Total Product Cost
Credit Card Number
Credit Card Exp. Date
Now each time a customer places an order or comes into your store to make
a purchase, you create one record in the Customers table (if this customer
hasn’t bought from you before), one record in the Orders table, and one
record for each item purchased in the Order Details table. The Order Details
table has room to store the quantity of the item in case the customer wants
more than one of something. You should also store the selling price of the
product. Access can calculate the cost of that quantity of each product
(price × quantity), so you don’t need to store that information.
The following are really good reasons not to store multiple fields (such as
Product 1, Product 2, and Product 3) in one table and to create a
separate table instead:
✦ You can’t anticipate the right number of fields. If someone buys more
than three things (as in this example), you have to create a separate
order and enter everything twice.
✦ You can’t analyze the information later. What if you sell books, and
you want to see a list of everyone who bought the last Honor Harrington
book so that you can notify those customers that the next book in the
series is coming out? If you have multiple fields for this information,
your query needs to look for orders that contain an Honor Harrington
book in Product 1 or Product 2 or Product 3. What a pain.
We don’t want to drive this point into the ground, but creating multiple,
identical fields is a problem that many first-time database designers make
for themselves. Be good to yourself: Don’t do it!