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;
Vehicle Parking Management System Database Design
Vehicle Parking Management System Database Design

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.

Comments

comments

, , ,

Post navigation