Boarding House Management System Free Database Design Tutorial

Boarding House Management System Free Database Design Tutorial

Introduction

It is an online system that allows boarding house managers to more effectively manage their boarding house related transactions and activities.

Boarding house managers can use the system to organize and manage the residents, rooms, and services at their boarding house. As an added feature, board house managers can control the finances of their establishment, including the establishment’s monthly budget as well as its daily and weekly spending patterns.

Furthermore, the system enables boarding house managers to generate reports concerning the operation and upkeep of all facilities at their boarding house for easy reference. Through the use of an Internet browser on a computer or any other device that is linked to the Internet, users can gain access to the system.

Benefits of the Boarding House Management System

The utilization of an online boarding home management system has a number of advantages and benefits.

On top of that, when it comes to managing boarding house operations, an online boarding house management system is efficient and effective to utilize. This software makes it simple and efficient for boarding house operators to manage bookings, room reservations, guest arrivals and departures, as well as invoices and payments. Additionally, an online boarding house management system can assist in lowering the operating costs of a boarding house. Furthermore, it increases transparency while also increasing the overall security of the boarding house.

Second, an online boarding house management system is a cost-effective solution for boarders. It saves time for operators because visitors no longer have to fill out all of the forms manually, which saves them time. Furthermore, there are no additional fees associated with using an online boarding house management system rather than a manual process. As a result, boarding house operators save both time and money as a result of the automation process.

Third, visitors will find it simple to go through the online boarding house administration system. It enables customers in reserving a boarding house room without experiencing any difficulties or confusion regarding the process of booking a boarding house room. Furthermore, it facilitates the management of reservations for landline and wireless phone numbers, as well as email.

Database Tables

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

tbl_tenant – this table will store the information of the tenants that are registered in the system.

• tenant_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).
• last_name – last name of the tenant
• first_name – first name of the tenant
• middle_name – middle name of the tenant
• complete_address – complete address of the tenant
• email_address – the tenant’s email address
• contact_number – the contact number of the
• gender – the gender of the tenant
• profile_picture – the profile photo of the tenant
• valid_documents – valid documents submitted by the tenants

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

CREATE TABLE `tbl_tenant` (
`tenant_id` int(11) NOT NULL,
`last_name` varchar(30) NOT NULL,
`first_name` varchar(30) NOT NULL,
`middle_name` varchar(30) NOT NULL,
`complete_address` varchar(200) NOT NULL,
`email_address` varchar(50) NOT NULL,
`contact_number` varchar(15) NOT NULL,
`gender` int(1) NOT NULL,
`profile_picture` longblob NOT NULL,
`valid_documents` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_room – the information of the rooms inside the boarding house is stored in this table.

• room_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).
• room_number – the assigned number of the room
• description – description of the room
• image – upload image of the room

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

CREATE TABLE `tbl_room` (
`room_id` int(11) NOT NULL,
`room_number` int(3) NOT NULL,
`description` varchar(200) NOT NULL,
`image` longblob NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_bed_management – this table will store information of the beds in the boarding house encoded in the system.

• bed_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).
• bed_number – the assigned number of the bed
• room_id – this is a foreign key that points out to the room where the bed is situated
• daily_rent – the rate for the bed’s daily rent
• monthly_rent – the rate for the bed’s monthly rent
• bed_status – available, occupied

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

