Collective Supermarket Owners
Supermarket System Specification
Jan 16, 1996
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.
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
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.
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.
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
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.
The CSO in-store database should be structured to include the following
information for each product that we carry:
- item description
- bar code
- item code
- item location in store
- point of order (stock minimum)
- maximum (amount to order)
- current stock
- date ordered
- quantity ordered - (maximum - current stock)
- date received
- quantity received
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.