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.
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 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.
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.
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.