General Information and Business Rules

ACME has customers who buy different products. Each customer has one or more addresses — for example, billing and shipping — and one or more phone and/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 in time may be put on credit hold, which means they cannot order any more until their debts have been paid. Also, customers who did not buy from ACME for over 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 supplier (the one from whom products are bought), or both.

ACME sells multiple products. Each product has a price, description, brand, weight, and some other characteristics.

Customers can order products. When a customer orders more than one product at a time, all order items are logically grouped within one order. Order stores general information, such as order number, default salesperson and customer information, quantities ordered, dates, invoice number, and so on. The actual shipped quantity can differ from the ordered one (for example, because of a lack of sufficient inventory).

Some fields (payment terms, salesman) are populated by default when customer is selected, but can later be changed. Some other fields have to be populated manually. Order and invoice numbers are populated automatically and cannot be modified. Some orders are eligible for volume discount based on total order price. For example, all orders with total amount between $15,000 and $20,000 are eligible for a 3 percent discount.

When a new order is created, the COMPLETE status is assigned to it. It is now ready to be shipped to the customer after which the status is to be changed to SHIPPED. A sales department clerk creates an invoice for this order and changes its status to INVOICED. The order can be canceled by the customer; the status is then changes to CANCELED.

The database also contains specific shipping information — for example, bill of lading number, total number of cases shipped, trailer number, freight terms, dates, and so on. Usually one shipment contains one order, but special situations when one order is distributed between more than one shipment, or one shipment can contain more than one order, can also be handled.