Bike Rental System Database Design
The project entitled bike rental system is a multi-store platform that allows bike store or shop owners to post their bikes available for rental. The web application will serve as a middle man between the bike owners and customers. It also offers an ads management system which allows a bike owner to advertise their products on a specific location of the website (header, sidebar, footer, floating, sticky).
This article will provide you with the list of tables and field/columns for every table in the design of database structure/schema of bike rental system. The team will later provide a video tutorial on how to create the database in PHPMyAdmin.
This database design has 10 tables with their respective fields and columns as well as their relationships among each other.
tblbikeinfo – this table will hold the records and information of the bike. It has 8 fields or columns.
- bike_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).
- bike_category_id – this refers and links to the category of bike in the tblbikecategory.
- shop_id – refers to the owner of the bike that points out to the tblshopinfo.
- bike_name – name of the bike.
- specs – detailed specification of the bike (bike parts and components).
- rent_price – the rental amount of the bike per day.
- availability – this refers if the bike is available or not, it contains 0 or 1 value (0-not available, 1-available).
- user_id – the user who encodes the bike information.
Create SQL Statement – the statement below is used to create the tblbikeinfo, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.
CREATE TABLE `tblbikeinfo` ( `bike_id` int(11) NOT NULL, `bike_category_id` int(11) NOT NULL, `shop_id` int(11) NOT NULL, `bike_name` varchar(30) NOT NULL, `specs` varchar(100) NOT NULL, `rent_price` float NOT NULL, `availability` int(1) NOT NULL, `user_id` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
tblbikecategory – this is the table that stores the different bike categories. The table has 3 columns.
- 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 – the name of the category.
- description – a brief information about the bike category.
Create SQL Statement – the statement below is used to create the tblbikecategory, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.
CREATE TABLE `tblbikecategory` ( `category_id` int(11) NOT NULL, `category_name` varchar(30) NOT NULL, `description` varchar(100) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
tblshopinfo – this table will store the information of the store or shop.
- 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 `tblshopinfo` ( `shop_id` int(11) NOT NULL, `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 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
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 `tblclient` ( `client_id` int(11) NOT NULL, `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 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
tblrental – this table will store the rental transactions between the shop owner and the customer. The said table has 10 columns.
- rental_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).
- bike_id – this is a foreign key that links to the bike information table.
- client_id – this is a foreign key that links to the client information table.
- rental_start_date – the date the bike will be rented.
- rental_end_date – the expected date the bike will be returned.
- total_amount – total amount of rental fee, it is equal to the amount of bike rental per day times the number of days.
- payment_status – it stores 0 and 1 value, 0 means not paid, 1 for paid.
- rental_status – it stores 0 and 1 value, 0 means not yet returned, 1 for returned.
- remarks – additional information about the transacation.
- user_id – the user who processed the transaction.
Create SQL Statement – the statement below is used to create the tblrental, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.
CREATE TABLE `tblrental` ( `rental_id` int(11) NOT NULL, `bike_id` int(11) NOT NULL, `client_id` int(11) NOT NULL, `rental_start_date` date NOT NULL, `rental_end_date` date NOT NULL, `total_amount` float NOT NULL, `payment_status` int(1) NOT NULL, `rental_status` int(1) NOT NULL, `remarks` varchar(100) NOT NULL, `user_id` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
tblpayment – this table will store and hold the payment information of the customer.
- 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).
- rental_id – this is a foreign key that points out to the rental table information.
- payment_type – payment type column can contain 0,1, and 2, 0 for cash, 1, for cheque, and 2 for credit card.
- paid_by – person who pays the transactions, it is usually the customer.
- payment_date – the date of payment.
- remarks – other information about the payment transaction.
- user_id – the user who processed the transaction.
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 `tblpayment` ( `payment_id` int(11) NOT NULL, `rental_id` int(11) NOT NULL, `payment_type` int(1) NOT NULL, `paid_by` varchar(30) NOT NULL, `payment_date` date NOT NULL, `remarks` varchar(100) NOT NULL, `user_id` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
tblpenalty – this table stores the information of the penalties. If the customer fails to return the bike in the specified return date or if the bike sustain a damage, then a penalty will be charge for the customer.
- penalty_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).
- rental_id – this is a foreign key that points out to the rental table information.
- penalty_amount – the penalty amount to be paid by the customer.
- payment_status – it stores 0 and 1 value, 0 means not paid, 1 for paid.
- remarks – additional information about the transaction.
- paid_by – person who pays the transactions, it is usually the customer.
- user_id – the user who processed the transaction.
Create SQL Statement – the statement below is used to create the tblpenalty, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.
CREATE TABLE `tblpenalty` ( `penalty_id` int(11) NOT NULL, `rental_id` int(11) NOT NULL, `penalty_amount` float NOT NULL, `payment_status` int(1) NOT NULL, `remarks` varchar(100) NOT NULL, `paid_by` varchar(30) NOT NULL, `user_id` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
tbladsmanagement – the table that stores the advertisement information. It has 10 columns.
- ads_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).
- ad_name – name of the advertisement.
- shop_id – the shop information details that links to the tblshopinfo.
- banner_image – the image of the ads.
- description – information about the ads.
- start_date – the date of the ads it will start to display in the platform.
- end_date – the date the ads will stop from displaying in the platform.
- ad_location – location of the ads
- amount – the amount to be paid by the shop.
- user_id – the user who processed the transaction.
Create SQL Statement – the statement below is used to create the tbladsmanagement, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.
CREATE TABLE `tbladsmanagement` ( `ads_id` int(11) NOT NULL, `ad_name` varchar(30) NOT NULL, `shop_id` int(11) NOT NULL, `banner_image` blob NOT NULL, `description` varchar(100) NOT NULL, `start_date` date NOT NULL, `end_date` date NOT NULL, `ad_location` int(1) NOT NULL, `amount` float NOT NULL, `user_id` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
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 `tbluser` ( `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;
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 `tblusergroup` ( `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 table `tblshopinfo` -- ALTER TABLE `tblshopinfo` ADD PRIMARY KEY (`shop_id`), ADD KEY `updated_by` (`updated_by`); -- -- Indexes for table `tbluser` -- ALTER TABLE `tbluser` ADD PRIMARY KEY (`user_id`), ADD KEY `user_category_id` (`user_category_id`); -- -- Indexes for table `tblusergroup` -- ALTER TABLE `tblusergroup` ADD PRIMARY KEY (`user_group_id`); -- -- AUTO_INCREMENT for dumped tables -- -- -- AUTO_INCREMENT for table `tbladsmanagement` -- ALTER TABLE `tbladsmanagement` MODIFY `ads_id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `tblbikecategory` -- ALTER TABLE `tblbikecategory` MODIFY `category_id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `tblbikeinfo` -- ALTER TABLE `tblbikeinfo` MODIFY `bike_id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `tblclient` -- ALTER TABLE `tblclient` MODIFY `client_id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `tblpayment` -- ALTER TABLE `tblpayment` MODIFY `payment_id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `tblpenalty` -- ALTER TABLE `tblpenalty` MODIFY `penalty_id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `tblrental` -- ALTER TABLE `tblrental` MODIFY `rental_id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `tblshopinfo` -- ALTER TABLE `tblshopinfo` MODIFY `shop_id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `tbluser` -- ALTER TABLE `tbluser` MODIFY `user_id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `tblusergroup` -- ALTER TABLE `tblusergroup` MODIFY `user_group_id` int(11) NOT NULL AUTO_INCREMENT; -- -- Constraints for dumped tables -- -- -- Constraints for table `tbladsmanagement` -- ALTER TABLE `tbladsmanagement` ADD CONSTRAINT `tbladsmanagement_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbluser` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT `tbladsmanagement_ibfk_2` FOREIGN KEY (`shop_id`) REFERENCES `tblshopinfo` (`shop_id`) ON DELETE CASCADE ON UPDATE CASCADE; -- -- Constraints for table `tblbikeinfo` -- ALTER TABLE `tblbikeinfo` ADD CONSTRAINT `tblbikeinfo_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbluser` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT `tblbikeinfo_ibfk_2` FOREIGN KEY (`bike_category_id`) REFERENCES `tblbikecategory` (`category_id`) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT `tblbikeinfo_ibfk_3` FOREIGN KEY (`shop_id`) REFERENCES `tblshopinfo` (`shop_id`) ON DELETE CASCADE ON UPDATE CASCADE; -- -- Constraints for table `tblpayment` -- ALTER TABLE `tblpayment` ADD CONSTRAINT `tblpayment_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbluser` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT `tblpayment_ibfk_2` FOREIGN KEY (`rental_id`) REFERENCES `tblrental` (`rental_id`) ON DELETE CASCADE ON UPDATE CASCADE; -- -- Constraints for table `tblpenalty` -- ALTER TABLE `tblpenalty` ADD CONSTRAINT `tblpenalty_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbluser` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT `tblpenalty_ibfk_2` FOREIGN KEY (`rental_id`) REFERENCES `tblrental` (`rental_id`) ON DELETE CASCADE ON UPDATE CASCADE; -- -- Constraints for table `tblrental` -- ALTER TABLE `tblrental` ADD CONSTRAINT `tblrental_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbluser` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT `tblrental_ibfk_2` FOREIGN KEY (`bike_id`) REFERENCES `tblbikeinfo` (`bike_id`) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT `tblrental_ibfk_3` FOREIGN KEY (`client_id`) REFERENCES `tblclient` (`client_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 `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; COMMIT;
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.
