Car Rental System Database Design

January 14, 2020August 24, 2024 inettutor.com

Car Rental System Database Design

The car rental system is an online platform that serves as a tool for the car owners to post their vehicle for hire or rent, this will also serve as market ground for customers who are looking to rent a vehicle.

The purpose of this article is to provide a guide on how to plan and prepare the database model of a car or vehicle rental system.

Based on the list of tables presented in this article, our team can create a full functional car rental system in the following programming languages:

Visual Basic and MySQL/MariaDB
C#.Net
PHP, MySQL and Bootstrap

Car Rental System List of Database Tables

Database Schema

tbladmin (admin_id, name, contact, address, username, password)

Description: The tbladmin database table stores the information of the administrator such as the name, contact, address, username and password. Administrators of the system can encode and update the customer and car owner information; it can also process the payment of customers.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tbladmin` (
`admin_id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
`contact` varchar(11) NOT NULL,
`address` varchar(100) NOT NULL,
`username` varchar(30) NOT NULL,
`password` varchar(30) NOT NULL,
PRIMARY KEY (`admin_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblcar (car_id, car_name, description, car_model_year, car_brand, color, capacity, plate_number, rate, owner_id, status)

Description: The owner of the car is the one who encodes the details into the system. Car information and details are encoded/recorded in the tblcar database table; it includes the year model of the car, the brand, color, capacity and plate number. The tblcar is linked or connected to the tblower table through its foreign key (owner_id).

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tblcar` (
`car_id` int(11) NOT NULL AUTO_INCREMENT,
`car_name` varchar(30) NOT NULL,
`description` varchar(100) NOT NULL,
`car_model_year` int(4) NOT NULL,
`car_brand` varchar(25) NOT NULL,
`color` varchar(15) NOT NULL,
`capacity` int(3) NOT NULL,
`plate_number` varchar(11) NOT NULL,
`rate` float NOT NULL,
`owner_id` int(11) NOT NULL,
`status` int(2) NOT NULL,
PRIMARY KEY (`car_id`),
KEY `owner_id` (`owner_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblcarimage (image_id, image_description, car_id)

Description: the car owner can upload multiple images of the car and it will be stored in tblcarimage database table. The table includes three entities; image_id is the primary key, image_description is the name and details of the image and the car_id (foreign key) which serves as the link between the tblcar and tblcarimage.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tblcarimage` (
`image_id` int(11) NOT NULL AUTO_INCREMENT,
`image_description` varchar(50) NOT NULL,
`car_id` int(11) NOT NULL,
PRIMARY KEY (`image_id`),
KEY `car_id` (`car_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblcarreview (review_id, review, review_score, date, customer_id)

Description: the customers of the system can rate and give feedback to the car rented through the review and comment section of the system. The table consists of five entities; the primary key of the table (review_id), the comment, the score (1-5, five as the highest), the date, and the customer_id which is the foreign key and the primary key of the tblcustomer table.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tblcarreview` (
`review_id` int(11) NOT NULL AUTO_INCREMENT,
`review` varchar(100) NOT NULL,
`review_score` int(1) NOT NULL,
`date` date NOT NULL,
`customer_id` int(11) NOT NULL,
`car_id` int(11) NOT NULL,
PRIMARY KEY (`review_id`),
KEY `customer_id` (`customer_id`),
KEY `car_id` (`car_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblcustomer (customer_id, customer_name, address, contact, profile_image, fb_account, username, password, admin_id, account_status)

Description: customers of the system can register into the platform and it will be verified by the admin once the customer submits all the credentials needed. The table includes the name, address, contact, image, the facebook account, username and password. The account_status field serves as the flag as to whether the account is active or deactivated.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tblcustomer` (
`customer_id` int(11) NOT NULL AUTO_INCREMENT,
`customer_name` varchar(50) NOT NULL,
`address` varchar(100) NOT NULL,
`contact` varchar(11) NOT NULL,
`profile_image` blob NOT NULL,
`fb_account` varchar(50) NOT NULL,
`username` varchar(30) NOT NULL,
`password` varchar(30) NOT NULL,
`admin_id` int(11) NOT NULL,
`account_status` int(1) NOT NULL,
PRIMARY KEY (`customer_id`),
KEY `admin_id` (`admin_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblcustomercredential (credential_id, credential_name, file_upload, customer_id)

Description: the customers will need first to upload the necessary documents to verify its identity. The credentials uploaded by the customers will be stored in the tblcustomercredential.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tblcustomercredential` (
`credential_id` int(11) NOT NULL AUTO_INCREMENT,
`credential_name` varchar(30) NOT NULL,
`file_upload` blob NOT NULL,
`customer_id` int(11) NOT NULL,
PRIMARY KEY (`credential_id`),
KEY `customer_id` (`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblcarowner (owner_id, owner_name, address, contact, profile_image, fb_account, username, password, admin_id, account_status)

Description: there are two ways in order for the car owners to be part of the platform; (1) is to register the information through the registration module of the system or (2) fill-up the hardcopy form and submit it to the administrators for encoding.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tblcarowner` (
`owner_id` int(11) NOT NULL AUTO_INCREMENT,
`owner_name` varchar(50) NOT NULL,
`address` varchar(100) NOT NULL,
`contact` varchar(11) NOT NULL,
`profile_image` blob NOT NULL,
`fb_account` varchar(50) NOT NULL,
`username` varchar(30) NOT NULL,
`password` varchar(30) NOT NULL,
`admin_id` int(11) NOT NULL,
`account_status` int(1) NOT NULL,
PRIMARY KEY (`owner_id`),
KEY `admin_id` (`admin_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblcarownercredential (owner_credential_id, credential_name, file_upload, owner_id)

Description: the car owner needs also to submit personal credentials and valid documents to verify its identity and the ownership of the vehicle.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tblcarownercredential` (
`owner_credential_id` int(11) NOT NULL AUTO_INCREMENT,
`credential_name` varchar(30) NOT NULL,
`file_upload` blob NOT NULL,
`owner_id` int(11) NOT NULL,
PRIMARY KEY (`owner_credential_id`),
KEY `customer_id` (`owner_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblpayment (payment_id, rental_id, payment_amount, add_charges, payment_date, admin_id)

Description: the payment of rental will be stored in the tblpayment database table.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tblpayment` (
`payment_id` int(11) NOT NULL AUTO_INCREMENT,
`rental_id` int(11) NOT NULL,
`payment_amount` float NOT NULL,
`add_charges` float NOT NULL,
`payment_date` date NOT NULL,
`admin_id` int(11) NOT NULL,
PRIMARY KEY (`payment_id`),
KEY `rental_id` (`rental_id`,`admin_id`),
KEY `admin_id` (`admin_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblrental (rental_id, rental_date, rental_time, return_date, owner_id, car_id, customer_id, rental_status )

Description: car rental process will be stored in the tblrental database table; it has eight entities, (1) rental_id which serves as the primary key of the table, (2) the rental_date, (3) rental_time, (4) return_date – the date that will end the rental agreement, (5) the owner_id – this is the foreign key which links to the tblowner table, (6) car_id – another foreign key that links to the tblcar table, (7) customer_id – this foreign key links the tblcustomer, and (8) the rental_status – it means if the rental agreement has been confirmed, cancelled or completed.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tblrental` (
`rental_id` int(11) NOT NULL AUTO_INCREMENT,
`rental_date` date NOT NULL,
`rental_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`return_date` date NOT NULL,
`owner_id` int(11) NOT NULL,
`car_id` int(11) NOT NULL,
`customer_id` int(11) NOT NULL,
`rental_status` int(2) NOT NULL,
PRIMARY KEY (`rental_id`),
KEY `owner_id` (`owner_id`,`car_id`,`customer_id`),
KEY `car_id` (`car_id`),
KEY `customer_id` (`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Car Rental System Database Design

Constraints for dumped tables

—
— Constraints for table `tblcar`
—
ALTER TABLE `tblcar`
ADD CONSTRAINT `tblcar_ibfk_1` FOREIGN KEY (`owner_id`) REFERENCES `tblowner` (`owner_id`) ON DELETE CASCADE ON UPDATE CASCADE;

—
— Constraints for table `tblcarimage`
—
ALTER TABLE `tblcarimage`
ADD CONSTRAINT `tblcarimage_ibfk_1` FOREIGN KEY (`car_id`) REFERENCES `tblcar` (`car_id`) ON DELETE CASCADE ON UPDATE CASCADE;

—
— Constraints for table `tblcarreview`
—
ALTER TABLE `tblcarreview`
ADD CONSTRAINT `tblcarreview_ibfk_1` FOREIGN KEY (`car_id`) REFERENCES `tblcar` (`car_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblcarreview_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 (`admin_id`) REFERENCES `tbladmin` (`admin_id`) ON DELETE CASCADE ON UPDATE CASCADE;

—
— Constraints for table `tblcustomercredential`
—
ALTER TABLE `tblcustomercredential`
ADD CONSTRAINT `tblcustomercredential_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `tblcustomer` (`customer_id`) ON DELETE CASCADE ON UPDATE CASCADE;

—
— Constraints for table `tblowner`
—
ALTER TABLE `tblowner`
ADD CONSTRAINT `tblowner_ibfk_1` FOREIGN KEY (`admin_id`) REFERENCES `tbladmin` (`admin_id`) ON DELETE CASCADE ON UPDATE CASCADE;

—
— Constraints for table `tblownercredential`
—
ALTER TABLE `tblownercredential`
ADD CONSTRAINT `tblownercredential_ibfk_1` FOREIGN KEY (`owner_id`) REFERENCES `tblowner` (`owner_id`) ON DELETE CASCADE ON UPDATE CASCADE;

—
— Constraints for table `tblpayment`
—
ALTER TABLE `tblpayment`
ADD CONSTRAINT `tblpayment_ibfk_1` FOREIGN KEY (`admin_id`) REFERENCES `tbladmin` (`admin_id`) ON DELETE CASCADE ON UPDATE CASCADE;

—
— Constraints for table `tblrental`
—
ALTER TABLE `tblrental`
ADD CONSTRAINT `tblrental_ibfk_1` FOREIGN KEY (`car_id`) REFERENCES `tblcar` (`car_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblrental_ibfk_2` FOREIGN KEY (`owner_id`) REFERENCES `tblowner` (`owner_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblrental_ibfk_3` FOREIGN KEY (`customer_id`) REFERENCES `tblcustomer` (`customer_id`) ON DELETE CASCADE ON UPDATE CASCADE;

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.

capstone project, capstone project ideas, capstone project in information technology, capstone project in it, capstone project sample for information technology, capstone project title in information technology, capstone proposal in it, free download capstone project in information technology, free download capstone project source code, it capstone project, it capstone sample documentation, it project, it thesis proposal, project design management system proposal, sample thesis documentation in information technology, system analysis and design proposal presentation, systemdevelopment, thesis documentation for information technology, thesis documentation in informationtechnology, thesis titles in information technology, thesis titles in it, title proposal for system analysis and design

▲