Garbage Collection Scheduling System Database Design

Garbage Collection Scheduling System Database Design

Introduction

Welcome to the Garbage Collection Scheduling System with SMS Notification. This innovative solution is designed to streamline waste management processes by automating collection schedules and enhancing communication through SMS alerts. With this system, managing garbage collection becomes more efficient, organized, and responsive to community needs.

Purpose and Benefits

The primary goal of this system is to simplify and optimize garbage collection operations. Here’s how it benefits users:

• Efficient Scheduling: Automates the scheduling of garbage collection, reducing manual errors and ensuring timely pickups.
• Enhanced Communication: Sends SMS notifications to residents about collection schedules and changes, improving awareness and compliance.
• Centralized Management: Provides a single platform for managing collection centers, trucks, and schedules, making it easier for waste management authorities to oversee operations.

Importance of Database Design

A well-structured database is crucial for the success of this system. It organizes and stores data efficiently, which is essential for:

• Data Integrity: Ensures that all information about collection schedules, centers, and member interactions is accurate and reliable.
• Performance Optimization: Facilitates quick retrieval and updating of data, which is vital for real-time notifications and scheduling adjustments.
• Scalability: Supports the system’s growth and adaptation to new requirements or increased data volume without compromising performance.

Effective database design underpins the smooth operation of the Garbage Collection Scheduling System, ensuring that it delivers on its promise of efficiency and reliability.

IOT Garbage Monitoring System using Arduino with Bluetooth Application

Requirements Analysis

Identifying the Core Components

To build an effective Garbage Collection Scheduling System with SMS Notification, it’s essential to identify and understand its core components. This analysis will guide the development and implementation of the system, ensuring all necessary features are included and function seamlessly together.

Garbage Collection Scheduling

Purpose: The scheduling component automates the process of planning garbage collection activities.

Key Features:

• Automated Scheduling: Allows for the creation and management of collection schedules based on predefined intervals or on-demand.
• Real-Time Updates: Enables adjustments to schedules in response to unforeseen events, such as truck breakdowns or changes in collection requirements.
• Detailed Tracking: Provides visibility into scheduled and completed collections, helping to manage and optimize routes and timings.

Collection Sites Management

Purpose: This component handles the management of various garbage collection centers and sites.

Key Features:

• Site Information: Stores and manages details of collection centers, including addresses and contact information.
• Location Tracking: Integrates geographical data to assist in route planning and optimize collection efficiency.
• Center Coordination: Facilitates coordination between different collection sites, ensuring coverage and efficient resource allocation.

SMS Notification Integration

Purpose: The SMS notification system enhances communication by sending timely updates to residents and stakeholders.

Key Features:

• Automated Alerts: Sends notifications about collection schedules, changes, or issues directly to residents’ mobile phones.
• Customizable Messages: Allows for personalized messages based on user preferences or specific events.
• Status Updates: Provides real-time updates on collection status, such as delays or cancellations, to keep residents informed.

By focusing on these core components—garbage collection scheduling, collection sites management, and SMS notification integration—the system aims to deliver a comprehensive solution that improves waste management efficiency and communication.

Explanation of the Tables

Here’s an explanation of each table in your database design:

tbl_barangay

This table stores information about different barangays (local administrative divisions).

• location_id: Unique identifier for each barangay.
• barangay: Name of the barangay.
• complete_address: Full address of the barangay.
• contact: Contact number for the barangay.
• email_address: Email address for communication with the barangay.

tbl_purok

This table records details about different puroks (neighborhood subdivisions within a barangay).

• purok_id: Unique identifier for each purok.
• location_id: Foreign key linking to tbl_barangay, indicating the barangay to which this purok belongs.
• purok_name: Name of the purok.

tbl_garbage_collection_center

This table, also used in the evacuation system, holds information about garbage collection centers.

• center_id: Unique identifier for each collection center.
• name: Name of the garbage collection center.
• complete_address: Full address of the center.
• longitude: Longitude coordinate for the center’s location.
• latitude: Latitude coordinate for the center’s location.

tbl_member

Originally from the laundry system, this table contains details about members who are associated with different puroks.

• member_id: Unique identifier for each member.
• purok_id: Foreign key linking to tbl_purok, showing which purok the member belongs to.
• member_name: Name of the member.
• member_address: Address of the member.
• member_email: Email address of the member.
• member_contact: Contact number of the member.
• member_username: Username for the member’s account.
• member_password: Password for the member’s account.
• member_profile_image: Profile image of the member.
• latitude: Latitude coordinate for the member’s location.
• longitude: Longitude coordinate for the member’s location.
• status: Current status of the member (active or inactive).

tbl_garbage_truck_info

This table stores information about garbage trucks used for collection.

• garbage_truck_info_id: Unique identifier for each garbage truck.
• truck_info: Details about the truck, such as model or type.
• plate_no: License plate number of the truck.

tbl_collection

This table records details about garbage collection events.

