How ishwar-panchariya/zepto-clone-database-application-in-sql-server works
Project type and purpose
• This is a database-only backend project: a full SQL Server implementation of a Hyperlocal Delivery Platform (a Zepto/Blinkit/Dunzo clone).
• Purpose: design, build, optimize, secure, and automate a production-quality relational database for orders, inventory, users, delivery tracking, notifications, etc.
File structure
• Top‐level .sql scripts:
• database_and_table_creation.sql – schema definition (tables, PKs/FKs).
• zepto_clone_db_insert_sample_data.sql – sample data insertion.
• views, trigger, indexes, and stored procedure.sql – performance and modularization.
• role base access and security.sql – RBAC definitions (users/roles/permissions).
• schedule a job.sql – SQL Server Agent jobs for daily reports/notifications.
• Queries for Report, Data Analysis (helpful for interviews).sql – analytical queries.
• No frontend/backend code—purely database design, automation, and analytics.
README for additional insights
• Core modules (Users & Addresses, Vendors & Inventory, Products & Categories, Orders & OrderItems, Payments, Delivery Tracking, Reviews, Status Management, Notifications).
• Emphasis on normalization, indexing, triggers, stored procedures, performance tuning, RBAC, scheduled jobs.
a. Main components
• Relational Entities: each table group as a module (Users, Vendors, Products, Orders, Payments, Delivery, Reviews, Statuses, Notifications).
• Database Engine: SQL Server instance.
• Automation Layer: SQL Server Agent (jobs for reports, notifications).
• Security Layer: Role-Based Access Control (Admin/Vendor/Delivery roles).
• Analytics/Reporting: ad-hoc query layer or BI tool (represented by analytical queries).
b. Relationships & interactions
• Triggers firing on INSERT/UPDATE in inventory and orders to update stock and statuses.
• Stored procedures called by application layers or SQL Agent jobs.
• Scheduled jobs invoking stored procedures for daily summaries and sending notifications.
c. Architectural patterns & design principles
• Normalized schema (3NF+) illustrated by separated modules/tables.
• Modularization: separate views, triggers, stored procedures layers.
• Automation/Orchestration: SQL Server Agent for scheduled tasks.
• Security by Roles: separate schemas or security contexts for RBAC.
d. Key technologies
• Microsoft SQL Server / T-SQL.
• SQL Server Agent for job scheduling.
• ERD as the conceptual blueprint.
• Indexes and Performance-tuning tools (Execution plans).
• Optional BI/Reporting layer (Power BI / SSRS / ad-hoc tools).