Laundry Booking System Database Design

Laundry Booking System Database Design

The capstone project entitled “Laundry Booking System” is a multi-shop platform that will allow laundry shop owners to manage their laundry business online. This study will also be beneficial to the customers since they can book an appointment online.

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

Laundry Booking System Database Design - List of Table — Laundry Booking System Database Design – List of Table

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

tblcustomer – this table will store the information of the customer, the table has 11 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_code – a unique code given to the person (qr code, barcode, etc)
avatar – this will hold the profile image of the person.
customer_name – full name of the client/customer.
email_address – email address of the client/customer.
contact_number – contact number of the client/customer, preferably mobile or cellphone number.
complete_address – complete address of the client/customer.
username – the desired username of the client/customer.
password – the desired password of the client/customer.
status – the value of this column is 0 or 1, 0 means deactivated or inactive, 1 is activated or active.
user_id – the user who accepts/approve/deactivate the person profile and registration. It is a foreign key that connects to the user table.

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

CREATE TABLE `tblcustomer` (
`customer_id` int(11) NOT NULL,
`customer_code` varchar(30) NOT NULL,
`avatar` blob NOT NULL,
`customer_name` varchar(30) NOT NULL,
`email_address` varchar(30) NOT NULL,
`contact_number` varchar(15) NOT NULL,
`complete_address` varchar(100) NOT NULL,
`username` varchar(30) NOT NULL,
`password` varchar(30) NOT NULL,
`status` int(1) NOT NULL,
`user_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

tblservice – this table stores the information on the different services offered by the laundry shop.

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).
service_name – name of the service (example: washing, ironing, dry cleaning, etc).
service_description – description of the service
user_id – the user who encode/update the service information.

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

CREATE TABLE `tblservice` (
`service_id` int(11) NOT NULL,
`service_name` varchar(30) NOT NULL,
`service_description` varchar(50) NOT NULL,
`user_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

tblunit – list of clothes, garments, apparel, dress, etc are stored and managed in this table.

unit_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).
unit_name – name of the unit (example: shirts, pants, jeans, etc).
charge_amount – the charge amount per unit.
user_id – the user who encode/update the unit information.

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

CREATE TABLE `tblunit` (
`unit_id` int(11) NOT NULL,
`unit_name` varchar(15) NOT NULL,
`charge_amount` float NOT NULL,
`user_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

tblappointment – appointment table stores the information of the appointment made by the customers.

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 will point out to the customer who requested for an appointment in the laundry shop.
date_of_appointment – date of appointment transaction selected by the customer.
status – status of the appointment (example: completed, cancelled by the customer, rejected, rescheduled, etc)
remarks – additional information of the transaction.
user_id – the user who encode/update the appointment information.

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

CREATE TABLE `tblappointment` (
`appointment_id` int(11) NOT NULL,
`customer_id` int(11) NOT NULL,
`date_of_appointment` date NOT NULL,
`status` int(1) NOT NULL,
`remarks` varchar(100) NOT NULL,
`user_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

tblpayment – payment information will be stored and archived on this table.

payment_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).
appointment_id – points out to the appointment record.
unit_id – points out to the unit information.
service_id – the service the customer wants to avail.
quantity – number or quantity of the unit.
charge_amount – amount or charge per unit.
discount – discount if any.
remarks – additional information on the transaction.
payment_status – status of the payment (paid, unpaid)
paid_by – person who processed the payment (it is usually the customer information).
user_id – the user who processed the payment information.

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

CREATE TABLE `tblpayment` (
`payment_id` int(11) NOT NULL,
`appointment_id` int(11) NOT NULL,
`unit_id` int(11) NOT NULL,
`service_id` int(11) NOT NULL,
`quantity` int(11) NOT NULL,
`charge_amount` float NOT NULL,
`discount` float NOT NULL,
`remarks` varchar(100) NOT NULL,
`payment_status` int(1) NOT NULL,
`paid_by` varchar(30) NOT NULL,
`user_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

tblsettings– this table will store the information of the laundry shop or company. The table has 7 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 or shop
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 tblsettings, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE `tblsetting` (
`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;

tbluser – 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 tbluser, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE `tbluser` (
`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;

tblusergroup – 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.
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 tblusergroup, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE `tblusergroup` (
`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 `tblappointment`
--
ALTER TABLE `tblappointment`
ADD PRIMARY KEY (`appointment_id`),
ADD KEY `customer_id` (`customer_id`),
ADD KEY `user_id` (`user_id`);

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

--
-- Indexes for table `tblpayment`
--
ALTER TABLE `tblpayment`
ADD PRIMARY KEY (`payment_id`),
ADD KEY `appointment_id` (`appointment_id`),
ADD KEY `unit_id` (`unit_id`),
ADD KEY `service_id` (`service_id`),
ADD KEY `user_id` (`user_id`);

--
-- Indexes for table `tblservice`
--
ALTER TABLE `tblservice`
ADD PRIMARY KEY (`service_id`),
ADD KEY `user_id` (`user_id`);

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

--
-- Indexes for table `tblunit`
--
ALTER TABLE `tblunit`
ADD PRIMARY KEY (`unit_id`),
ADD KEY `user_id` (`user_id`);

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

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

--
-- AUTO_INCREMENT for dumped tables
--

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

--
-- AUTO_INCREMENT for table `tblpayment`
--
ALTER TABLE `tblpayment`
MODIFY `payment_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `tblservice`
--
ALTER TABLE `tblservice`
MODIFY `service_id` int(11) NOT NULL AUTO_INCREMENT;

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

--
-- AUTO_INCREMENT for table `tblunit`
--
ALTER TABLE `tblunit`
MODIFY `unit_id` int(11) NOT NULL AUTO_INCREMENT;

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

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

Constraints for dumped tables

--
-- Constraints for table `tblappointment`
--
ALTER TABLE `tblappointment`
ADD CONSTRAINT `tblappointment_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbluser` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblappointment_ibfk_2` FOREIGN KEY (`customer_id`) REFERENCES `tblcustomer` (`customer_id`) ON DELETE CASCADE ON UPDATE CASCADE;

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

--
-- Constraints for table `tblpayment`
--
ALTER TABLE `tblpayment`
ADD CONSTRAINT `tblpayment_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbluser` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblpayment_ibfk_2` FOREIGN KEY (`unit_id`) REFERENCES `tblunit` (`unit_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblpayment_ibfk_3` FOREIGN KEY (`service_id`) REFERENCES `tblservice` (`service_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblpayment_ibfk_4` FOREIGN KEY (`appointment_id`) REFERENCES `tblappointment` (`appointment_id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `tblservice`
--
ALTER TABLE `tblservice`
ADD CONSTRAINT `tblservice_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbluser` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `tblsetting`
--
ALTER TABLE `tblsetting`
ADD CONSTRAINT `tblsetting_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbluser` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `tblunit`
--
ALTER TABLE `tblunit`
ADD CONSTRAINT `tblunit_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbluser` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `tbluser`
--
ALTER TABLE `tbluser`
ADD CONSTRAINT `tbluser_ibfk_1` FOREIGN KEY (`user_category_id`) REFERENCES `tblusergroup` (`user_group_id`) ON DELETE CASCADE ON UPDATE CASCADE;
COMMIT;