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.

Boarding House Management System - Admin Dashboard — Boarding House Management System – Admin Dashboard

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

Boarding House Management System Free Database Design Tutorial - List of Tables — Boarding House Management System Free Database Design Tutorial – List of 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.

Introduction

Benefits of the Boarding House Management System

Database Tables

Summary

Leave a Comment Cancel reply