Billing System Database Design

Billing System Database Design

This article will serve as a guide in the development of your database schema or model about a simple billing system. The project is available in Visual Basic and MySQL/MS Access. iNetTutor.com allows you to copy the database design and modify it based on your requirement, a LIKE and SHARE  will be greatly appreciated.

tblattachedfile (id, customer_id, file_name_uploaded, remarks, file_type, date_uploaded, encoded_by)

Table Description: the tblattachedfile will store the files uploaded in the system, the can only accept pdf and png type of files (receipts, billing and other proofs of billing transaction). The said table has 7 columns; (1) id is the primary key of the table, (2) customer_id is a foreign key that links to the tblcustomer table, (3) file_name_uploaded refers to the file name of the uploaded file, (4) remarks is to the description or additional notes about the uploaded file, (5) file_type refers to the extension type of the file, (6) date_uploaded refers to the date the file was uploaded, (7) encoded_by is a foreign key that links to the user who encoded the transaction.

CREATE SQL Statement:

CREATE TABLE IF NOT EXISTS `tblattachedfile` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`customer_id` int(11) NOT NULL,
`file_name_uploaded` longblob NOT NULL,
`remarks` varchar(100) NOT NULL,
`file_type` int(1) NOT NULL,
`date_uploaded` date NOT NULL,
`encoded_by` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `customer_id` (`customer_id`,`encoded_by`),
KEY `encoded_by` (`encoded_by`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblbilling (id, invoice_no, customer_id, bill_amount, date_encoded, encoded_by, status,  reviewed_by, reviewed_date)

Table Description: the billing information will be stored in the tblbilling table and it has 9 attributes; (1) id is the primary key, (2) invoice_no refers to the official no of the transaction, (3) customer_id is a foreign key that links to the tblcustomer table, (4) bill_amount is to the total amount of billing, (5) date_encoded is the date the record was encoded in the system, (6) encoded_by is a foreign key that links to the user who processed the transaction, (7) status can contain on-going, processed and pending values, (8) reviewed_by refers to the another user who reviewed the transaction for double checking of the transaction, (9) reviewed_date refers to the date the transaction was reviewed.

CREATE SQL Statement:

CREATE TABLE IF NOT EXISTS `tblbilling` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`invoice_no` varchar(15) NOT NULL,
`customer_id` int(11) NOT NULL,
`bill_amount` float NOT NULL,
`date_encoded` date NOT NULL,
`encoded_by` int(11) NOT NULL,
`status` int(1) NOT NULL,
`reviewed_by` int(11) NOT NULL,
`reviewed_date` date NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `invoice_no` (`invoice_no`),
KEY `customer_id` (`customer_id`,`encoded_by`,`reviewed_by`),
KEY `encoded_by` (`encoded_by`),
KEY `reviewed_by` (`reviewed_by`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblbillinginfo (id, invoice_no, charge_id, charge_cost)

Table Description: the list of charges for a specific transaction will be stored in the tblbillinginfo. The table has 4 columns; (1) id is the primary key of the table, (2) invoice_no is a foreign key that connects to the tblbilling table, (3) charge_id is a foreign key that links to the tblcharges, (4) charge_cost the amount of the fee.

CREATE SQL Statement:

CREATE TABLE IF NOT EXISTS `tblbillinginfo` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`invoice_no` varchar(15) NOT NULL,
`charge_name` varchar(25) NOT NULL,
`charge_cost` float NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `invoice_no` (`invoice_no`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblcharges (id, charge_name, description, encoded_by, encoded_date)

Table Description: the list of charges allowed in the transactions are also encoded and recorded in the system, it will be stored in the tblcharges table and it has 5 attributes; (1) id is the primary key of the table, (2) charge_name, (3) description or known as the name of fee, (4) encoded_by refers to the user who encoded the record, (5) encoded_date refers to the date of data encoding.

CREATE SQL Statement:

CREATE TABLE IF NOT EXISTS `tblcharges` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`charge_name` varchar(25) NOT NULL,
`amount` float NOT NULL,
`description` varchar(100) NOT NULL,
`encoded_by` int(11) NOT NULL,
`encoded_date` date NOT NULL,
PRIMARY KEY (`id`),
KEY `encoded_by` (`encoded_by`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblcurrencyhistory (id, currency_value, last_update, updated_by)

Table Description: the value of peso to dollar are also part and of the system, the conversion rates are being monitored and being encoded in the system for reference use.

CREATE SQL Statement:

CREATE TABLE IF NOT EXISTS `tblcurrencyhistory` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`currency_value` float NOT NULL,
`last_update` date NOT NULL,
`updated_by` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `updated_by` (`updated_by`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblcustomer (id, customer_code, full_name, contact, address, hours_work, manpower, encoded_by)

Table Description: customer information such as the name, contact and address will be stored in the tblcustomer table. The said table has 8 attributes.

CREATE SQL Statement:

CREATE TABLE IF NOT EXISTS `tblcustomer` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`customer_code` varchar(15) NOT NULL,
`full_name` varchar(50) NOT NULL,
`contact` varchar(15) NOT NULL,
`address` varchar(100) NOT NULL,
`hours_work` float NOT NULL,
`manpower` int(3) NOT NULL,
`encoded_by` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `encoded_by` (`encoded_by`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tbluser (id, username, password, full_name, contact, user_type)

Table Description: the system has 3 types of users; (1) administrator has full access to the system, (2) accounting accounts can review the billing transaction, (3) billing or encoders can encode the billing details. The list of users will be stored in the tbluser table.

CREATE SQL Statement:

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

tbluserlog (id, user_id, login_time, logout_time, date_recorded)

Table Description: user log is a part of the program that monitors the login and logout time of each user and the logs will be stored in the tbluserlog table. It has 5 attributes; (1) id is the primary key of the table, (2) user_id is a foreign key that links to the tbluser table, (3) login_time the system will record the login time of the user to the system, (4) logout_time is when the user exits the system, (5) date_recorded is the date when the user has logged-in.

CREATE SQL Statement:

CREATE TABLE IF NOT EXISTS `tbluserlog` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`login_time` time NOT NULL,
`logout_time` time NOT NULL,
`date_recorded` date NOT NULL,
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Download .sql file

Constraints for dumped tables
—
— Constraints for table `tblattachedfile`
—
ALTER TABLE `tblattachedfile`
ADD CONSTRAINT `tblattachedfile_ibfk_2` FOREIGN KEY (`customer_id`) REFERENCES `tblcustomer` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblattachedfile_ibfk_1` FOREIGN KEY (`encoded_by`) REFERENCES `tbluser` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

—
— Constraints for table `tblbilling`
—
ALTER TABLE `tblbilling`
ADD CONSTRAINT `tblbilling_ibfk_3` FOREIGN KEY (`customer_id`) REFERENCES `tblcustomer` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblbilling_ibfk_1` FOREIGN KEY (`encoded_by`) REFERENCES `tbluser` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblbilling_ibfk_2` FOREIGN KEY (`reviewed_by`) REFERENCES `tbluser` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

—
— Constraints for table `tblbillinginfo`
—
ALTER TABLE `tblbillinginfo`
ADD CONSTRAINT `tblbillinginfo_ibfk_1` FOREIGN KEY (`invoice_no`) REFERENCES `tblbilling` (`invoice_no`) ON DELETE CASCADE ON UPDATE CASCADE;

—
— Constraints for table `tblcharges`
—
ALTER TABLE `tblcharges`
ADD CONSTRAINT `tblcharges_ibfk_1` FOREIGN KEY (`encoded_by`) REFERENCES `tbluser` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

—
— Constraints for table `tblcurrencyhistory`
—
ALTER TABLE `tblcurrencyhistory`
ADD CONSTRAINT `tblcurrencyhistory_ibfk_1` FOREIGN KEY (`updated_by`) REFERENCES `tbluser` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

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

—
— Constraints for table `tbluserlog`
—
ALTER TABLE `tbluserlog`
ADD CONSTRAINT `tbluserlog_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbluser` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

You may visit our facebook page for more information, inquiries and comments.

Hire our team to do the project.