SQL Bible, Second Edition

Appendix B. The ACME Sample Database

ACME is the database for a company of the same name that sells building materials. It has 15 tables, which are described in detail in this chapter.

General Information and Business Rules

ACME has customers who buy various products. Each customer has one or more addresses — for example, billing and shipping — and one or more phone or fax numbers. Customers also have default payment terms that specify how many days they have to make a payment to get a specified discount; for example, a customer who pays within 31 days might qualify for a 2 percent discount. Customers who do not pay on time may be put on credit hold, which means they cannot order any more until their debts have been paid. Also, customers who have not bought from ACME for more than two years are marked inactive. Customers are referred to by aliases given to them (for internal company use only).

Every customer is assigned to a default salesperson who is a liaison between the customer and the company. Each salesperson has address, phone, and fax numbers, a code he or she is referred by, and some other properties. If all customers for a salesperson become inactive, the salesperson is inactivated within the system.

ACME implements a kind of multi-level marketing. It sells products to resellers, who in turn either sell the products to a lower-level reseller or directly to a customer. That means any of ACME's customers could either be a reseller (the one who buys and resells products) or a ...