Vehicle Parking Management System Database Design

Vehicle Parking Management System Database Design

The capstone project entitled “Vehicle Parking Management System” is a platform that allows an organization to manage their parking transactions, such as the booking and payments. This system will be a benefit and bring convenience to the customers by just processing the booking of parking through the online platform.

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

Vehicle Parking Management System Database Design - List of Tables — Vehicle Parking Management System Database Design – List of Tables

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

tbl_vehicle – this is the table that store the information of the vehicle, and the following are the columns/fields of the table.

vehicle_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).
vehicle_category_id – this is a foreign key that links to the vehicle category information.
vehicle_plate_number – the registered plate number of the vehicle.
vehicle_description – description and other information of the vehicle such as the color and other relevant information.
vehicle_image – the system is requiring the owners to upload the picture of their vehicle.
vehicle_owner_id – this is a foreign key that points out to the owner of the vehicle. The design of this table is that the owners can post and register more than 1 vehicle.

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

CREATE TABLE `tbl_vehicle` (
`vehicle_id` int(11) NOT NULL,
`vehicle_category_id` int(11) NOT NULL,
`vehicle_plate_number` varchar(15) NOT NULL,
`vehicle_description` varchar(100) NOT NULL,
`vehicle_image` blob NOT NULL,
`vehicle_owner_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

tbl_vehicle_category – this table stores the different categories of the vehicle.

vehicle_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).
vehicle_category_name – name of the category (scooters, cars, motorcycle, etc).
user_id – the user who encode/manage/update the vehicle category information. It is a foreign key that connects to the user table.

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

CREATE TABLE `tbl_vehicle_category` (
`vehicle_category_id` int(11) NOT NULL,
`vehicle_category_name` varchar(30) NOT NULL,
`user_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

tbl_vehicle_owner – profile information of the customer or vehicle owner will be stored in this table. The vehicle owner table contains the following columns:

vehicle_owner_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).
vehicle_owner_name – the complete name of the owner.
avatar – this will hold the profile image of the person.
vehicle_owner_contact – mobile contact number of the customer.
vehicle_owner_email – email address of the customer
owner_username – desired username of the customer.
owner_password – desired password of the 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 customer 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 tbl_vehicle_owner, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE `tbl_vehicle_owner` (
`vehicle_owner_id` int(11) NOT NULL,
`vehicle_owner_name` varchar(30) NOT NULL,
`avatar` blob NOT NULL,
`vehicle_owner_contact` varchar(15) NOT NULL,
`vehicle_owner_email` varchar(30) NOT NULL,
`owner_username` varchar(30) NOT NULL,
`owner_password` varchar(30) NOT NULL,
`status` int(1) NOT NULL,
`user_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

tbl_parking_slot – this table stores the parking slot information, and it has 4 columns.

parking_slot_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).
parking_slot_number – the slot number or parking area number.
parking_slot_status – 0 for available and 1 for occupied 2 for reserved. This field will be updated when a booking or payment has been processed.
user_id – the user who encode/manage/update the vehicle category information. It is a foreign key that connects to the user table.

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

CREATE TABLE `tbl_parking_slot` (
`parking_slot_id` int(11) NOT NULL,
`parking_slot_number` int(4) NOT NULL,
`parking_slot_status` int(1) NOT NULL,
`user_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

tbl_booking – booking transaction are stored in this table and the following are the columns of the table:

booking_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 – foreign key that links to the customer information (the one who processed the booking)
vehicle_id – foreign key that links to the vehicle information table.
duration_of_booking – it refers to how long the booking will be (minimum is 3 hours).
slot_id – foreign key that links to the parking slot information.
booking_status – status of booking, 0 for processing, 1 for approved, 2 cancelled, 3 rejected, 4 completed.
remarks – additional information of the booking transaction.
user_id – the user who processed the booking information.

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_booking` (
`booking_id` int(11) NOT NULL,
`customer_id` int(11) NOT NULL,
`vehicle_id` int(11) NOT NULL,
`duration_of_booking` varchar(15) NOT NULL,
`slot_id` int(11) NOT NULL,
`booking_status` int(1) NOT NULL,
`remarks` varchar(100) NOT NULL,
`user_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

tbl_payment – 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).
booking_id – it is either reference number of order or service.
amount_due – the amount of money owed by the customer to the parking/booking.
amount_paid – amount paid by the customer.
remarks – additional information on the transaction.
payment_status – status of the payment (partial, fully paid)
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 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,
`booking_id` int(11) NOT NULL,
`amount_due` float NOT NULL,
`amount_paid` 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;

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

