Overview

Working as a Physics Lab Assistant at Fraser International College (FIC), I was tasked with maintaining the inventory using their Excel sheets - a very tedious method. To avoid the Excel sheets for maintaining inventory, I came up with an idea that would utilize a database to maintain the inventory. My supervisor was supportive of my idea, and so I started looking into tutorials that explain the fundamentals of database-driven applications.

From my research, as a beginner to full-stack development, everyone suggested starting with PHP on the backend, as it's easier to understand & implement server-side concepts. I took an introductory course to database-driven web applications on Udemy for the first 4-5 weeks. Upon finishing the course, I moved on to implementing the inventory management application for FIC using PHP & MySQL on the backend, and JavaScript & AJAX on the frontend.

Problem Statement

The existing inventory management system faced several challenges:

  • Manual Excel-based tracking was time-consuming and error-prone
  • Difficulty in maintaining accurate inventory records
  • Lack of real-time updates and notifications
  • No centralized database for inventory management
  • Limited accessibility and user interface

Solution

Developed a comprehensive web-based inventory management system with the following features:

1. User Authentication System

Secure login and account management:

  • User registration and authentication
  • Role-based access control
  • Secure password management
  • Session management and security

2. Inventory Management

Complete inventory tracking and management:

  • Add, edit, and delete inventory items
  • Real-time inventory tracking
  • Stock level monitoring and alerts
  • Inventory search and filtering

3. Database Integration

Robust database design and management:

  • MySQL database for data storage
  • Efficient data retrieval and queries
  • Data backup and recovery
  • Database optimization and performance

Technical Implementation

Frontend Development

Modern and responsive user interface:

  • HTML5 and CSS3 for structure and styling
  • JavaScript for interactive functionality
  • AJAX for asynchronous data updates
  • Responsive design for mobile compatibility

Backend Development

Robust server-side implementation:

  • PHP for server-side logic and processing
  • MySQL database for data persistence
  • RESTful API design for data operations
  • Security measures and input validation

Database Design

Efficient and scalable database architecture:

  • Normalized database schema design
  • Optimized queries for performance
  • Data integrity and constraint management
  • Backup and recovery procedures

Implemented Pages

1. Login Page

Login Page

Secure user authentication and login interface

2. Account Page

Account Page

User account management and profile settings

3. Inventory Page

Inventory Page

Main inventory dashboard with item listing and management

4. Add Product Page

Add Product Page

Form interface for adding new inventory items

Results & Impact

The implemented system provided significant improvements in inventory management:

Performance Improvements

  • Reduced inventory tracking time by 80%
  • Eliminated manual data entry errors
  • Improved data accuracy and consistency
  • Enhanced user experience and accessibility
  • Real-time inventory updates and notifications

System Benefits

  • Centralized database for all inventory data
  • User-friendly web interface
  • Automated inventory tracking and alerts
  • Scalable solution for future expansion
  • Reduced administrative overhead

Lessons Learned

This project provided valuable insights into full-stack web development:

  • Importance of proper database design and optimization
  • Value of user-centered design and interface
  • Complexity of integrating frontend and backend systems
  • Critical role of security in web applications
  • Need for comprehensive testing and validation