Designing and Developing the Inventory System

An interactive blog for accountingdes.com website. Your preferred choice of free accounting software provider.

As discussed earlier, the Product Table (refer snapshot above) was created to keep track on each product item’s name, description, supplier code and its account set code. This table is still not in perfect form, as Dave, would also want to keep track on the movement of each of the product sold, the costing method applied on each product items and the pricing of each of its product items. We will again, follow the step-by-step normalization process to further identify key elements that should be broken down further away from the Product Table.

First Normal Form

We know that, not all products in MaxCorp are purchased from the same source of supplier, it could be a product sourced from different supplier or a supplier could also be supplying MaxCorp more than one product type. It is logical, at this point, to create a separate table for supplier, referencing the Product Table via a foreign key named Cred_ID_VC, being a primary key assigned in the Creditor Table.

Second Normal Form

At this stage, we have further identified another key element, the product category, as MaxCorp will also need to group its sale by category, in order to better analyze the marketability and profitability of each product type. We branch out the Pdt_Category_VC field, to create a separate table, named, Product Table_Category, assigning the field Pdt_Category_VC, as the primary key, having linked to the Product Table, as a foreign key.

Third Normal Form

We have not created any field to capture the quantity movement and balance of each product items. Create a table named, Stock_Movement Table to capture the physical movement of each product sold by MaxCorp, and a table named, Stock_Balance Table to monitor the current quantity balance of each product items.

Designing Stock Movement Table
 

This table (refer snapshot above) would keep track of each product movement in MaxCorp warehouse. A row of records would be inserted into this table when MaxCorp delivers products to its customer, receiving incoming goods from its supplier, or even transferring goods between different warehouse locations. In our table, we only maintain one location for storing MaxCorp’s goods, and should MaxCorp expands its business further in future, it may establish branches that would require more than one warehouse to store its product, then MaxCorp may need to create a separate table just to store warehousing information. The Descrip_VC field would accommodate the delivery note and goods receive note numbering for each product items moving in and out from MaxCorp’s warehouse.

Designing Stock Balance Table

This table will hold the latest balance of each product items, after accumulating or deducting the previous balance of each product items, to illustrate this further, assuming that, MaxCorp purchase 2 units of item A at $1.50 each. The system would record this information in this table after updating the Stock_Movement Table. When item A is sold to a customer, the system would then insert a new record in the Stock_Movement Table and will update the balance of item A in Stock_Balance Table simultaneously. For each update in the Stock_Balance Table, the system will first identify the matching product item in the Stock_Balance Table, if it exist, the system would update its quantity and unit price, by replacing its current quantity and unit price, and if, it is a new product item, a new record would then be inserted. This table plays a significant influence on product pricing. We will have a better understanding, as we discuss further on the costing method field created in the Product AccountSet Table.

Designing Product Account Setting Table

The Cost_Method_VC field plays an important role in determining how a product item should be priced, when a product is sold to a customer. At present, we have two alternative in pricing MaxCorp product, the first-in-first-out (FIFO) method and the cost average method. Applying the first method, would price MaxCorp’s goods on the oldest price first, as for the latter, at an average price. The quantity and pricing of each product item is referenced to the Stock_Balance Table.

Besides determining the pricing method of its out-going goods, Dave would also want the double-entries to flow into the Journal Table, to record the cost of the out-going goods and the cost of purchasing the goods from its suppliers. To achieve this, we need to assign a default inventory account code to record the cost of in-coming and out-going of each product item. In the Stock_IN field, a clearing account (Payable_IN) would need to be assigned to place its purchase cost temporarily, while awaiting billing from respective supplier and a clearing account (Shipment_IN) is assigned to capture its shipment cost, while pending subsequent billing to its customer.

Designing Product Category Table

This table contains three important fields, with the last field requiring Dave, to assign two default account code for effecting the double-entries into the Journal Table, each time a user raises an invoice to its customer. We will assign the field Pdt_Category_VC as the primary key, having reference to the Product Table. Firstly, under each product category, Dave would need to assign a default account code for the cost of goods sold amount, to record the pricing cost of its product, secondly, a default account code to record the sales amount of each invoices raised from the client application.

Next. We will learn the steps in designing and developing the purchase table which will keep track of our purchasing transaction and payment to our supplier, stay tuned..