Boarding House and Dormitory System Database Project

Web Based Boarding House and Dormitory System Database Project

About the Project

The project entitled Boarding House and Dormitory Management System is a project designed and developed using web technologies such as PHP, MySQL and Bootstrap. Since the project is a browser or web based, it simply means that it will work online and it can also be implemented in offline settings. The system was developed to improve and provide solutions to the needs of boarding houses and dormitories as well. With this system, it will be easier for the managers and landlords/landladies to enter the information, process and provide accurate records.

A Web Based Boarding House and Dormitory System is a platform that provides an online booking system for customers looking for accommodation in boarding houses and dormitories. The system provides a user-friendly interface for customers to search for available rooms, check room availability, and make reservations. The system is designed to manage the daily operations of boarding houses and dormitories, making it easier for the management to keep track of customer information, bookings, payments, and room information.

The system also provides security features such as user authentication and access control to ensure that sensitive information is protected. The management of boarding houses and dormitories can use the system to manage room information, update room availability, and keep track of payments. The system also provides reports on customer activity, booking and payment history, and occupancy rate. The Web Based Boarding House and Dormitory System is an essential tool for the management of boarding houses and dormitories, making it easier to manage the daily operations of the business, while providing a convenient and user-friendly platform for customers.

Why Database Design is Important?

Database design is an important step in the development of a database-driven system because it lays the foundation for the way data will be stored, organized, and accessed. The quality of the database design can have a significant impact on the overall performance, functionality, and maintenance of the system.

A well-designed database can ensure data consistency, accuracy, and integrity, while also reducing the risk of data loss or corruption. It can also help to minimize redundancy, improve data access times, and simplify the process of making changes or updates to the data.

Additionally, a good database design can make the system more scalable and flexible, allowing it to accommodate future growth or changes in requirements. It can also improve the security of the data, making it more difficult for unauthorized users to access or manipulate the data.

Database Table

The “tbl_user” table in the Boarding House and Dormitory System is used to store information about users of the system. Each row in the table represents a single user. The columns of the table are as follows:

• user_id: This is the primary key of the table, used to uniquely identify each user. It is usually set to auto_increment, meaning that the database will automatically assign increasing values to this column as new users are added to the system.
• username: This is the username chosen by the user, which is combined with the password to allow them to log in to the system.
• password: This is the password chosen by the user, which is combined with the username to allow them to log in to the system.
• contact_name: This is the name of the person associated with the user account, which may or may not be the same as the username.
• contact: This is the contact information for the user, such as a phone number or email address.
• account_type: This column specifies the type of user account, such as an administrator or a regular user. This information is used to control what actions the user is allowed to perform within the system.

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(50) NOT NULL,
`password` varchar(50) NOT NULL,
`contact_name` varchar(100) DEFAULT NULL,
`contact` varchar(50) DEFAULT NULL,
`account_type` enum('admin','regular') NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

The tbl_booking table stores information about bookings made for beds in the boarding house or dormitory.

• booking_id is the primary key of the table and is usually set to auto-increment.
• bed_id is a foreign key that points to the table storing information about beds.
• customer_id is a foreign key that points to the table storing information about customers.
• start_date and end_date represent the period of time for which the bed has been booked.
• total_amount is the total amount that the customer has to pay for the booking.
• status is a field that indicates whether the booking is pending, approved, or cancelled.
• user_id is a foreign key that points to the table storing information about users. It identifies the user who made the booking.

Create SQL Statement – the statement below is used to create the tbl_booking, 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,
`bed_id` int(11) DEFAULT NULL,
`customer_id` int(11) DEFAULT NULL,
`start_date` date DEFAULT NULL,
`end_date` date DEFAULT NULL,
`total_amount` decimal(10,2) DEFAULT NULL,
`status` varchar(20) DEFAULT NULL,
`user_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

The table “tbl_payment” stores information about payments made by customers for their bookings in a boarding house or dormitory system. The columns in the table are:

• payment_id – the primary key of the table, which is typically set to auto_increment to give each payment a unique identifier.
• booking_id – a foreign key that points to the corresponding booking in the “tbl_booking” table. This allows the system to associate a payment with a specific booking.
• date_from – the start date of the payment period.
• date_to – the end date of the payment period.
• payment_amount – the total amount paid by the customer for the specified period.
• discount – the amount of discount applied to the payment, if any.
• user_id – a foreign key that points to the user who processed the payment in the “tbl_user” table. This allows the system to track who entered 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,
`date_from` date NOT NULL,
`date_to` date NOT NULL,
`payment_amount` decimal(10,2) NOT NULL,
`discount` decimal(10,2) DEFAULT NULL,
`user_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

