Online Document Request Database Design

Online Document Request Database Design

Introduction to Database Design

In any online document request system, the database serves as the backbone, ensuring that data is organized, secure, and easily accessible! Its primary purpose is to facilitate seamless interactions between the users and the system, enabling efficient storage and retrieval of essential information such as student details, document requests, and payment transactions! By centralizing data in a structured way, the database minimizes redundancy, enhances system performance, and guarantees data integrity!

The database design for this system incorporates several interconnected tables that work together to streamline processes! For instance, the tbl_student table stores comprehensive student profiles, while the tbl_request table tracks the lifecycle of document requests from submission to completion! These tables are linked through primary and foreign keys, enabling efficient cross-referencing and data consistency! Payment-related information is handled through tbl_payment and tbl_paymongo_logs, ensuring that financial transactions are accurately recorded and easily traceable!

By understanding how these tables interact, you’ll see how this system simplifies the management of document requests while providing students with a user-friendly experience! The design ensures scalability and reliability, making it capable of handling growing user demands over time!

How to Create Online Document Request with PayMongo Integration

Database Tables and Their Functions

The database for this system is designed with precision, ensuring each table serves a distinct purpose and collectively powers the seamless management of document requests! Below is a breakdown of each table and its primary function:

1. tbl_course_grade: This table manages the hierarchy of courses and grade levels by storing information like course grade codes and names! It ensures proper classification of students within their respective academic programs!
2. tbl_student: A vital table that holds student details such as personal information, contact details, and login credentials! It also establishes a link to the corresponding course grade using course_grade_id!
3. tbl_document_info: Defines the types of documents available for request, including descriptions, processing durations, and associated fees!
4. tbl_delivery_type: Tracks the delivery methods available for document requests, such as pickup or courier services, along with detailed descriptions!
5. tbl_paymongo_api: Stores credentials and configuration settings for integrating the PayMongo payment gateway, including API keys and environment settings (sandbox or live)!
6. tbl_philsms_settings: Configures the SMS notification system by storing the API key and sender ID, enabling real-time updates on request statuses!
7. tbl_request: The central table for managing document requests! It tracks details such as the requested document type, number of copies, status, and delivery options!
8. tbl_payment: This table records payment transactions, including total amounts, payment statuses, and PayMongo payment IDs for reference!
9. tbl_paymongo_logs: Maintains a detailed log of PayMongo payment responses for auditing and troubleshooting purposes!

Each table is intricately connected through relationships, ensuring the system operates efficiently while maintaining data consistency and integrity! This well-structured design simplifies request management and enhances the user experience!

Database Tables and Their Columns

Below is a detailed breakdown of the database tables and their respective columns. Each table is uniquely designed to manage specific aspects of the system effectively:

tbl_course_grade

• course_grade_id: Primary key to uniquely identify each course-grade entry.
• course_grade_code: A unique code for the course-grade combination.
• course_grade_name: The name or description of the course-grade.

tbl_student

• student_id: Primary key to identify each student.
• student_id_number: Unique identifier for each student, typically issued by the institution.
• student_complete_name: Full name of the student.
• complete_address: Address of the student.
• student_contact_number: Contact number of the student.
• student_email_address: Email address for communication.
• student_profile_image: Path to the student’s profile image.
• username: Username for system login.
• password: Encrypted password for account security.
• course_grade_id: Foreign key linking to tbl_course_grade.

tbl_document_info

• document_id: Primary key to uniquely identify each document type.
• document_name: Name of the document (e.g., Transcript, Diploma).
• description: Brief description of the document.
• number_of_days_to_process: Time required to process the document.
• amount_to_pay: Cost of the document.

tbl_delivery_type

• delivery_type_id: Primary key to identify delivery types.
• delivery_type_name: Name of the delivery method (e.g., Pickup, Courier).
• delivery_type_description: Description of the delivery method.

tbl_paymongo_api

• api_id: Primary key to identify API configurations.
• api_name: Name of the API configuration.
• api_key: Public key for API access.
• api_secret: Private key for API security.
• environment: Indicates whether the API is in sandbox or live mode.
• updated_at: Timestamp for the last update of API details.

tbl_philsms_settings

• id: Primary key to identify SMS settings.
• api_key: Key for the SMS API.
• sender_id: Identifier for the SMS sender.

tbl_request

