This repository contains a complete, production-grade warehouse database system designed for large-scale e-commerce platforms such as Amazon. Although originally developed as part of an academic initiative in 2016, the system has been fully restructured for real-world commercial deployment, reflecting deep expertise in business-oriented data modeling and operational logic.
-
Structured Entity Relationships
Includes normalized tables for inventory, categories, purchase types, warehouses, and item tracking. -
Real-World Schema
Designed with actual warehouse operations in mind, including multi-level categorization and cross-referencing. -
25 Predefined SQL Queries
Users can select from a curated list of high-utility queries via a user panel, modify parameters, and view results instantly. -
Modular Design
Easily extendable to support invoicing, supplier management, and real-time stock updates. -
Business Logic Ready
Built to support decision-making, reporting, and integration with ERP systems.
📁 docs/ → Schema diagrams, real data source references, invoice logic
📁 sql/ → Table creation scripts, seed data, 25 optimized SQL queries
📁 screenshots/ → Visual previews of schema, dashboard, and query panel
This table lists 25 pre-defined SQL queries designed for real-time warehouse operations. Each query is parameterized and accessible via the user panel, allowing users to filter results without editing SQL manually. Queries are sorted by practical relevance and business impact.
| Query ID | Title | Description |
|---|---|---|
| QUERY 01 | Top 5 Inventory Transactions After Date | Shows the latest 5 inventory movements after a selected timestamp. |
| QUERY 02 | Items Below Reorder Threshold | Lists items that need restocking based on minimum quantity rules. |
| QUERY 03 | Current Stock by Category | Displays total stock grouped by item category. |
| QUERY 04 | Warehouse-wise Inventory Summary | Shows inventory count per warehouse for operational overview. |
| QUERY 05 | Top 10 Fastest Moving Items | Identifies items with highest movement frequency. |
| QUERY 06 | On-Hold Inventory Details | Lists items currently on hold or pending clearance. |
| QUERY 07 | All Inventory by Purchase Type | Filters inventory based on purchase classification (e.g. wholesale, retail). |
| QUERY 08 | Highest Priced Items in Warehouse | Displays top-valued items for financial tracking. |
| QUERY 09 | Most Recent Inventory Transactions | Shows the latest transactions across all warehouses. |
| QUERY 10 | Top 3 Most Moved Items in 30 Days | Highlights the most frequently moved items in the past month. |
| QUERY 11 | Current Stock of Specific Type | Filters stock based on item type (e.g. electronics, apparel). |
| QUERY 12 | Total Value of Current Inventory | Calculates total monetary value of all current stock. |
| QUERY 13 | Total Order Cost by Category | Aggregates order costs grouped by item category. |
| QUERY 14 | Inventory Movement by Responsible Manager | Tracks item movements by warehouse manager ID. |
| QUERY 15 | Items Received in Specific Date Range | Filters items received between two selected dates. |
| QUERY 16 | Inventory Transactions by Item ID | Shows all movements for a specific item. |
| QUERY 17 | Items with No Movement in 60 Days | Identifies stagnant inventory. |
| QUERY 18 | Average Item Price by Category | Calculates average price per category. |
| QUERY 19 | Inventory Count by Purchase Type | Displays item counts grouped by purchase classification. |
| QUERY 20 | Daily Inventory Movement Summary | Summarizes movements per day. |
| QUERY 21 | Monthly Inventory Value Trend | Shows inventory value trend over months. |
| QUERY 22 | Items Assigned to Specific Warehouse | Filters items by warehouse ID. |
| QUERY 23 | Inventory Movement by Item Type | Tracks movement grouped by item type. |
| QUERY 24 | Total Orders by Supplier | Aggregates orders per supplier. |
| QUERY 25 | Inventory Movement by Invoice Type | Filters movements based on invoice classification. |
- Current stock by category
- Items below reorder threshold
- Warehouse-wise inventory summary
- Purchase trends by item type
- Invoice breakdown by supplier
- Most moved items in last 30 days
- Dynamic search with variable filters
- And 18 more queries tailored for operational insight
Each query is designed to be parameterized, allowing users to adjust filters directly from the panel interface without writing SQL manually.
A user-friendly interface for selecting and executing 25 predefined SQL queries with real-time parameter control.
Entity-Relationship Diagram showing all tables, keys, and relationships in the warehouse system.
This system was originally developed as part of my B.Sc. thesis in Computer Engineering. However, it has since evolved into a commercial-grade solution, reflecting my commitment to scalable architecture, business logic, and real-world deployment. The design showcases my ability to think in structured layers, optimize for performance, and align technical decisions with business goals.
- E-commerce warehouse management
- Inventory tracking and reporting
- Supplier and invoice coordination
- ERP integration
- Data-driven decision support
Released under the MIT License. Free to use, modify, and extend with attribution.
Ehsan Lesani
AI Solutions Architect | AI Engineer | Full-Stack Dev | Remote-first CTO | Business-Oriented System Designer
🔗 Official Site
📂 Linkedin Profile
📧 Contact for Commercial Collaboration