Water Refilling System Database Model

Water Refilling System Database Model

The project entitled water refilling system is a database oriented information system that automates the record keeping of the water refilling station.

This article will provide you with the list of tables that might help you in the development of your own water refilling system.

Database Design/Schema and List of Database Tables

tblcustomer (id, control_no, full_name, address, contact_no, date_encoded, encoded_by)

Description: customer table will store the information of the clients, the table has 7 entities; (1) id serves as the primary key of the table, (2) the control_no serves as the customer’s unique code, (3) full_name stores the name of the customer, (4) address stores the complete address of the customer, (5) contact_no includes the mobile phone number, (6) date_encoded is the date given by the system, it represents the date when the information was saved/stored in the database, (7) encoded_by is the foreign key which links to the information of the user who encoded the information.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tblcustomer` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`control_no` varchar(15) NOT NULL,
`full_name` varchar(50) NOT NULL,
`address` varchar(30) NOT NULL,
`contact_no` varchar(30) NOT NULL,
`date_encoded` date NOT NULL,
`encoded_by` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `encodedby` (`encoded_by`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

tbldelivery (id, delivery_no, date_delivered, name_of_delivery, plate_no, customer_id, date_encoded, encoded_by)

Description: tbldelivery table is the list of delivery of water supplies to the customers. The table has 8 entities; (1) id is the primary key, (2) delivery_no is the control number of the transaction, (3) date_delivered is the date of delivery, (4) name_of_delivery is the column that stores the driver and the delivery boy information, (5) plate_no represents the plate number of the vehicle, (6) customer_id is the foreign key that links to the tblcustomer table, (7) date_encoded is the date of data entry to the system, (8) encoded_by is another foreign key which means the user who encoded the transaction.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tbldelivery` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`delivery_no` varchar(15) NOT NULL,
`date_delivered` date NOT NULL,
`name_of_delivery` varchar(20) NOT NULL,
`plate_no` varchar(10) NOT NULL,
`customer_id` int(11) NOT NULL,
`date_encoded` date NOT NULL,
`encoded_by` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `customerid` (`customer_id`,`encoded_by`),
KEY `encoded_by` (`encoded_by`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

tbldeliverydetails (delivery_no, product_id, qty, amount, total_amount, status(returned, unreturned))

Description: tbldeliverydetails is a table that stores the detailed products delivered to the client/customer. This table is connected to the tbldelivery.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tbldeliverydetails` (
`delivery_no` varchar(15) NOT NULL,
`product_id` int(11) NOT NULL,
`qty` int(11) NOT NULL,
`amount` int(11) NOT NULL,
`total_amount` int(11) NOT NULL,
`status` varchar(15) NOT NULL,
KEY `productid` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tblpayment (id, delivery_id, or_no, payment_amount, customer_id, encoded_by)

Description: payment of customer will be stored in tblpayment table, this table is exclusive for the delivery transactions. The table has 6 entities; (1) id is the primary key, (2) delivery_id is the foreign key that links to the tbldelivery table, (3) or_no is the official receipt number of the transaction, (4) payment_amount is the amount of delivery that includes the amount of orders and delivery fee, (5) customer_id is another foreign key that connects to the tblcustomer table and (6) encoded_by is the user info who processed the transaction.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tblpayment` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`delivery_id` int(11) NOT NULL,
`or_no` varchar(15) NOT NULL,
`payment_amount` float NOT NULL,
`customer_id` int(11) NOT NULL,
`encoded_by` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `customerid` (`customer_id`,`encoded_by`),
KEY `orno` (`or_no`,`payment_amount`,`customer_id`,`encoded_by`),
KEY `delivery_id` (`delivery_id`),
KEY `encoded_by` (`encoded_by`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblpos (id, sales_no, date, date_encoded, encoded_by)

Description: tblpos is the table for direct sales or point of sales, it also refers to the walk-in sales.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tblpos` (
`id` int(11) NOT NULL DEFAULT '0',
`sales_no` varchar(15) NOT NULL,
`date_encoded` date NOT NULL,
`encoded_by` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `encoded_by` (`encoded_by`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tblposdetails (sales_no, pos_id, qty, refill_price, total_amount)

Description: tblposdetails is the table that stores the different items bought through the POS module of the system. This is connected to the tblpos.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tblposdetails` (
`sales_no` varchar(15) NOT NULL,
`pos_id` int(11) NOT NULL,
`qty` int(11) NOT NULL,
`refill_price` float NOT NULL,
`total_amount` float NOT NULL,
KEY `pos_id` (`pos_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tblproduct (id, control_no, product_name, category(container, pet bottles, accessories), qty, price, refill_price, discount_percent, discount_amount, date_encoded, encoded_by)

Description: the different products of the store will be stored in the tblproduct table. The table has 11 entities; (1) the primary is the id column, (2) the control_no is the code of the project, (3) product_name is the name of the project, (4) category is the grouping of products (container, pet bottles, accessories), (5) qty refers to the quantity of items on the inventory, (6) price is the suggested retail price of the product, (7) refill_price the amount of refill, (8) discount_percent is the discount in percent format, (9) discount_amount is the discount in actual amount, (10) date_encoded is the data entry of the product to the system, and (11) the encoded_by is the user who encodes the product to the system.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tblproduct` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`control_no` varchar(15) NOT NULL,
`product_name` varchar(30) NOT NULL,
`category` varchar(15) NOT NULL,
`qty` int(11) NOT NULL,
`price` int(11) NOT NULL,
`refill_price` float NOT NULL,
`discount_percent` float NOT NULL,
`discount_amount` float NOT NULL,
`date_encoded` date NOT NULL,
`encoded_by` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `encoded_by` (`encoded_by`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

tbluser (id, username, password, user_full_name, user_address, user_contact, user_type(cashier, admin))

Description: user table will store the different person that can access the system, it has two types the administrator and the cashier type.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tbluser` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_full_name` varchar(30) NOT NULL,
`user_address` varchar(50) NOT NULL,
`user_contact` varchar(15) NOT NULL,
`username` varchar(15) NOT NULL,
`password` varchar(15) NOT NULL,
`user_type` varchar(15) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Constraints for dumped tables

—
— Constraints for table `tblcustomer`
—
ALTER TABLE `tblcustomer`
ADD CONSTRAINT `tblcustomer_ibfk_1` FOREIGN KEY (`encoded_by`) REFERENCES `tbluser` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE;

—
— Constraints for table `tbldelivery`
—
ALTER TABLE `tbldelivery`
ADD CONSTRAINT `tbldelivery_ibfk_2` FOREIGN KEY (`customer_id`) REFERENCES `tblcustomer` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tbldelivery_ibfk_1` FOREIGN KEY (`encoded_by`) REFERENCES `tbluser` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE;

—
— Constraints for table `tbldeliverydetails`
—
ALTER TABLE `tbldeliverydetails`
ADD CONSTRAINT `tbldeliverydetails_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `tblproduct` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

—
— Constraints for table `tblpayment`
—
ALTER TABLE `tblpayment`
ADD CONSTRAINT `tblpayment_ibfk_3` FOREIGN KEY (`delivery_id`) REFERENCES `tbldelivery` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblpayment_ibfk_1` FOREIGN KEY (`encoded_by`) REFERENCES `tbluser` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE,
ADD CONSTRAINT `tblpayment_ibfk_2` FOREIGN KEY (`customer_id`) REFERENCES `tblcustomer` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

—
— Constraints for table `tblpos`
—
ALTER TABLE `tblpos`
ADD CONSTRAINT `tblpos_ibfk_1` FOREIGN KEY (`encoded_by`) REFERENCES `tbluser` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE;

—
— Constraints for table `tblposdetails`
—
ALTER TABLE `tblposdetails`
ADD CONSTRAINT `tblposdetails_ibfk_1` FOREIGN KEY (`pos_id`) REFERENCES `tblpos` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

—
— Constraints for table `tblproduct`
—
ALTER TABLE `tblproduct`
ADD CONSTRAINT `tblproduct_ibfk_1` FOREIGN KEY (`encoded_by`) REFERENCES `tbluser` (`id`) ON DELETE NO ACTION 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.