Microsoft Office Tutorials and References
In Depth Information
Designing a Database
As you look at these two tables, you soon realize that one customer can
make more than one purchase. What happens when a customer buys
something else, perhaps on a different date?
Book I
Chapter 3
Combining customer information with purchase information won’t work.
Leave information about the customer in the Customers table (all the facts
about the customer that don’t change from one purchase to the next), and
move information about a specific purchase into a separate Orders table,
like this:
Customers
First Name
Last Name
Street Address
City
State/Province
Zip or Postcode
Phone Number
Email Address
Tax Exempt (Yes/No)
Orders
Customer First Name
Customer Last Name
Purchase Date
Product 1
Product 2
Product 3
Shipping Cost
Sales Tax
Total Price
Payment Method
Credit Card Number
Credit Card Exp. Date
Check Number
But wait — what if the customer buys more than three items at a time? (We
usually do.) If you own the store, you don’t want to put an arbitrary limit on
how many items your customers can buy. (Limit your profit for the sake of
your database? In a word, nope.) Any time your database design includes a
bunch of fields that store essentially the same kind of information (such as
Product 1, Product 2, and Product 3), something is wrong. An order
can consist of zero, one, or many books. Does that sound familiar? Yes, a
oneto-many relationship exists between an order and the items in that order, so
you need to make a separate table for the individual items, like this:
Search JabSto ::




Custom Search