StockHub Ltd - Stock Management System (SMS)

Entity Relationship Diagram (ERD) Description

Entities and Attributes

1. Users

• id (Primary Key)
• username
• password
• createdAt

2. Products

• productCode (Primary Key)
• productName
• category
• quantityInStock
• unitPrice
• supplierName
• dateReceived
• warehouseCode (Foreign Key → Warehouses)

3. Warehouses

• warehouseCode (Primary Key)
• warehouseName
• warehouseLocation

4. StockTransactions

• transactionId (Primary Key)
• transactionDate
• quantityMoved
• transactionType
• productCode (Foreign Key → Products)
• warehouseCode (Foreign Key → Warehouses)

Relationships and Cardinalities

• Warehouses to Products: One-to-Many (1:N)

  • One warehouse can store many products
  • Each product belongs to one warehouse

• Products to StockTransactions: One-to-Many (1:N)

  • One product can have many transactions
  • Each transaction relates to one product

• Warehouses to StockTransactions: One-to-Many (1:N)

  • One warehouse can have many transactions
  • Each transaction occurs at one warehouse

Features Implemented

Authentication

• User Login with session management
• User Registration (new endpoint)
• Logout functionality

Products Management

• INSERT operation (Add new products)
• Retrieve/Display all products

Warehouses Management

• INSERT operation (Add new warehouses)
• Retrieve/Display all warehouses

Transactions Management

• INSERT, UPDATE, DELETE, RETRIEVE operations (Full CRUD)
• Stock in/out tracking with automatic stock adjustment

Reports

• Daily Stock Report
• Weekly Stock Report
• Monthly Stock Report
• Stock In Report
• Stock Out Report

Bills Generation (New)

• Generate individual bills for stock out products
• Generate bills report for date ranges
• Professional invoice format with all product details

Project Structure

wor/
├── backend-project/
│   ├── server.js          # Node.js/Express backend with MySQL
│   ├── app.js             # Backend initialization
│   ├── schema.sql         # MySQL database schema
│   └── package.json       # Backend dependencies
├── frontend-project/
│   ├── src/
│   │   ├── components/
│   │   │   ├── Login.jsx      # Login page
│   │   │   ├── Register.jsx   # Registration page
│   │   │   ├── Dashboard.jsx  # Main dashboard
│   │   │   ├── Products.jsx   # Product management
│   │   │   ├── Warehouses.jsx # Warehouse management
│   │   │   ├── Transactions.jsx # Transaction management
│   │   │   ├── Reports.jsx     # Reports section
│   │   │   └── Bills.jsx       # Bill generation
│   │   ├── App.jsx        # Main application component
│   │   └── main.jsx       # Entry point
│   └── package.json       # Frontend dependencies
└── README.md              # Documentation

Technologies Used

• Backend: Node.js, Express.js, MySQL2, Express-Session
• Frontend: React.js, Tailwind CSS, Axios, React Router DOM
• Database: MySQL