• collection_id: Unique identifier for each collection event.
• collectors: Names or IDs of the collectors involved.
• schedule_date_time: Date and time when the collection is scheduled to occur.
• garbage_truck_info_id: Foreign key linking to tbl_garbage_truck_info, indicating the truck assigned for the collection.
• purok_id: Foreign key linking to tbl_purok, showing which purok is being serviced.
• status: Current status of the collection (completed, cancelled, or rescheduled).
• remarks: Additional notes or comments regarding the collection event.

tbl_comments

This table contains comments made by members regarding the collection system.

• comment_id: Unique identifier for each comment.
• member_id: Foreign key linking to tbl_member, indicating the member who made the comment.
• comment_date_time: Date and time when the comment was made.
• comments: Content of the comment.

tbl_sms_info

This table holds the configuration details for SMS notifications.

• account_sid: Account SID for SMS service (e.g., Twilio).
• auth_token: Authentication token for accessing the SMS service.
• twilio_number: The phone number used for sending SMS messages.

This database design ensures all necessary information is captured and managed effectively for the garbage collection scheduling system with SMS notifications.

Garbage Collection Scheduling System with SMS Notification

Relationships and Constraints

Defining Primary Keys and Foreign Keys

Primary Keys: Each table in a database should have a primary key that uniquely identifies each record within that table. This key is essential for ensuring that each row can be distinctly referenced.

• Example: In the tbl_barangay table, location_id serves as the primary key, uniquely identifying each barangay.

Foreign Keys: Foreign keys establish relationships between tables by referring to the primary key of another table. They enforce referential integrity, ensuring that relationships between tables remain consistent.

• Example: In the tbl_purok table, location_id is a foreign key that links to location_id in the tbl_barangay table. This relationship signifies which barangay each purok belongs to.

Example: center_id as a Foreign Key in tbl_collection

To illustrate the use of foreign keys, consider the tbl_collection table:

• tbl_collection:
  • collection_id (Primary Key)
  • collectors
  • schedule_date_time
  • garbage_truck_info_id (Foreign Key)
  • purok_id (Foreign Key)
  • status
  • remarks
• Foreign Key Relationships:
  • garbage_truck_info_id refers to garbage_truck_info_id in tbl_garbage_truck_info, linking each collection event to a specific garbage truck.
  • purok_id refers to purok_id in tbl_purok, indicating which purok the collection is associated with.
  • In some designs, center_id could be a foreign key if you’re tracking which collection center is managing the collection, though it is not shown in your current schema.

Implementing Referential Integrity

Referential integrity ensures that the relationships between tables remain consistent. It involves several practices:

1. Enforcing Foreign Key Constraints: Ensure that a foreign key value in a table must match a primary key value in the referenced table or be NULL if allowed. This prevents orphaned records and maintains data accuracy.
2. Cascading Actions: Define how updates and deletions in the primary table affect related records in the foreign table. Common actions include:
   • CASCADE: Automatically updates or deletes related records.
   • SET NULL: Sets the foreign key to NULL if the referenced record is deleted.
   • RESTRICT: Prevents deletion of a record if it has related foreign key references.
3. Data Validation: Implement validation rules to ensure that data entered into foreign key fields conforms to the expected format and exists in the referenced table.

Ensuring Data Consistency Across Tables

1. Data Validation Rules: Establish rules to ensure data integrity. For example, enforce valid addresses, email formats, and unique identifiers.
2. Regular Audits: Perform regular checks and audits to identify and correct inconsistencies or errors in the database.
3. Database Constraints: Use constraints such as UNIQUE, CHECK, and NOT NULL to enforce data integrity at the column level.
4. Referential Integrity Maintenance: Regularly review foreign key constraints and relationships to ensure they reflect current data relationships and business rules.

By defining clear primary and foreign keys, implementing referential integrity, and ensuring data consistency, you can maintain a well-structured and reliable database for your Garbage Collection Scheduling System with SMS Notification.

SQL Statements

-- Create tbl_barangay table
CREATE TABLE tbl_barangay (
location_id INT AUTO_INCREMENT PRIMARY KEY,
barangay VARCHAR(255) NOT NULL,
complete_address VARCHAR(255) NOT NULL,
contact VARCHAR(50),
email_address VARCHAR(100)
);

-- Create tbl_purok table
CREATE TABLE tbl_purok (
purok_id INT AUTO_INCREMENT PRIMARY KEY,
location_id INT,
purok_name VARCHAR(255) NOT NULL,
FOREIGN KEY (location_id) REFERENCES tbl_barangay(location_id)
);

-- Create tbl_garbage_collection_center table
CREATE TABLE tbl_garbage_collection_center (
center_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
complete_address VARCHAR(255) NOT NULL,
longitude DECIMAL(10, 7),
latitude DECIMAL(10, 7)
);

