Point of Sale System Database Design

Point of Sale System Database Design

This article will list down the tables needed to develop a Point of Sale System. iNetTutor.com allows you to copy the database design and modify it based on your requirement, a LIKE and SHARE  will be greatly appreciated.

tblproduct (product_id, produce_code, product_name, unit_id, category_id, unit_in_stock, unit_price, discount_percentage, reorder_level, user_id)

Table Description: The list of products of the store will be recorded and stored in the tblproduct. The table has 10 columns; (1) product_id is the primary key of the table, it serves as the unique value of the table, (2) product_code is for the barcode of the product, (3) product_name is for the name of the item or product, (4) unit_id is a foreign key that links to the tblunit table, (5) category_id is another foreign key that connects to the tblcategory table, (6) unit_in_stock is the quantity of items available in the inventory, (7) unit_price is the price or amount per unit, (8) discount_percentage is the discount of product in percent, (9) reorder_level is the number that will notify the system if the item or products needs to be reorder, (10) user_id refers to the user who encoded the item information.

CREATE SQL Statement:

CREATE TABLE IF NOT EXISTS `tblproduct` (
`product_id` int(11) NOT NULL AUTO_INCREMENT,
`produce_code` varchar(25) NOT NULL,
`product_name` varchar(50) NOT NULL,
`unit_id` int(11) NOT NULL,
`category_id` int(11) NOT NULL,
`unit_in_stock` float NOT NULL,
`unit_price` float NOT NULL,
`discount_percentage` float NOT NULL,
`reorder_level` float NOT NULL,
`user_id` int(11) NOT NULL,
PRIMARY KEY (`product_id`),
KEY `unit_id` (`unit_id`,`category_id`,`user_id`),
KEY `category_id` (`category_id`),
KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblproductunit (unit_id, unit_name)

Table Description: unit refers to the unit of measurement that is used as a standard for measurement of the same kind of quantity. The table has 2 attribues; (1) unit_id is the primary key of the table, (2) unit_name or the name of the unit.

CREATE SQL Statement:

CREATE TABLE IF NOT EXISTS `tblproductunit` (
`unit_id` int(11) NOT NULL AUTO_INCREMENT,
`unit_name` varchar(15) NOT NULL,
PRIMARY KEY (`unit_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tlbproductcategory (category_id, category_name)

Table Description: products are grouped according to their type and the type of products will be encoded and stored in the tlbproductcategory. The table has 2 columns; (1) category_id is the primary key or unique key of the table, (2) category_name or the name of the category.

CREATE SQL Statement:

CREATE TABLE IF NOT EXISTS `tblproductcategory` (
`category_id` int(11) NOT NULL AUTO_INCREMENT,
`category_name` varchar(25) NOT NULL,
PRIMARY KEY (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblcustomer (customer_id, customer_code, customer_name, contact, address)

Table Description: customer information will be encoded and stored in the tblcustomer table, it has 5 attributes; (1) the primary key of the table is the customer_id column, (2) customer_code refers to the barcode that will be assigned to the customer, (3) customer_name is the full name of the customer, (4) contact is to the contact number or mobile number of the customer and, (5) address is to the complete address of the customer.

CREATE SQL Statement:

CREATE TABLE IF NOT EXISTS `tblcustomer` (
`customer_id` int(11) NOT NULL AUTO_INCREMENT,
`customer_code` varchar(25) NOT NULL,
`customer_name` varchar(50) NOT NULL,
`contact` varchar(15) NOT NULL,
`address` varchar(100) NOT NULL,
PRIMARY KEY (`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblsales (sales_id, invoice_id, product_id, quantity, unit_price, sub_total)

Table Description: tblsales is the table where the list of items or products bought by the customers will be stored. The said table has 6 columns; (1) sales_id is the primary key of the table and it is usually the unique key which means that no duplicate value will be stored in this column, (2) invoice_id is a foreign key that links to the tblinvoice table, (3) product_id is also a foreign key that connects to the tblproduct table, (4) quantity refers to the number of items bought by the customer, (5) unit_price is the price per item, and (6) sub_total is equals to the number of quantity multiply by the unit price.

CREATE SQL Statement:

CREATE TABLE IF NOT EXISTS `tblsales` (
`sales_id` int(11) NOT NULL AUTO_INCREMENT,
`invoice_id` int(11) NOT NULL,
`product_id` int(11) NOT NULL,
`quantity` float NOT NULL,
`unit_price` float NOT NULL,
`sub_total` float NOT NULL,
PRIMARY KEY (`sales_id`),
KEY `invoice_id` (`invoice_id`,`product_id`),
KEY `product_id` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblsupplier (supplier_id, supplier_code, supplier_name, supplier_contact, supplier_address, supplier_email)

Table Description: The list of suppliers that provides the different items for the store are also being recorded in the system for future references and report generation and it is stored in the tblsupplier. The table has 6 columns; (1) supplier_id is the primary key and it is usually auto-generated by the database which is set to auto_increment, (2) supplier_code serves as the id number that the stores uses to identify each customer, (3) supplier_name refers to the name of the supplier, (4) supplier_contact is the contact information or contact number of the supplier, (5) supplier_address states the complete address of the supplier or company, (6) supplier_email stores the email address of the supplier or company.

CREATE SQL Statement:

CREATE TABLE IF NOT EXISTS `tblsupplier` (
`supplier_id` int(11) NOT NULL AUTO_INCREMENT,
`supplier_code` varchar(15) NOT NULL,
`supplier_name` varchar(50) NOT NULL,
`supplier_contact` varchar(15) NOT NULL,
`supplier_address` varchar(100) NOT NULL,
`supplier_email` varchar(50) NOT NULL,
PRIMARY KEY (`supplier_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblinvoice (invoice_id, customer_id, payment_type, total_amount, amount_tendered, bank_account_name, bank_account_number, date_recorded, user_id)

Table Description: An invoice is a commercial document that itemizes and records a transaction between a buyer and a seller. The tblinvoice has 9 attributes; (1) invoice_id is the primay key of the table, (2) customer_id is a foreign key that links to the tblcustomer table, (3) payment_type refers to the payment method which is cash or credit, (4) total_amount the amount that the customer needs to pay, (5) amount_tendered is the payment made by the customer, (6) bank_account_name the bank name of the cardholder, (7) bank_account_number is the account number stated in the card, (8) date_recorded the date of transaction, (9) user_id is a foreign key that links to the tbluser, it refers to the user who conducted the transaction.

CREATE SQL Statement:

CREATE TABLE IF NOT EXISTS `tblinvoice` (
`invoice_id` int(11) NOT NULL AUTO_INCREMENT,
`customer_id` int(11) NOT NULL,
`payment_type` int(1) NOT NULL,
`total_amount` float NOT NULL,
`amount_tendered` float NOT NULL,
`bank_account_name` varchar(50) NOT NULL,
`bank_account_number` varchar(25) NOT NULL,
`date_recorded` date NOT NULL,
`user_id` int(11) NOT NULL,
PRIMARY KEY (`invoice_id`),
KEY `customer_id` (`customer_id`,`user_id`),
KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblpurchaseorder (purchase_order_id, product_id, quantity, unit_price, sub_total, supplier_id, order_date, user_id)

Table Description: the store needs to replenish its products and it needs to request or order to its supplier. The table that will hold the record of the purchase orders is the tblpurchaseorder. The table has 8 columns; (1) purchase_order_id is the primary key of the table, (2) product_id is a foreign key that links to the tblproduct, (3) quantity refers to the number of items to be order, (4) unit_price is the price per item of the product, (5) sub_total is equal to the quantity time the number of quantity, (6) supplier_id refers to the supplier who will provide the item, (7) the date of process is the order_date, and lastly the (8) user_id is a foreign key that refers to the user who processed the purchase order.

CREATE SQL Statement:

CREATE TABLE IF NOT EXISTS `tblpurchaseorder` (
`purchase_order_id` int(11) NOT NULL AUTO_INCREMENT,
`product_id` int(11) NOT NULL,
`quantity` float NOT NULL,
`unit_price` float NOT NULL,
`sub_total` float NOT NULL,
`supplier_id` int(11) NOT NULL,
`order_date` date NOT NULL,
`user_id` int(11) NOT NULL,
PRIMARY KEY (`purchase_order_id`),
KEY `product_id` (`product_id`,`supplier_id`,`user_id`),
KEY `user_id` (`user_id`),
KEY `supplier_id` (`supplier_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblreceiveproduct (receive_product_id, product_id, quantity, unit_price, sub_total, supplier_id, received_date, user_id)

Table Description: after the purchase order, the store will wait for the supplier to deliver the products, once delivered the information will be encoded and stored in the tblreceiveproduct. The table has 8 attributes; 1) receive_product_id is the primary key of the table, (2) product_id is a foreign key that links to the tblproduct, (3) quantity refers to the number of items to be order, (4) unit_price is the price per item of the product, (5) sub_total is equal to the quantity time the number of quantity, (6) supplier_id refers to the supplier who will provide the item, (7) received_date refers to the delivery date, and lastly the (8) user_id is a foreign key that refers to the user who received the delivery.

CREATE SQL Statement:

CREATE TABLE IF NOT EXISTS `tblreceiveproduct` (
`receive_product_id` int(11) NOT NULL AUTO_INCREMENT,
`product_id` int(11) NOT NULL,
`quantity` float NOT NULL,
`unit_price` float NOT NULL,
`sub_total` float NOT NULL,
`supplier_id` int(11) NOT NULL,
`received_date` date NOT NULL,
`user_id` int(11) NOT NULL,
PRIMARY KEY (`receive_product_id`),
KEY `product_id` (`product_id`,`supplier_id`,`user_id`),
KEY `user_id` (`user_id`),
KEY `supplier_id` (`supplier_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tbluser (user_id, username, password, fullname, designation, contact, account_type)

Table Description: the system can be access by three user groups namely the administrator, cashier and inventory officer. Information of the user will be stored in the tbluser table and it has 7 attributes; (1) user_id is the foreign key of the table, (2) username and (3) password is the combination used to access the system, (4) fullname stores the complete name of the user, (5) designation refers to the position of the user in the company, (6) contact is to the contact number of the user, (7) account_type refers to the access type of the user to the system (admin has full access, cashier is only for POS module, and inventory officer for purchase and receiving of items.)

CREATE SQL Statement:

CREATE TABLE IF NOT EXISTS `tbluser` (
`user_id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(30) NOT NULL,
`password` varchar(30) NOT NULL,
`fullname` varchar(50) NOT NULL,
`designation` int(1) NOT NULL,
`contact` varchar(15) NOT NULL,
`account_type` int(1) NOT NULL,
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Download .sql file

Constraints for dumped tables

—
— Constraints for table `tblinvoice`
—
ALTER TABLE `tblinvoice`
ADD CONSTRAINT `tblinvoice_ibfk_2` FOREIGN KEY (`customer_id`) REFERENCES `tblcustomer` (`customer_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblinvoice_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbluser` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;

—
— Constraints for table `tblproduct`
—
ALTER TABLE `tblproduct`
ADD CONSTRAINT `tblproduct_ibfk_3` FOREIGN KEY (`user_id`) REFERENCES `tbluser` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblproduct_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `tlbproductcategory` (`category_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblproduct_ibfk_2` FOREIGN KEY (`unit_id`) REFERENCES `tblproductunit` (`unit_id`) ON DELETE CASCADE ON UPDATE CASCADE;

—
— Constraints for table `tblpurchaseorder`
—
ALTER TABLE `tblpurchaseorder`
ADD CONSTRAINT `tblpurchaseorder_ibfk_3` FOREIGN KEY (`product_id`) REFERENCES `tblproduct` (`product_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblpurchaseorder_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbluser` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblpurchaseorder_ibfk_2` FOREIGN KEY (`supplier_id`) REFERENCES `tblsupplier` (`supplier_id`) ON DELETE CASCADE ON UPDATE CASCADE;

—
— Constraints for table `tblreceiveproduct`
—
ALTER TABLE `tblreceiveproduct`
ADD CONSTRAINT `tblreceiveproduct_ibfk_3` FOREIGN KEY (`product_id`) REFERENCES `tblproduct` (`product_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblreceiveproduct_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbluser` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblreceiveproduct_ibfk_2` FOREIGN KEY (`supplier_id`) REFERENCES `tblsupplier` (`supplier_id`) ON DELETE CASCADE ON UPDATE CASCADE;

—
— Constraints for table `tblsales`
—
ALTER TABLE `tblsales`
ADD CONSTRAINT `tblsales_ibfk_2` FOREIGN KEY (`invoice_id`) REFERENCES `tblinvoice` (`invoice_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblsales_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `tblproduct` (`product_id`) ON DELETE CASCADE ON UPDATE CASCADE;

You may visit our Facebook page for more information, inquiries, and comments. Please subscribe also to our YouTube Channel to receive free capstone projects resources and computer programming tutorials.

Hire our team to do the project