“tbl_customer” is a database table that stores information about the customers of a boarding house and dormitory system. The table has the following columns:

• customer_id – this is the primary key of the table, which is set to auto_increment so that the database will automatically assign a unique identifier to each new customer record.
• name – the name of the customer.
• contact – the contact details of the customer, such as their phone number.
• address – the address of the customer.
• age – the age of the customer.
• email_address – the email address of the customer.
• username – the username chosen by the customer to log into the system.
• password – the password chosen by the customer to secure their account in the system.

The customer information stored in this table is useful for managing customer accounts, tracking customer history and demographics, and allowing customers to log into the system to view and manage their bookings.

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

CREATE TABLE `tbl_customer` (
`customer_id` int(11) NOT NULL,
`name` varchar(50) NOT NULL,
`contact` varchar(20) NOT NULL,
`address` varchar(100) NOT NULL,
`age` int(11) DEFAULT NULL,
`email_address` varchar(100) DEFAULT NULL,
`username` varchar(50) NOT NULL,
`password` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

The “tbl_guardian” table is a database table in the Boarding House and Dormitory System that stores information about the guardians of customers (residents).

The following is an explanation of the columns in the table:

• guardian_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 – this is a foreign key that points out to the customer in the “tbl_customer” table. It references the customer_id column in the “tbl_customer” table, allowing you to associate each guardian with a specific customer.
• name – the name of the guardian.
• contact – the contact information of the guardian, such as a phone number or email address.
• address – the address of the guardian.

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

CREATE TABLE `tbl_guardian` (
`guardian_id` int(11) NOT NULL,
`customer_id` int(11) DEFAULT NULL,
`name` varchar(255) NOT NULL,
`contact` varchar(255) NOT NULL,
`address` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

The “tbl_room_type” table is used to store information about the different types of rooms available in the boarding house or dormitory. It contains two columns:

• type_id: This is the primary key of the table and is set to auto-increment (the database will automatically give this column a value starting from 1).
• name: This is the name of the room type, such as “Single”, “Double”, “Triple”, etc. This column is used to identify and distinguish between the different room types available in the boarding house or dormitory.

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

CREATE TABLE `tbl_room_type` (
`type_id` int(11) NOT NULL,
`name` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

The database table “tbl_room” is part of a Boarding House and Dormitory System project. It stores information about the rooms available in the system. The table has several columns:

• room_id – the primary key of the table. It is usually set to auto_increment (the database will automatically assign a unique value starting from 1) to identify each room in the system.
• type_id – a foreign key that points to the “tbl_room_type” table, which defines the different room types available.
• number – a unique identifier for the room, such as the room number.
• description – a brief description of the room, including its size and amenities.
• name – a name for the room, which could be used to distinguish it from other rooms.
• number_of_beds – the number of beds available in the room. This information is important for determining the number of customers who can be accommodated in 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,
`type_id` int(11) NOT NULL,
`number` varchar(50) NOT NULL,
`description` text DEFAULT NULL,
`name` varchar(50) DEFAULT NULL,
`number_of_beds` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

The tbl_bed database table is used to store information about beds in a boarding house or dormitory system.

The table has the following fields:

• bed_id – the primary key of the table, typically set to auto-increment, so that the database will automatically assign a unique identifier for each bed in the system.
• room_id – a foreign key that references the room_id in the tbl_room table, indicating which room the bed belongs to.
• bed_number – a unique identifier for the bed within the room.
• price – the cost of renting the bed. This information can be used to calculate the total amount due for a customer’s stay, based on the number of nights they book a bed and the price per night.

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

CREATE TABLE `tbl_bed` (
`bed_id` int(11) NOT NULL,
`room_id` int(11) NOT NULL,
`bed_number` int(11) NOT NULL,
`price` decimal(10,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

FREE DOWNLOAD DATABASE

Summary

The main objective and target of this article is to design and develop a database for Boarding House and Dormitory System. The proposed system can solve the problems presented above. The objective can be achieved by designing a browser based or web-based application that will convert the manual process into computer based. In summary, database design is important because it plays a critical role in ensuring the overall success of the database-driven system by providing a stable and efficient foundation for data storage, organization, and access. 

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

Readers are also interested in:

Web Based Boarding House and Dormitory Management System

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.