CREATE TABLE `tbl_bed_management` (
`bed_id` int(11) NOT NULL,
`bed_number` int(3) NOT NULL,
`room_id` int(11) NOT NULL,
`daily_rent` float NOT NULL,
`monthly_rent` float NOT NULL,
`bed_status` int(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_bed_assignment – this table will s\tore the information of the beds assigned to the tenants.

• assignment_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).
• tenant_id – this is a foreign key that points out to the tenant
• room_id – this is a foreign key that points out to room where the room is.
• bed_id – this is a foreign key that points to the table bed.
• date_started – the date when the bed was assigned to the tenant.
• due_date – due date of the payment for the rent’s rate.

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

CREATE TABLE `tbl_bed_assignment` (
`assignment_id` int(11) NOT NULL,
`tenant_id` int(11) NOT NULL,
`room_id` int(11) NOT NULL,
`bed_id` int(11) NOT NULL,
`date_started` date NOT NULL,
`due_date` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_rate_setting –  this table will hold the information of the water and electricity rate setting for the boarding house.

• water_bill_rate – the amount for the water bill rate
• electricity_bill_rate – the amount for the electricity rate

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

CREATE TABLE `tbl_rate_setting` (
`water_bill_rate` float NOT NULL,
`electricity_bill_rate` float NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_sms_setting –  the informationf for the sms setting will be held in this table.

• api_code – this is a unique code for the api
• api_password – the password of the api
• set_alarm – alarm for the sms notif
• message – the short message to serve as notification

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

CREATE TABLE `tbl_sms_setting` (
`api_code` varchar(50) NOT NULL,
`api_password` text NOT NULL,
`set_alarm` datetime NOT NULL,
`message` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_invoice –  the records of invoices willl 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).
• tenant_id – this is a foreign key that points out to the tenant.
• room_id – this is a foreign key that points out to the room table
• room_rate – the rate for the room’s rent
• water_bill_rate – the amount of the water bill rate
• electricity_bill_rate – the amount of the electricity bill rate
• total_due – the total amount to be payed for
• date – the date the invoice record was created
• remarks – additional information or description of the invoice
• status – paid, unpaid
• processed_by – this is a foreign key that links to the user table. It refers to the one who processed 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,
`tenant_id` int(11) NOT NULL,
`room_id` int(11) NOT NULL,
`room_rate` float NOT NULL,
`water_bill_rate` float NOT NULL,
`electricity_bill_rate` float NOT NULL,
`total_due` float NOT NULL,
`date` date NOT NULL,
`remarks` varchar(100) NOT NULL,
`status` int(1) NOT NULL,
`processed_by` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_payment –  this table will store the information of the payments for the boarding house rates.

• 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).
• or_number – the unique number for the official receipt
• payment_amount – the amount for the payment made
• date – the date for the payment
• proof_of_payment – proof for the payment
• remarks – additional information for the payment that is made
• status – pending, approved, cancelled
• processed_by – this is a foreign key that links to the user table. It refers to the one who processed the invoice.

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,
`or_number` varchar(15) NOT NULL,
`payment_amount` float NOT NULL,
`date` date NOT NULL,
`proof_of_payment` longblob NOT NULL,
`remarks` varchar(100) NOT NULL,
`status` int(1) NOT NULL,
`processed_by` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_notice_board – this table will store the information for the notice board intended for the tenant

• notice_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).
• title – the title for the notice to be disseminated
• content – the content of the notice
• status – publish, unpublish

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

CREATE TABLE `tbl_notice_board` (
`notice_id` int(11) NOT NULL,
`title` varchar(15) NOT NULL,
`content` varchar(100) NOT NULL,
`status` int(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_suggestions – the suggestions made by the tenant will be stored in this table.

• suggestion_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).
• tenant_id – this is a foreign key that points out to the tenant.
• suggestion – the suggestion made by the tenants
• date – the date the suggestion was made
• reply_from_owner – the reply of the boarding house owner for the suggestion
• status – pending, solved, on-going

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

CREATE TABLE `tbl_suggestions` (
`suggestion_id` int(11) NOT NULL,
`tenant_id` int(11) NOT NULL,
`suggestion` varchar(200) NOT NULL,
`date` date NOT NULL,
`reply_from_owner` varchar(200) NOT NULL,
`status` int(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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.
• 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` text NOT NULL,
`avatar` longblob NOT NULL,
`fullname` varchar(100) NOT NULL,
`contact` varchar(15) NOT NULL,
`email` varchar(30) NOT NULL,
`status` int(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Summary

The main purpose of this article is to provide you with the idea and example on how to create a database for the project entitled boarding management system.  We hope that this tutorial has provided you with information that might help in designing and developing your own version of the boarding house management system or similar projects related to it such as dormitory management or hotel room reservation.

Please watch the video tutorial that will be posted on our YouTube Channel.

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 Links and Articles:

Web Based Boarding House and Dormitory Management System

Dormitory Management System in PHP and MySQL Capstone Project

Boarding House and Dormitory System ER Diagram

Dormitory Management System Review of Related Literature

Dormitory Management System in PHP and MySQL User Interface

Community Home Owners Information Portal