Diagrams Projects

Repair Shop Management System Database Design

inettutor.com

Repair Shop Management System Database Design

The project repair shop management system is an online platform intended to cater the transactions between the shop owner, technician and customers/clients. The design of this project can be used by computer repair shop, cellphone repair shop, car/auto repair shop and many more.

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

Repair Shop Management System Database Design - List of Tables
Repair Shop Management System Database Design – List of Tables

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 IF NOT EXISTS `tbluser` (
`user_id` int(11) NOT NULL AUTO_INCREMENT,
`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,
PRIMARY KEY (`user_id`),
KEY `user_category_id` (`user_category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

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 IF NOT EXISTS `tblusergroup` (
`user_group_id` int(11) NOT NULL AUTO_INCREMENT,
`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,
PRIMARY KEY (`user_group_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblclient – this table will store the information of the customer/clients, the table has 9 columns.

  • client_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).
  • client_code – a unique code given to the client/customer.
  • avatar – this will hold the profile image of the client/customer.
  • client_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.

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

CREATE TABLE IF NOT EXISTS `tblclient` (
`client_id` int(11) NOT NULL AUTO_INCREMENT,
`client_code` varchar(15) NOT NULL,
`avatar` blob NOT NULL,
`client_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,
PRIMARY KEY (`client_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblservices – list of services offered by the shop will be stored and managed in this table.

  • 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 offered by the shop.
  • description – detailed description of the service.
  • amount – the amount of the service.
  • encoded_by – this is a foreign key that points out to the user who encodes the service name.

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

CREATE TABLE IF NOT EXISTS `tblservices` (
`service_id` int(11) NOT NULL AUTO_INCREMENT,
`service_name` varchar(30) NOT NULL,
`description` varchar(50) NOT NULL,
`amount` float NOT NULL,
`encoded_by` int(11) NOT NULL,
PRIMARY KEY (`service_id`),
KEY `encoded_by` (`encoded_by`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblitem – list of items, materials, tools and products of the shop will be encoded and stored in this table.

  • item_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).
  • item_name – name of the item, tools or product.
  • item_category_id – this is a foreign key that links to the tblitemcategory table.
  • image – the actual picture of the item.
  • description – the specification of the item.
  • serial_no – serial number of the item.
  • amount – the retail amount of the item.
  • encoded_by – this is a foreign key that points out to the user who encodes the item information.

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

CREATE TABLE IF NOT EXISTS `tblitem` (
`item_id` int(11) NOT NULL AUTO_INCREMENT,
`item_name` varchar(30) NOT NULL,
`item_category_id` int(11) NOT NULL,
`image` blob NOT NULL,
`description` varchar(50) NOT NULL,
`serial_no` varchar(30) NOT NULL,
`amount` float NOT NULL,
`encoded_by` int(11) NOT NULL,
PRIMARY KEY (`item_id`),
KEY `item_category_id` (`item_category_id`,`encoded_by`),
KEY `encoded_by` (`encoded_by`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblitemcategory – items are grouped according to their purpose. This table has two columns.

  • item_category_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).
  • category_name – item category name.

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

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

tbltechnician – information of the technician and repair man will be encoded and stored in this table. The table consists of 11 columns.

  • technician_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).
  • technician_code – a unique code given to the technician/repair man.
  • avatar – profile picture/image of the technician/repair man.
  • technician_name – complete name of the technician/repair man.
  • technician_email – email address of the technician/repair man.
  • techinician_contact – contact information of the technician/repair man.
  • specialization_details – field of specialization of the technician/repair man.
  • technician_username – desired username of the technician/repair man.
  • technician_password – desired password of the technician/repair man.
  • status – the value of this column is 0 or 1, 0 means deactivated or inactive, 1 is activated or active.
  • encoded_by – this is a foreign key that points out to the user who encodes the technician information.

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

 CREATE TABLE IF NOT EXISTS `tbltechnician` (
`technician_id` int(11) NOT NULL AUTO_INCREMENT,
`technician_code` varchar(15) NOT NULL,
`avatar` blob NOT NULL,
`technician_name` varchar(30) NOT NULL,
`technician_email` varchar(30) NOT NULL,
`techinician_contact` varchar(15) NOT NULL,
`specialization_details` varchar(100) NOT NULL,
`technician_username` varchar(30) NOT NULL,
`technician_password` varchar(30) NOT NULL,
`status` int(1) NOT NULL,
`encoded_by` int(11) NOT NULL,
PRIMARY KEY (`technician_id`),
KEY `encoded_by` (`encoded_by`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblworkorder – work request of the customers will be stored and processed on this table and it has 10 entities/fields.

  • work_order_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).
  • request_date – the date of work request by the customer.
  • service_id – this is a foreign key that links to the service information table.
  • amount – the cost of service.
  • customer_id – – this is a foreign key that points out to the customer information that request for a repair or service.
  • technician_id – this is a foreign key that links to the technician table. This is the technician that is assigned in a specific work request or order.
  • estimate_date_of_completion – the estimate date of completion.
  • status – progress of the work.
  • remarks – comments and additional information about the work request.
  • processed_by – this is a foreign key that points out to the user who processed the work order information.

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

 CREATE TABLE IF NOT EXISTS `tblworkorder` (
`work_order_id` int(11) NOT NULL AUTO_INCREMENT,
`request_date` date NOT NULL,
`service_id` int(11) NOT NULL,
`amount` float NOT NULL,
`customer_id` int(11) NOT NULL,
`technician_id` int(11) NOT NULL,
`estimate_date_of_completion` date NOT NULL,
`status` int(1) NOT NULL,
`remarks` varchar(100) NOT NULL,
`processed_by` int(11) NOT NULL,
PRIMARY KEY (`work_order_id`),
KEY `service_id` (`service_id`,`customer_id`,`technician_id`,`processed_by`),
KEY `processed_by` (`processed_by`),
KEY `customer_id` (`customer_id`),
KEY `technician_id` (`technician_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblworkorderdetails

  • work_order_detail_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).
  • work_order_id – this is a foreign key that links to the work order table.
  • item_id – this is a foreign key that points out to the item information in the item table.
  • quantity – the number of items to be included in the work order details.
  • amount – the amount of the item.
  • total – this column is equal to the quantity multiple to the amount.

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

CREATE TABLE IF NOT EXISTS `tblworkorderdetails` (
`work_order_detail_id` int(11) NOT NULL AUTO_INCREMENT,
`work_order_id` int(11) NOT NULL,
`item_id` int(11) NOT NULL,
`quantity` int(5) NOT NULL,
`amount` float NOT NULL,
`total` float NOT NULL,
PRIMARY KEY (`work_order_detail_id`),
KEY `work_order_id` (`work_order_id`,`item_id`),
KEY `item_id` (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblpayment

  • 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).
  • or_no – official receipt number.
  • work_order_id – foreign key that links to the word order table.
  • total_amount = service amount + item amount.
  • is_paid – the status of payment (paid or not).
  • date_of_payment – the payment date.
  • paid_by – the person who pays the transaction (usually the customer itself).
  • remarks – additional details and comments of the transaction.
  • processed_by – this is a foreign key that points out to 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 IF NOT EXISTS `tblpayment` (
`payment_id` int(11) NOT NULL AUTO_INCREMENT,
`or_no` varchar(15) NOT NULL,
`work_order_id` int(11) NOT NULL,
`total_amount` float NOT NULL,
`is_paid` int(1) NOT NULL,
`date_of_payment` date NOT NULL,
`paid_by` varchar(50) NOT NULL,
`remarks` varchar(50) NOT NULL,
`processed_by` int(11) NOT NULL,
PRIMARY KEY (`payment_id`),
KEY `work_order_id` (`work_order_id`,`processed_by`),
KEY `processed_by` (`processed_by`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblshopinfo

  • shop_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).
  • shop_name – the name of the shop or business.
  • owner_name – the name of the owner.
  • address – complete address of the shop or store.
  • email_address – email address of the shop.
  • contact_no – contact information (mobile or landline number)
  • website – company website if there is one.
  • updated_by – the user who updates the information.

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

CREATE TABLE IF NOT EXISTS `tblshopinfo` (
`shop_id` int(11) NOT NULL AUTO_INCREMENT,
`shop_name` varchar(50) NOT NULL,
`owner_name` varchar(30) NOT NULL,
`address` varchar(100) NOT NULL,
`email_address` varchar(30) NOT NULL,
`contact_no` varchar(15) NOT NULL,
`website` varchar(30) NOT NULL,
`updated_by` int(11) NOT NULL,
PRIMARY KEY (`shop_id`),
KEY `updated_by` (`updated_by`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
Repair Shop Management System Database Design

Repair Shop Management System Database Design

Constraints for dumped tables
--
-- Constraints for table `tblitem`
--
ALTER TABLE `tblitem`
ADD CONSTRAINT `tblitem_ibfk_2` FOREIGN KEY (`item_category_id`) REFERENCES `tblitemcategory` (`item_category_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblitem_ibfk_1` FOREIGN KEY (`encoded_by`) REFERENCES `tbluser` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `tblpayment`
--
ALTER TABLE `tblpayment`
ADD CONSTRAINT `tblpayment_ibfk_2` FOREIGN KEY (`work_order_id`) REFERENCES `tblworkorder` (`work_order_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblpayment_ibfk_1` FOREIGN KEY (`processed_by`) REFERENCES `tbluser` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;

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

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

--
-- Constraints for table `tbltechnician`
--
ALTER TABLE `tbltechnician`
ADD CONSTRAINT `tbltechnician_ibfk_1` FOREIGN KEY (`encoded_by`) 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;

--
-- Constraints for table `tblworkorder`
--
ALTER TABLE `tblworkorder`
ADD CONSTRAINT `tblworkorder_ibfk_4` FOREIGN KEY (`technician_id`) REFERENCES `tbltechnician` (`technician_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblworkorder_ibfk_1` FOREIGN KEY (`processed_by`) REFERENCES `tbluser` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblworkorder_ibfk_2` FOREIGN KEY (`service_id`) REFERENCES `tblservices` (`service_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblworkorder_ibfk_3` FOREIGN KEY (`customer_id`) REFERENCES `tblclient` (`client_id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `tblworkorderdetails`
--
ALTER TABLE `tblworkorderdetails`
ADD CONSTRAINT `tblworkorderdetails_ibfk_2` FOREIGN KEY (`work_order_id`) REFERENCES `tblworkorder` (`work_order_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblworkorderdetails_ibfk_1` FOREIGN KEY (`item_id`) REFERENCES `tblitem` (`item_id`) ON DELETE CASCADE ON UPDATE CASCADE;

Free Download .sql file

Our team can modify the project based on your specific business requirements.

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

Hire our team to do the project.

Watch the full video tutorial

, ,

Post navigation

Similar Articles

Registrar Grade Profiling System in Visual Basic

inettutor.comLeave a reply
Sales and Inventory System ER Diagram - Step 3 Complete ERD

Sales and Inventory System ER Diagram

inettutor.comLeave a reply

IPO Model Conceptual Framework of Online Patient Information System

inettutor.comLeave a reply