Beauty Parlour Billing System Database Design

Beauty Parlour Billing System Database Design

The capstone project entitled “Beauty Parlour Billing System” is a network based system intended to automate the processes and transactions in a beauty parlour shop. You can use the database design example provided as a basis in the development of your own beauty parlour billing system.

This article will provide you with the list of tables and field/columns for every table in the design of database structure/schema of Beauty Parlour Billing System. The team will later provide a video tutorial on how to create the database in PHPMyAdmin.

This database design has 8 tables with their respective fields and columns as well as their relationships among each other.

Beauty Parlor Billing System Database Design - List of Tables — Beauty Parlor Billing System Database Design – List of Tables

tbl_appointment – this table will record the appointment schedule of the client or customer. Schedule of appointments are done via a phone call, text message or email. The appointment table of the beauty parlour billing system has 5 columns.

appointment_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
customer_id – this is a foreign key that represents the customer/client who had set a schedule on the shop.
schedule_date – date of schedule set by the client.
remarks – other important message or information about the appointment.
appointment_status – 0 for reserved, 1 for completed, 2 for cancelled, 3 or rescheduled.

Create SQL Statement – the statement below is used to create the tbl_appointment, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE `tbl_appointment` (
`appointment_id` int(11) NOT NULL,
`customer_id` int(11) NOT NULL,
`schedule_date` date NOT NULL,
`remarks` varchar(100) NOT NULL,
`appointment_status` int(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

tbl_company_profile – this table will store the information of the company. The table has 8 columns.

company_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
company_name – the complete name of the company.
company_contact_person – contact person of the company
company_email – official email address of the company
company_contact_number – contact information of the company (landline, mobile, etc)
company_website – official website of the company (if any)
company_profile – this column is open for any content such as the history of the company, services offered, and any other information about the company.
user_id – the user who accepts/approve/deactivate the company profile. It is a foreign key that connects to the user table.

Create SQL Statement – the statement below is used to create the tbl_company_profile, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE `tbl_company_profile` (
`company_id` int(11) NOT NULL,
`company_name` varchar(50) NOT NULL,
`company_contact_person` varchar(30) NOT NULL,
`company_email` varchar(30) NOT NULL,
`company_contact_number` varchar(15) NOT NULL,
`company_website` varchar(30) NOT NULL,
`company_profile` varchar(250) NOT NULL,
`user_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

tbl_customer – this table will store the basic information of the customer/client. The customer table of the beauty parlour billing system has 5 columns.

customer_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
customer_name – complete name of the customer or client.
address – complete address of the customer
phone_no – mobile or cellphone number of the customer or client

Create SQL Statement – the statement below is used to create the tbl_customer, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE `tbl_customer` (
`customer_id` int(11) NOT NULL,
`customer_name` varchar(30) NOT NULL,
`address` varchar(100) NOT NULL,
`phone_no` varchar(15) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

tbl_employee – this table stores the information of the employees of the beauty parlour shop or company. Employee table of the beauty parlour billing system has 5 columns.

employee_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
employee_name – complete name of the employee.
work_description – job or work description of the employee.
phone_no – mobile or cellphone number of the employee.
address – complete address of the employee.

Create SQL Statement – the statement below is used to create the tbl_employee, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE `tbl_employee` (
`employee_id` int(11) NOT NULL,
`employee_name` varchar(30) NOT NULL,
`work_description` varchar(100) NOT NULL,
`phone_no` varchar(15) NOT NULL,
`address` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

tbl_invoice – invoice table will store the information of the billing statement. The invoice table of the beauty parlour billing system has 10 columns.

invoice_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
invoice_number – system generated transaction number.
date_recorded – date of the billing transaction.
customer_id – this is a foreign key that links to the information of the customer.
total_amount_to_pay – total amount of billing.
discounted_amount – discount of the billing.
vat_percentage – vat charge.
total_after_discount – this column is equal to the amount of billing deducted by the discount.
payment_type – 0 for cash, 1 for credit or via card.
user_id – this is the foreign key that represents the user who processed the billing transaction.

Create SQL Statement – the statement below is used to create the tbl_invoice, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE `tbl_invoice` (
`invoice_id` int(11) NOT NULL,
`invoice_number` varchar(15) NOT NULL,
`date_recorded` date NOT NULL,
`customer_id` int(11) NOT NULL,
`total_amount_to_pay` float NOT NULL,
`discounted_amount` float NOT NULL,
`vat_percentage` int(2) NOT NULL,
`total_after_discount` float NOT NULL,
`payment_type` int(1) NOT NULL,
`user_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

tbl_invoice_details – this table store the details of the billing, this is the list of items, products or services availed by the customer. The invoice details table of the beauty parlour billing system has 10 columns.

invoice_details_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
invoice_id – this columns refers to the billing information.
product_service_id – the product or service availed or bought by the customer. This is a foreign key that connects to the product and service table.
quantity – number of items bought or availed by the customer.
service – if the customer availed for a service then the amount will be stored in this column.
product – if the customer bought an item then the amount will be stored in this column.
amount – it is equal to either the service or product column.
total_amount – this column is equal to quantity multiply to the amount.
date_recorded – date of transaction.
employee_id – foreign key that links to the employee table.

Create SQL Statement – the statement below is used to create the tbl_invoice_details, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE `tbl_invoice_details` (
`invoice_details_id` int(11) NOT NULL,
`invoice_id` int(11) NOT NULL,
`product_service_id` int(11) NOT NULL,
`quantity` int(3) NOT NULL,
`service` float NOT NULL,
`product` float NOT NULL,
`amount` float NOT NULL,
`total_amount` float NOT NULL,
`date_recorded` date NOT NULL,
`employee_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

tbl_product_service – this table stores the products and services of the beauty parlour. The products and services table of the beauty parlour billing system has 5 columns.

product_service_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
product_service_name – name of the product or service.
p_description – description of the product or service.
amount – amount of the product or service.
p_type – 0 for product and 1 for service.

Create SQL Statement – the statement below is used to create the tbl_product_service, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE `tbl_product_service` (
`product_service_id` int(11) NOT NULL,
`product_service_name` varchar(30) NOT NULL,
`p_description` varchar(100) NOT NULL,
`amount` float NOT NULL,
`p_type` int(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

tbl_user – this table will store the information of personnel who can access the records of the system.

user_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
username – username of the personnel used to login together with the password.
password – password of the personnel used to login together with the username.
avatar – this will hold the profile image of the user.
fullname – the complete name of the personnel or user.
contact – contact number of the personnel (mobile/cellphone number).
email – email address of the personnel/user.
user_category_id – the user group or category of the user.
status – the value of this column is 0 or 1, 0 means deactivated or inactive, 1 is activated or active.

Create SQL Statement – the statement below is used to create the tbl_user, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE `tbl_user` (
`user_id` int(11) NOT NULL,
`username` varchar(30) NOT NULL,
`password` varchar(30) NOT NULL,
`avatar` blob NOT NULL,
`fullname` varchar(50) NOT NULL,
`contact` varchar(15) NOT NULL,
`email` varchar(30) NOT NULL,
`user_category_id` int(1) NOT NULL,
`status` int(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_user_group – this table store the information of the user group which includes the functions they can and can’t access in the system.

user_group_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
group_name – name of the category or user group (cashier, encoder, administrator).
description – information on what the user group is all about.
allow_add – this column is to allow or prevent user from adding a record.
allow_edit – this column is to allow or prevent user from editing or updating a record.
allow_delete – this column is to allow or prevent user from removing or deleting a record.
allow_print – this column is to allow or prevent user from printing a report.
allow_import – this column is to allow or prevent user from importing records to the system.
allow_export – this column is to allow or prevent user from exporting records from the system.

Create SQL Statement – the statement below is used to create the tbl_user_group, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE `tbl_user_group` (
`user_group_id` int(11) NOT NULL,
`group_name` varchar(30) NOT NULL,
`description` varchar(50) NOT NULL,
`allow_add` int(1) NOT NULL,
`allow_edit` int(1) NOT NULL,
`allow_delete` int(1) NOT NULL,
`allow_print` int(1) NOT NULL,
`allow_import` int(1) NOT NULL,
`allow_export` int(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Indexes for dumped tables

--
-- Indexes for table `tbl_appointment`
--
ALTER TABLE `tbl_appointment`
ADD PRIMARY KEY (`appointment_id`),
ADD KEY `customer_id` (`customer_id`);

--
-- Indexes for table `tbl_company_profile`
--
ALTER TABLE `tbl_company_profile`
ADD PRIMARY KEY (`company_id`),
ADD KEY `user_id` (`user_id`);

--
-- Indexes for table `tbl_customer`
--
ALTER TABLE `tbl_customer`
ADD PRIMARY KEY (`customer_id`);

--
-- Indexes for table `tbl_employee`
--
ALTER TABLE `tbl_employee`
ADD PRIMARY KEY (`employee_id`);

--
-- Indexes for table `tbl_invoice`
--
ALTER TABLE `tbl_invoice`
ADD PRIMARY KEY (`invoice_id`),
ADD KEY `customer_id` (`customer_id`),
ADD KEY `user_id` (`user_id`);

--
-- Indexes for table `tbl_invoice_details`
--
ALTER TABLE `tbl_invoice_details`
ADD PRIMARY KEY (`invoice_details_id`),
ADD KEY `invoice_id` (`invoice_id`),
ADD KEY `product_service_id` (`product_service_id`),
ADD KEY `employee_id` (`employee_id`);

--
-- Indexes for table `tbl_product_service`
--
ALTER TABLE `tbl_product_service`
ADD PRIMARY KEY (`product_service_id`);

--
-- Indexes for table `tbl_user`
--
ALTER TABLE `tbl_user`
ADD PRIMARY KEY (`user_id`),
ADD KEY `user_category_id` (`user_category_id`);

--
-- Indexes for table `tbl_user_group`
--
ALTER TABLE `tbl_user_group`
ADD PRIMARY KEY (`user_group_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `tbl_appointment`
--
ALTER TABLE `tbl_appointment`
MODIFY `appointment_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `tbl_company_profile`
--
ALTER TABLE `tbl_company_profile`
MODIFY `company_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `tbl_customer`
--
ALTER TABLE `tbl_customer`
MODIFY `customer_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `tbl_employee`
--
ALTER TABLE `tbl_employee`
MODIFY `employee_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `tbl_invoice`
--
ALTER TABLE `tbl_invoice`
MODIFY `invoice_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `tbl_invoice_details`
--
ALTER TABLE `tbl_invoice_details`
MODIFY `invoice_details_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `tbl_product_service`
--
ALTER TABLE `tbl_product_service`
MODIFY `product_service_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `tbl_user`
--
ALTER TABLE `tbl_user`
MODIFY `user_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `tbl_user_group`
--
ALTER TABLE `tbl_user_group`
MODIFY `user_group_id` int(11) NOT NULL AUTO_INCREMENT;

Constraints for dumped tables

--
-- Constraints for table `tbl_appointment`
--
ALTER TABLE `tbl_appointment`
ADD CONSTRAINT `tbl_appointment_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `tbl_customer` (`customer_id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `tbl_company_profile`
--
ALTER TABLE `tbl_company_profile`
ADD CONSTRAINT `tbl_company_profile_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbl_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `tbl_invoice`
--
ALTER TABLE `tbl_invoice`
ADD CONSTRAINT `tbl_invoice_ibfk_2` FOREIGN KEY (`customer_id`) REFERENCES `tbl_customer` (`customer_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tbl_invoice_ibfk_3` FOREIGN KEY (`user_id`) REFERENCES `tbl_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `tbl_invoice_details`
--
ALTER TABLE `tbl_invoice_details`
ADD CONSTRAINT `tbl_invoice_details_ibfk_1` FOREIGN KEY (`invoice_id`) REFERENCES `tbl_invoice` (`invoice_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tbl_invoice_details_ibfk_2` FOREIGN KEY (`employee_id`) REFERENCES `tbl_employee` (`employee_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tbl_invoice_details_ibfk_3` FOREIGN KEY (`product_service_id`) REFERENCES `tbl_product_service` (`product_service_id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `tbl_user`
--
ALTER TABLE `tbl_user`
ADD CONSTRAINT `tbl_user_ibfk_1` FOREIGN KEY (`user_category_id`) REFERENCES `tbl_user_group` (`user_group_id`) ON DELETE CASCADE ON UPDATE CASCADE;
COMMIT;