• request_id: Primary key for each document request.
• control_number: Unique identifier in the format (year_student_id_number_uniqueid).
• student_id: Foreign key linking to the student requesting the document.
• document_id: Foreign key linking to the requested document type.
• number_of_copies: Number of document copies requested.
• date_of_request: Date when the request was made.
• date_of_releasing: Scheduled release date of the document.
• user_id: ID of the staff processing the request.
• r_status: Status of the request (e.g., Pending, Processing, Completed).
• remarks: Additional notes for the request.
• delivery_type_id: Foreign key linking to the chosen delivery type.

tbl_payment

• payment_id: Primary key for each payment record.
• control_number: Links the payment to the corresponding request.
• total_amount: Total cost of the transaction.
• amount_paid: Amount received from the student.
• date_of_payment: Date when the payment was made.
• status: Status of the payment (e.g., Paid, Unpaid).
• paymongo_payment_id: Reference ID from PayMongo.

tbl_paymongo_logs

• log_id: Primary key for each log entry.
• payment_id: Foreign key linking to tbl_payment.
• request_id: Foreign key linking to tbl_request.
• paymongo_payment_id: Reference ID for the payment in PayMongo.
• status: Status of the transaction (e.g., Success, Failure).
• response: Detailed response from PayMongo.
• created_at: Timestamp for when the log entry was created.

This comprehensive structure ensures efficient management of student requests, payments, and notifications while maintaining data integrity across the system!

Primary Keys and Relationships

In database design, primary keys and foreign keys play a critical role in ensuring data integrity and enabling relationships between tables. Below is an explanation of the primary keys in each table and how foreign keys establish relationships across the database.

Primary Keys in Each Table

A primary key uniquely identifies each record in a table. The following are the primary keys for the tables:

• tbl_course_grade: course_grade_id uniquely identifies each course-grade entry.
• tbl_student: student_id is the unique identifier for each student.
• tbl_document_info: document_id uniquely identifies document types.
• tbl_delivery_type: delivery_type_id uniquely identifies delivery methods.
• tbl_paymongo_api: api_id uniquely identifies PayMongo API configurations.
• tbl_philsms_settings: id uniquely identifies SMS settings.
• tbl_request: request_id uniquely identifies document requests.
• tbl_payment: payment_id uniquely identifies payment transactions.
• tbl_paymongo_logs: log_id uniquely identifies each PayMongo log entry.

How Foreign Keys Establish Relationships

Foreign keys link tables to create relationships, allowing data to be referenced across the system. Here are the key relationships:

1. Student to Course Grade:
   • tbl_student.course_grade_id is a foreign key referencing tbl_course_grade.course_grade_id.
   • This links students to their corresponding course-grade information.
2. Requests to Students:
   • tbl_request.student_id is a foreign key referencing tbl_student.student_id.
   • This connects each document request to the specific student making the request.
3. Requests to Documents:
   • tbl_request.document_id is a foreign key referencing tbl_document_info.document_id.
   • This associates each request with the type of document being requested.
4. Requests to Delivery Types:
   • tbl_request.delivery_type_id is a foreign key referencing tbl_delivery_type.delivery_type_id.
   • This links a document request to the chosen delivery method.
5. Payments to Requests:
   • tbl_payment.control_number references tbl_request.control_number.
   • This ensures that payments are tied to their corresponding document requests.
6. PayMongo Logs to Payments and Requests:
   • tbl_paymongo_logs.payment_id is a foreign key referencing tbl_payment.payment_id.
   • tbl_paymongo_logs.request_id references tbl_request.request_id.
   • These relationships enable tracking of payment processing logs for specific requests and payments.

Example Relationship Flow

• A student is associated with a course and grade through tbl_course_grade.
• The student makes a document request, which is recorded in tbl_request and linked back to the student via student_id.
• The request may require a payment, recorded in tbl_payment and linked through the control_number.
• If PayMongo is used for the transaction, its details are logged in tbl_paymongo_logs for auditing.

By defining primary and foreign keys, the database ensures referential integrity and smooth data flow across the entire system. This structure supports efficient query execution and reduces redundancy!

Summary

The database serves as the backbone of the system, ensuring the smooth operation of document requests and related processes. With its well-structured tables, primary keys, and relationships, the database enables efficient management of student information, document requests, payment transactions, and delivery tracking. This structure ensures data integrity while minimizing redundancy, allowing the system to perform seamlessly in real-time scenarios. By leveraging these interconnected components, the database enhances the overall user experience, providing a reliable and intuitive platform for both administrators and students.

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.