Vehicle Repair and Maintenance Management System Database Design

Vehicle Repair and Maintenance Management System Database Design

About the System

Information Technology has become an integral part of any kind of business in terms of automating business operations and transactions. The capstone project, entitled “Vehicle Repair and Maintenance Management System” is designed for vehicle repair and maintenance management automation. The said project will automate the vehicle garage’s operations and daily transactions. The system will automate operations such as managing vehicle repair and maintenance records, invoice records, customer records, transaction records, billing and payment records, and transaction records.

As the number of vehicle owners grows, so does the popularity of vehicle garages. Vehicle owners frequently inquire about vehicle repair and maintenance in garages to get their vehicles inspected and repaired. Vehicle owners may guarantee that their vehicles function properly and do not fail unexpectedly by scheduling regular vehicle repair and maintenance services. Most vehicle garages still use manual techniques to conduct day-to-day business. Customer records are still kept manually, as is car repair progress monitoring, vehicle service updates, client transactions, and billing. For both vehicle garages and customers, this type of transaction is time-demanding. Customers would have to go to garages in-person to inquire about services and other necessary transactions to book for vehicle repair and maintenance. The garage’s functioning and reaction time to client inquiries will be slowed if manual methods are used repeatedly. The researchers recognized the need to improve the way vehicle garages operate.

There are many benefits to using a vehicle repair and maintenance management system. Some of the key benefits include:

1. Reduced Costs – A vehicle repair and maintenance management system can help you to track your vehicle maintenance and repair costs, allowing you to identify and correct any issues that are causing excessive spending.
2. Improved Safety – A properly managed vehicle repair and maintenance system can help to ensure that your vehicle is safer to drive, as it will be properly maintained and any issues identified will be fixed in a timely manner.
3. Better Efficiency – A vehicle repair and maintenance management system can help you to improve your overall efficiency when it comes to maintaining your car or truck.

Database Tables

Let’s start by making the tables and columns. For a thorough tutorial, please watch the video.

tbl_customer – this table will store the information of the customers in the system.

• 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).
• full_name – the customer’s full name
• complete_address – the complete address of the customer
• contact_number – the contact details of the customer most probably a mobile number
• avatar – this will hold the profile photo of the customer
• username – the desired username of the customer for his/her account
• password – the desired password of the customer for his/her account also used to login to the system

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,
`full_name` varchar(100) NOT NULL,
`complete_address` text NOT NULL,
`contact_number` varchar(15) NOT NULL,
`avatar` longblob NOT NULL,
`username` varchar(30) NOT NULL,
`password` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_task_info – this table will hold the task information in the system.

• task_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).
• transaction_code – unique code for the transaction
• task_name – name of the task
• description – description about the task
• vehicle_type- type of the vehicle to be repaired or maintained
• customer_id – this is a foreign key that points out to the customer
• member_id – this is a foreign key that points out to the member
• amount – amount to be paid for the task or service
• task_status – (0)pending, (1)completed, (2)paid
• completion_date- the date the task should be completed

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

CREATE TABLE `tbl_task_info` (
`task_id` int(11) NOT NULL,
`transaction_code` varchar(30) NOT NULL,
`task_name` varchar(30) NOT NULL,
`description` varchar(150) NOT NULL,
`vehicle_type` varchar(30) NOT NULL,
`customer_id` int(11) NOT NULL,
`member_id` int(11) NOT NULL,
`amount` float NOT NULL,
`task_status` int(1) NOT NULL,
`completion_date` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_task_details – this table will hold the details of the tasks in the system.

• task_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).
• task_id – this is a foreign key the points out to the task
• parts – the parts of the vehicle to be repaired or maintained
• quantity – quantity needed for the reparation
• amount – amount to be paid for the specific task
• remarks – additional information about the task

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

CREATE TABLE `tbl_task_details` (
`task_detail_id` int(11) NOT NULL,
`task_id` int(11) NOT NULL,
`parts` varchar(30) NOT NULL,
`quantity` int(4) NOT NULL,
`amount` float NOT NULL,
`remarks` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_invoice – the invoice information records will be stored in this table.

• 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 – the unique invoice number
• customer_id – this is a foreign key that points out to the customer
• total_amount – total amount to be paid for the invoice
• invoice_date – date when the invoice was generated
• prepared_by – user_id – this is a foreign key that points out to the user who prepared the invoice

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(30) NOT NULL,
`customer_id` int(11) NOT NULL,
`total_amount` float NOT NULL,
`invoice_date` date NOT NULL,
`prepared_by` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_team_member – this table will store the information of the team members working for vehicle reparation and maintenance.

• member_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).
• full_name- full name of the team member
• designation – designation of the member in the team
• complete_address- complete address of the
• username- the desired username of the team member, combined with the password to login to the account in the system
• password – the preferred password of the team member for his/her account.
• account_status – (0)active, (1)inactive

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

CREATE TABLE `tbl_team_member` (
`member_id` int(11) NOT NULL,
`full_name` varchar(100) NOT NULL,
`designation` int(1) NOT NULL,
`complete_address` text NOT NULL,
`username` varchar(30) NOT NULL,
`password` text NOT NULL,
`account_status` int(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_payment – the payment details will be stored in this table. This is also source table where the income reports are generated.

• 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).
• invoice_id – this is a foreign key that points out to the invoice
• paid_by (customer_id) – this is a foreign key that points out to the customer who made the payment
• payment_date – the date the payment was made
• amount- the amount to be paid
• balance_status – the amount of the balance
• remarks – additional information about the payment
• payment_status – (0)pending, (1)accepted, (2)rejected

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

CREATE TABLE `tbl_payment` (
`payment_id` int(11) NOT NULL,
`invoice_id` int(11) NOT NULL,
`paid_by` int(11) NOT NULL,
`payment_date` date NOT NULL,
`amount` float NOT NULL,
`balance_status` float NOT NULL,
`remarks` varchar(100) NOT NULL,
`payment_status` int(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_company_setting – this table will hold the information of the company setting.

• setting_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 name of the company
• address- the address where the company is located
• tin_number – tin number of the company
• contact_number – contact number of the company, can be a telephone or mobile number
• logo – the company’s logo
• updated_by (user_id) – this is a foreign key the points out to the user who commits update

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

CREATE TABLE `tbl_company_setting` (
`setting_id` int(11) NOT NULL,
`company_name` varchar(100) NOT NULL,
`address` text NOT NULL,
`tin_number` varchar(20) NOT NULL,
`contact_number` varchar(15) NOT NULL,
`logo` longblob NOT NULL,
`updated_by` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_sms – this table will store the information of the short messaging system (sms) in the system.

• api_code – unique API code
• api_password- the API password
• updated_by (user_id) – this is a foreign key that points out to the user who commits update

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

CREATE TABLE `tbl_sms` (
`api_code` varchar(100) NOT NULL,
`api_password` varchar(100) NOT NULL,
`updated_by` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Summary

In this article, we have identified the different database tables to include in the project entitled Vehicle Repair and Maintenance Management System. We hope that this will help you in the development and design of the said project and projects that might be related to it.

Please watch the video tutorial on how to prepare and create the different tables of the database of Vehicle Repair and Maintenance Management System.

Video Tutorial

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.

Related Topics and Articles:

Vehicle Repair and Maintenance Management System Free Bootstrap Source code

IPO Model Conceptual Framework of Vehicle Repair and Maintenance Management System

Vehicle Service Management System in Django

Python Vehicle Parking Management System

Vehicle Impoundment Information Management System

GPS based Vehicle Theft Detection System using GSM Technology

Vehicle Franchising and Drivers Offense System