Supermarket : Stock Database

Supermarket : Stock Database

A supermarket will store information about the products that are for sale as a file in a database. Other information such as supplier details can be stored in other files in the database if a relational database is used.

Structure of the Products File

Here is a small exert from the products file in a supermarket stock database :

Product Code	Item Name	Stock Level	Re-Order Level	Order Quantity	Price	Category	Supplier Code
100200019292	Spicy Pizza 12"	123	100	200	Ł2.49	FR	PC
100212422323	Canned Tomatoes 400g	742	500	1000	Ł0.24	TV	BW
102313538763	Frozen Peas 1lb	41	30	50	Ł0.89	FR	FV
403244976252	Freshly Squeezed Orange 1L	32	10	25	Ł2.59	JU	JL

The purpose of each field in the file is listed below :

Product Code : This is a unique code which identifies each product. Because each product has a different product code, it is used as the key field for this file.
Item Name : A brief description of each item is stored. This can be used to identify the item and can also be printed on till receipts.
Stock Level : The quanity of the product that is currently available at the store.
Re-Order Level : The minimum quanity of the product that should be available to ensure that the store does not run out of it. If the stock level falls below this figure then more on the product should be ordered from the appropriate supplier.
Order Quantity : The quantity of this product to order from the supplier when more is needed.
Price : The price that the customer will be charged for each unit of the product he purchases.
Category : A code identifying the type of product. e.g. FR = Frozen, TV = Tinned Vegetables
Supplier Code : A code identifying which company the product is supplied by so that the product can be reordered from the correct source.

Information in the category and supplier code fields is coded to speed up data input, reduce storage space and make reports more compact.

Updating the Products File

The products file can be updated for several different reasons. The most common ones are :

Reason	How Updated
Item Sold	Whenever an item is sold the "Stock Level" will be automatically reduced by a computerised till.
Delivery	When new items are delivered to the supermarket, the "Stock Level" field of each delivered item will be increased because the supermarket will now have more of the item. This change will be made manually by the person who accepts the delivery.
Change in Details	Sometimes an item's details will change. For example the price of the item may go up or the size of the can that the product is supplied in may change. These updates will be carried out manually by a computer operator.
Stock Check	Periodically a worker will count the quantity of an item that is actually available on the shelves in the supermarket. If this differs from the "Stock Level" listed on the computer then the database field must be updated to reflect the actual stock level.

Supermarkets need up-to-date information about the goods they have sold and the goods that they currently have in stock. Therefore transaction processing systems are used, with all updates to the stock database taking place very quickly.

Other Files

The products file will be the most important file in the stock database. Other files could be used to store information such as :

File

Purpose

Possible Structure

Links To Other Files

Suppliers File

Stores information about each supplier that the company purchases items from.

Field Name

Supplier Code

Supplier Name

Supplier Address

Supplier Phone No

Linked to the product file by the "Supplier Code" field.

Categories File

Stores information about the different categories of product that the supermarket sells.

Field Name