Title: Sales and Inventory System Database Design
Description:
The main objective of the study is to develop and implement computerized sales and inventory with decision support system. The development of this project started with the proponent’s statement of the problem which includes general and specific problems that the current processes of the owner/company encountered. These problems were acquired through different data determination techniques such as observation, interviews, and surveys. Upon knowing the problems and formulating the objectives of the development, the proponents set the scope and limitations of the developed system to determine what the system would be like.
The proponents will create a module that will integrate the Point of Sales and for the availability of the products. The proponents will use MySQL that will serve as the database and querying of the tables. The proponents will also use Visual Basic for building an application for this module.
The proponents will create a module that will integrate decision support system for demand planning for the inventory of the company. This module includes scheduling of purchasing of orders from the supplier and will determine the stock levels and slow and fast-moving products.
Database Schema with description of table:
Company setup table stores information of the company such as the company name, location, contact and the tax identification number. The data stored in this table will be used to display in the header part of the receipt and other reports.
companysetup – (index, companyname, location, contact, tinno)
Predictive days table will store information about the number of days necessary for the demand planning feature of the project.
setpredictivedays – (numdays)
Adjustment table will store records about the adjusted quantity of the products.
tbladjustitemqty – (id, productid, qty, userid, dateencoded)
Audit trail table will store information about the user activities on the system.
tblaudittrail – (id, userid, action, productid, daterecorded)
Category table store records of the different classification of products.
tblcategory – (id, categoryname, description, userid, dateencoded)
Invoice table will hold the information such as the total amount of purchase, the amount tendered by the customer, change, the date and time of transaction and the cashier information.
tblinvoice – (id, userid, totalamount, discountprice, tendered, change, daterecorded)
Item raw material table is the table for the raw materials used to develop the certain product. The table includes the product information, material name and the amount. The table is connected to the product table.
tblitemrawmaterial – (id, productid, materialname, amount, userid, dateencoded)
Low stock table is a temporary table used to store and display the list of products that needs to be reorder.
tbllowstock – (productcode, productname, inventorycount)
product table stores the information about the product such as the itemcode or barcode, itemname, variant, unitid, categoryid, netprice, productcost cost, reorder level and many more.
tblproduct – (id, itemcode, itemname, variant, unitvalue, unitid, categoryid, netprice, productioncost, markuppercent, markupprice, discount, discountprice, salesprice, reorderlevel, stocksqty, expirydate, userid, dateencoded)
Product set table is a combination of two or more products. The table includes the setcode, setname, price and the amount.
tblproductset – (id, setcode, setname, totalamount, setonhand, userid, dateencoded)
Sales table is the table for storing the purchased items by the customer, it is connected to the invoice table. It consists of the invoiceid, items , quantity and the subtotal.
tblsales – (id, invoiceid, itemid, saleqty, total, userid, dateencoded)
Unit table stores the different unit of measurement of the items or prodcuts.
tblunit – (id, unitname, description, userid, dateencoded)
userlog is the table for recording the login time and logout time of each user of the system
tbluserlog – (id, userid, logintime, logouttime, daterecorded)
tbluserinfo
Table Relationship:
Features/Modules:
Item-Product Module – (recid, itemcode, itemname, variant, unitvalue, unit, category, netPrice, productioncost, markupPercent, discount, discountPrice, saleprice,reorderlevel, stocks)
Item-Raw-Material – (recid, materialname, itemcode, amount)
Price = ( (sumofrawmaterials + productioncost) + markup%) – discount
Note: discount must not greater or equal to markupPercentage
Raw Material Module – (recid, materialname, amount)
Product Set (combination of two or more products) – (recid, setcode, setname, totalamount)
Item-SetProduct – (recid, setcode, itemcode, productNetPrice)
Item Unit Module – (recid, unitname, description)
Item Category Module – (recid, categoryname, description)
Adjust Item Quantity Module – (recid, itemcode, status(add,deduct), qty, date)
Transactions:
POS Module
Invoice-OR – (recid, cashier, total, tendered, change, date, month, year)
Sales – (recid, orid, itemcode, itemname, saleqty, saleprice, discount, stotal, cashier, date, month, year)
Reports:
Inventory Report – RealTime
Sales Report (by month and year)
DSS:
Low Stock items
Demand Planning
Expiring Products
Development Tools:
Visual Basic and MySQL
