Ms Office Tune Up

A sales database that stores the products, customers, and orders for a company that sells something is one of the most common databases. In fact, this pattern turns up so often that it’s worth looking at a quick example. As you’ll see, there are a few basic principles that apply to every sales-driven business, whether the business is selling collectible books or discount pharmaceuticals.

In this example, you’ll meet Boutique Fudge, a mail-order company that serves decadent treats to a large audience of chocolate-crazed customers. Their daring chefs are always innovating, and they need a better way to manage their ever-growing catalog of chocolate goodness. They also need a way to keep track of customers and the orders they make.

The product catalog and customer list

Even though you don’t know much about Boutique Fudge, you can already think of a few key tables that it’ll need. In order to put anything up for sale, they should have the following tables:

• Products lists the sinful chocolate delicacies they have for sale. This table records the name, description, and price of each item available. A few optional details also make sensefor example, why not keep track of the current stock using two numeric fields (UnitsInStock and UnitsOnOrder) and a Yes/No field (named Discontinued) to identify products that aren’t available any longer?

  ---

  Note: In many databases, you can’t delete old information. A company like Boutique Fudge can’t simply delete old products from their catalogs, because these products might be linked to old orders. Also, it makes sense to keep historical information to allow data analysis. (Boutique Fudge could use a query to uncover the top selling products in 1999, and check if declining cocoa levels are linked to lessening sales). For this reason, you need tricks like the Discontinued field. When you list the products for sale, you can leave out all the discontinued ones, using the filtering skills you picked up in Section 3.2.2.
  

  ---

• ProductCategories splits products into a few descriptive groups. That way, customers can browse just the products in the category they want (whether it’s Beverages, Candies, Chocolate, or Personalized Choco-wear.)

• Customers holds the list of chocoholics that have signed up to make an order. You need all the customary information here, like customer names, shipping information, and billing information.

So far, there’s only one relationship at work: a one-to-many relationship between ProductCategories and Products. Figure 5-20 shows this design.

Figure 5-20. A product (like Chocolate Jasmine Tea) can be placed in one category (like Beverages), but a single category holds many products.

Popularity: 2% [?]