CREATE TABLE `tbl_setting` (
`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,
`vendor_username` varchar(30) NOT NULL,
`vendor_password` varchar(30) NOT NULL,
`user_id` int(11) 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.
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_booking`
--
ALTER TABLE `tbl_booking`
ADD PRIMARY KEY (`booking_id`),
ADD KEY `customer_id` (`customer_id`),
ADD KEY `vehicle_id` (`vehicle_id`),
ADD KEY `slot_id` (`slot_id`),
ADD KEY `user_id` (`user_id`);

--
-- Indexes for table `tbl_parking_slot`
--
ALTER TABLE `tbl_parking_slot`
ADD PRIMARY KEY (`parking_slot_id`),
ADD KEY `user_id` (`user_id`);

--
-- Indexes for table `tbl_payment`
--
ALTER TABLE `tbl_payment`
ADD PRIMARY KEY (`payment_id`),
ADD KEY `booking_id` (`booking_id`),
ADD KEY `user_id` (`user_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`);

--
-- Indexes for table `tbl_vehicle`
--
ALTER TABLE `tbl_vehicle`
ADD PRIMARY KEY (`vehicle_id`),
ADD KEY `vehicle_owner_id` (`vehicle_owner_id`),
ADD KEY `vehicle_category_id` (`vehicle_category_id`);

--
-- Indexes for table `tbl_vehicle_category`
--
ALTER TABLE `tbl_vehicle_category`
ADD PRIMARY KEY (`vehicle_category_id`),
ADD KEY `user_id` (`user_id`);

--
-- Indexes for table `tbl_vehicle_owner`
--
ALTER TABLE `tbl_vehicle_owner`
ADD PRIMARY KEY (`vehicle_owner_id`),
ADD KEY `user_id` (`user_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `tbl_booking`
--
ALTER TABLE `tbl_booking`
MODIFY `booking_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `tbl_parking_slot`
--
ALTER TABLE `tbl_parking_slot`
MODIFY `parking_slot_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `tbl_payment`
--
ALTER TABLE `tbl_payment`
MODIFY `payment_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;

--
-- AUTO_INCREMENT for table `tbl_vehicle`
--
ALTER TABLE `tbl_vehicle`
MODIFY `vehicle_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `tbl_vehicle_category`
--
ALTER TABLE `tbl_vehicle_category`
MODIFY `vehicle_category_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `tbl_vehicle_owner`
--
ALTER TABLE `tbl_vehicle_owner`
MODIFY `vehicle_owner_id` int(11) NOT NULL AUTO_INCREMENT;

Constraints for dumped tables

--
-- Constraints for table `tbl_booking`
--
ALTER TABLE `tbl_booking`
ADD CONSTRAINT `tbl_booking_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbl_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tbl_booking_ibfk_2` FOREIGN KEY (`customer_id`) REFERENCES `tbl_vehicle_owner` (`vehicle_owner_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tbl_booking_ibfk_3` FOREIGN KEY (`vehicle_id`) REFERENCES `tbl_vehicle` (`vehicle_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tbl_booking_ibfk_4` FOREIGN KEY (`slot_id`) REFERENCES `tbl_parking_slot` (`parking_slot_id`) ON DELETE CASCADE ON UPDATE CASCADE;

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

--
-- Constraints for table `tbl_payment`
--
ALTER TABLE `tbl_payment`
ADD CONSTRAINT `tbl_payment_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbl_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tbl_payment_ibfk_2` FOREIGN KEY (`booking_id`) REFERENCES `tbl_booking` (`booking_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;

--
-- Constraints for table `tbl_vehicle`
--
ALTER TABLE `tbl_vehicle`
ADD CONSTRAINT `tbl_vehicle_ibfk_1` FOREIGN KEY (`vehicle_category_id`) REFERENCES `tbl_vehicle_category` (`vehicle_category_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tbl_vehicle_ibfk_2` FOREIGN KEY (`vehicle_owner_id`) REFERENCES `tbl_vehicle_owner` (`vehicle_owner_id`) ON DELETE CASCADE ON UPDATE CASCADE;

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

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