Database Answers Inventory Prices

Home Ask a Question Careers Contact us Data Models First Timers Search Site Map
Inventory Control and Pricing
Back to the Data Model
A. The requirement is to design a Database for Inventory Control and Pricing,
Raphael has defined by the Requirements in the following terms :-

A national electronics retail company has decided to design and implement a database system to manage their sales and stock. 
The business has around 1000 stores distributed across the UK. 
Each store stocks around 1500 products from a nation product base of some 4000 stock items. 

Maximum price are fixed nationally although local managers may apply local discounts to products. 

Managers are also able to select products that they deem suitable for their local area. 
A typical store has a range of around 1500 products selected by managers from the national product base of 4000 stock items. 

With respect to stock management they would like to have an automated stock ordering system that would order stock based on a 
reorder level (set by the local manager). 
At present each local store holds an amount of stock for each product. 

They would like stock to be reordered from head office automatically once a minimum level has been reached. 

Each local store currently records each product's stock level and a reorder level for each product. 

Head office keeps national stock for each product which are dispatched to local stores as required. 

Once an order is received at head office the stock is booked and dispatched on the Thursday of each week. 

The following users of the system have been already identified:-
  • Sales agents They require a login that provides POS facilities to sales staff within stores. The base requirement is that they are able to process customer sales transactions. Sales agents also receive a 1% commission on their sales.
  • Local Managers A store manager needs to be able to view stock and sales information for his store. He can also manage stock and discount local products. Specifically he can also set and edit stock reorder levels for the various products that he stocks. He can also select which products to stock from a national product base. Finally the sales manager wants to be able to review the performance of his staff via sales and commission reports.
  • Area Managers The area manager is able to view historical sales information for a collection of stores within his area (notionally this is around 15 stores per area manager).
  • National Stock Manager The national stock manager constructs and maintains the national product base. The national product base is a list of stock items that are available to local stores. The national stock manager is responsible for setting the national maximum price for stock items and for maintaining national stock levels. B. Things of Interest :- B.1 Areas B.2 Products B.3 Staff C. How are they related ? C.1 An Area has one and only one Area Manager. C.2

  • Barry Williams
    27th. February 2009
    Principal Consultant
    Database Answers


    Home Ask a Question Careers Contact us Data Models First Timers Search Site Map