Project Report: Advanced Logic & Desk Availability

Objective

The goal of this task was to implement logic that prevents double booking of desks by checking availability before creating a new membership. This involved optimizing database queries, improving user feedback, and ensuring efficient execution through indexing.

---

Steps

1. Database Optimization – Adding Indexes**

To optimize search queries for desk availability, an index was added to the memberships table for the columns desk_id, start_date, and end_date.

• A migration file was created and modified to include indexing.
• The migration was executed to apply the changes.

Screenshot: "Migration execution result"

---

2. Desk Availability Check in Controller

The store method in MembershipController was updated to check if a desk is already booked during the requested period before creating a new membership.

• The exists() method was used for an optimized query.
• If a booking conflict is detected, an error message is returned.

Screenshot: "Desk availability check logic in the controller"

---

3. Advanced Queries: Optimizing Desk Availability Checks

Indexing Strategy

To speed up the query checking for desk availability, an index was created on the desk_id, start_date, and end_date columns. This significantly improves the performance of WHERE conditions filtering by date ranges.

Why Indexing Helps?

• Speeds up search queries: The database can locate records faster instead of scanning the entire table.
• Optimizes WHERE conditions: Queries involving date ranges benefit from indexed searches rather than full-table scans.

Screenshot: SHOW INDEX FROM memberships;

To verify that the index is being used correctly, the EXPLAIN statement was executed in MySQL for a simple query filtering by start_date.

Screenshot: EXPLAIN SELECT * FROM memberships WHERE start_date execution result

Additionally, a more complex query filtering by both start_date and end_date was executed to observe index performance when checking overlapping dates.

Screenshot: EXPLAIN SELECT * FROM memberships WHERE start_date = '2025-02-01' AND end_date = '2025-02-21' execution result.

---

4. UI Feedback for Booking Conflicts

A validation error message was added to the create.blade.php template to inform users if they attempt to book an occupied desk.

• The error message dynamically appears when a user selects an unavailable desk.

Screenshot: "UI error when selecting an occupied desk"

---

5. GitHub Commit and Push

After confirming that the feature worked as expected, all changes were committed and pushed to GitHub.

_Screenshot: "Successful commit of changes" _

---

Conclusion

The implementation ensures that desks cannot be double-booked by validating new memberships against existing ones. Query performance was improved using indexing, and UI feedback was added for better user experience. After successful testing, the changes were committed and pushed to the repository. The system is now more robust and prevents booking conflicts effectively.