-- Create tbl_member table
CREATE TABLE tbl_member (
member_id INT AUTO_INCREMENT PRIMARY KEY,
purok_id INT,
member_name VARCHAR(255) NOT NULL,
member_address VARCHAR(255) NOT NULL,
member_email VARCHAR(100) NOT NULL,
member_contact VARCHAR(50),
member_username VARCHAR(50) UNIQUE NOT NULL,
member_password VARCHAR(255) NOT NULL,
member_profile_image VARCHAR(255),
latitude DECIMAL(10, 7),
longitude DECIMAL(10, 7),
status ENUM('active', 'inactive') NOT NULL,
FOREIGN KEY (purok_id) REFERENCES tbl_purok(purok_id)
);

-- Create tbl_garbage_truck_info table
CREATE TABLE tbl_garbage_truck_info (
garbage_truck_info_id INT AUTO_INCREMENT PRIMARY KEY,
truck_info VARCHAR(255),
plate_no VARCHAR(20) UNIQUE
);

-- Create tbl_collection table
CREATE TABLE tbl_collection (
collection_id INT AUTO_INCREMENT PRIMARY KEY,
collectors VARCHAR(255),
schedule_date_time DATETIME NOT NULL,
garbage_truck_info_id INT,
purok_id INT,
status ENUM('completed', 'cancelled', 'rescheduled') NOT NULL,
remarks TEXT,
FOREIGN KEY (garbage_truck_info_id) REFERENCES tbl_garbage_truck_info(garbage_truck_info_id),
FOREIGN KEY (purok_id) REFERENCES tbl_purok(purok_id)
);

-- Create tbl_comments table
CREATE TABLE tbl_comments (
comment_id INT AUTO_INCREMENT PRIMARY KEY,
member_id INT,
comment_date_time DATETIME NOT NULL,
comments TEXT NOT NULL,
FOREIGN KEY (member_id) REFERENCES tbl_member(member_id)
);

-- Create tbl_sms_info table
CREATE TABLE tbl_sms_info (
account_sid VARCHAR(255) PRIMARY KEY,
auth_token VARCHAR(255) NOT NULL,
twilio_number VARCHAR(20) NOT NULL
);

Explanation:

• Primary Keys: Each table has a primary key defined using AUTO_INCREMENT to ensure uniqueness.
• Foreign Keys: Foreign key constraints are used to establish relationships between tables, such as linking purok_id in tbl_member to purok_id in tbl_purok.
• Data Types: Use appropriate data types (e.g., VARCHAR, INT, DECIMAL, DATETIME) to match the expected data format and size.
• Enums: Enum data types are used for fields with predefined options, such as status in tbl_member and tbl_collection.

These statements will create the necessary tables for your database schema, ensuring the proper structure and relationships for your Garbage Collection Scheduling System with SMS Notification.

FREE DOWNLOAD SQL

Conclusion

In this database design for the Garbage Collection Scheduling System with SMS Notification, we have created a structured schema to support efficient waste management. The design includes essential tables to manage barangays, puroks, garbage collection centers, members, garbage trucks, collection events, comments, and SMS configuration. Key relationships and constraints have been defined to ensure data integrity and consistency.

Summary of Key Points

1. Table Definitions: The database schema consists of several tables, each serving a specific purpose:
   • tbl_barangay and tbl_purok manage location data.
   • tbl_garbage_collection_center records information about collection sites.
   • tbl_member stores details about members involved in the system.
   • tbl_garbage_truck_info provides data on garbage trucks.
   • tbl_collection tracks collection events and their statuses.
   • tbl_comments allows members to provide feedback.
   • tbl_sms_info holds configuration for SMS notifications.
2. Relationships: Foreign keys are used to establish relationships between tables, ensuring that data remains consistent and properly linked across the system. For example, purok_id in tbl_member links to tbl_purok, and garbage_truck_info_id in tbl_collection links to tbl_garbage_truck_info.
3. Constraints: Primary and foreign keys are implemented to maintain referential integrity, while ENUM types are used to define specific statuses.

Next Steps

1. Moving from Design to Implementation:
   • Database Creation: Execute the provided SQL CREATE TABLE statements to set up the database schema.
   • Data Insertion: Populate the tables with initial data to begin testing and using the system.
   • Application Development: Develop the application logic to interact with the database, handling data operations such as scheduling, collection management, and notifications.
2. Future Enhancements and Considerations:
   • Scalability: As the system grows, consider optimizing the database schema and queries to handle larger volumes of data efficiently.
   • User Feedback: Incorporate feedback from users to refine the system’s features and improve usability.
   • Advanced Features: Explore the addition of more sophisticated features, such as automated route optimization, real-time tracking, and integration with other municipal systems.
   • Security: Implement robust security measures to protect sensitive data and ensure the privacy of system users.

By transitioning from design to implementation and considering future enhancements, you will create a robust and scalable Garbage Collection Scheduling System that effectively manages waste collection and communicates with stakeholders through SMS notifications.

Readers are also interested in:

Junkshop and Recycling Center 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.