Collective Supermarket Owners

Supermarket System Specification

Jan 16, 1996

The Problem:

Collective Supermarket Owners (CSO) is interested in the design and setup of a database system that would control local store inventory, and cover the ordering of stock from CSO's central warehouse. The warehouse itself will require the ability to respond to the incoming requests from each store, fulfill these requests, and invoice the appropriate store accordingly. Every store in our association is organized similarly, and runs in much the same way as the others. Hence, one in-store system should be developed (of which copies can be placed in each individual store), along with one system for control of our central warehouse.

Requirements:

SUPERMARKET

Initially, each CSO supermarket has a variety of goods sitting on store shelves. These goods must all be contained within some kind of database system. New shipments of goods should be entered into this system as they arrive. When a customer purchases an item, the current inventory count should be decreased by one. CSO requires that a product will be automatically placed on a department ordering list by the system when the minimum stock number (point of order) is reached. Managers should also be able to adjust the defaults of an order if necessary. For example, in the case of a promotion of a certain product, the manager may wish to order a larger than normal quantity. Any adjustments made to the order form should constitute a temporary change in the amount of stock ordered, and should not affect the regular minimum or maximum default values contained in the database. This order should not be sent to the warehouse until permission has been obtained from the department or head manager.

Managers should also have the ability to cancel an order altogether, and to change the supplier. As new products hit the market, they should be able to add a new item to the database. Similarly, managers should be able to delete an item from the database in the case that it may not be selling, or is discontinued by the manufacturer.

Some mechanism allowing the manager to adjust the information contained in the database will be a beneficial tool. This mechanism should include among other things the ability to change the product point of order value, change the product maximum order value, transfer items between departments, and add and delete departments as necessary. Changing current inventory stock is a function that should be given to everyone in some form. For example, cashiers will automatically decrease the item count when a product is sold to a consumer. Stockers and/or managers will also need the ability to increase current stock count as new stock arrives.

The order form should contain the default amount of stock to be ordered for each item, and should be able to be adjusted by the manager under special circumstances (such as a promo). When the manager gives his/her OK to the system, the system should send the order to the warehouse. The order form must contain the order number, supermarket ID, address, department and date, as well as the list of item codes, item bar codes, item descriptions, and quantities ordered. The form should also include the name of the manager who has submitted the order.

An important concern of CSO is products on order. If some product has been ordered, and it takes a while for that product to arrive, we do not want the same product ordered several days in a row. The system must contain some sort of list of items on order. As the items received come in, they should be taken off this list.

SECURITY

Essentially CSO requires some form of a hierarchical security system which should consist of three categories. Level one access should be for the Head Manager. The Head Manager should have access to everything in the system, and should be able to add new people to the system, and delete them when necessary. It should also be possible for the Head Manager to easily switch people between different security levels, and to add/delete departments as required. Level two access should include privileges for Department Managers. These managers should have complete access to everything in their department, including the placing of orders and allocation of access for cashiers/stockers in their departments. However, they should not have access to any other department functions (ie, the Produce Manager should not have access to the Meats Department). Level three access is intended for cashiers/stockers. Basically they should be allowed to change the amount of stock in the current inventory, either through the selling of an item to a customer, or in the stocking of shelves with new inventory.

ARCHIVE FILE

CSO is interested in maintaining a list of all orders, and some form of an audit file. The audit file should contain a record of all adjustments made to the database, time of adjustment, date, and the person who made the adjustment. This will help add to security and will be beneficial to our record keeping.

WAREHOUSE

The warehouse should, upon receiving an order from a store, proceed to fill the request. A laiding form will need to be printed, which will accompany the shipment to the store, and should include the following information: (from) address of supplier, (to) supermarket ID, supermarket address, department, manager's name and order number, as well as a list of item descriptions, item codes, amount requested, and amount sent. When the shipment reaches the store, the manager initials the bill of laiding, confirming that the shipment has arrived. This form is then returned to the warehouse where an invoice must be generated. The invoice, which will be sent to the store should be in some form that can either be printed (and subsequently mailed), or simply sent via email to the specific store. The invoice should include the same information as the bill of laiding, along with the prices of each item, and a price total. Although not a top priority, CSO would also be open to the possibility of setting up some form of an electronic confirmation of shipments received. This electronic confirmation would then trigger the generation of the invoice form at the warehouse.

DATABASE

The CSO in-store database should be structured to include the following information for each product that we carry:

Assumptions:

CSO assumes that there are no expiry dates on any items in our stores and warehouse, and that our warehouse will never run out of inventory. We also assume that each item has a pre-defined minimum (point of order). Inventory should be ordered on the basis that when an item falls below its minimum point of order value, it should order up to the amount of the maximum value contained in the database (for that item), less the amount of stock sitting on the shelf. This will allow for the ordering of more stock in the case of a total sell-out, and the ordering of less stock when we are selling items on a slower basis. CSO also assumes that all items must be ordered by the case (which may contain 12, 24 or 36 items each).

Shipments from the warehouse are not always guaranteed to arrive the same day or next day ordered, but can be assumed to arrive at some time in the future. Deliveries will not always arrive together, or when expected.