# ishwar-panchariya/zepto-clone-database-application-in-sql-server architecture

> Architecture diagram of the GitHub repository ishwar-panchariya/zepto-clone-database-application-in-sql-server, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2025-05-31). It is an AI-made overview: check details against the source.

- Interactive diagram: https://gitdiagram.com/ishwar-panchariya/zepto-clone-database-application-in-sql-server
- Repository: https://github.com/ishwar-panchariya/zepto-clone-database-application-in-sql-server

## Overview

##### 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).

## Diagram (Mermaid)

```mermaid
flowchart TB
    %% Project Overview
    subgraph "Project Overview"
        README["README.md"]:::docs
    end
    click README "https://github.com/ishwar-panchariya/zepto-clone-database-application-in-sql-server/blob/main/README.md"

    %% External Client
    Client["Client/Application"]:::external
    Client -->|"calls Stored Procedures"| DB

    %% Database Engine
    DB[(SQL Server Engine)]:::db
    click DB "https://github.com/ishwar-panchariya/zepto-clone-database-application-in-sql-server/blob/main/database_and_table_creation.sql"

    %% Security Layer
    Security["Role-Based Access Control"]:::security
    Security --> DB
    click Security "https://github.com/ishwar-panchariya/zepto-clone-database-application-in-sql-server/blob/main/role base access and security.sql"

    %% Relational Modules
    subgraph "Relational Entity Modules"
        Users["Users & Addresses"]:::entity
        Vendors["Vendors & Inventory"]:::entity
        Products["Products & Categories"]:::entity
        Orders["Orders & OrderItems"]:::entity
        Payments["Payments"]:::entity
        Delivery["Delivery Tracking"]:::entity
        Reviews["Reviews"]:::entity
        Statuses["Status Management"]:::entity
        NotificationsMod["Notifications"]:::entity
    end
    DB --> Users
    DB --> Vendors
    DB --> Products
    DB --> Orders
    DB --> Payments
    DB --> Delivery
    DB --> Reviews
    DB --> Statuses
    DB --> NotificationsMod

    %% Sample Data
    SampleData["Sample Data Insertion"]:::data
    DB -->|"populates tables"| SampleData
    click SampleData "https://github.com/ishwar-panchariya/zepto-clone-database-application-in-sql-server/blob/main/zepto_clone_db_insert_sample_data.sql"

    %% Modular SQL Logic
    ViewsSP["Views, Triggers, Indexes & Stored Procedures"]:::logic
    DB --> ViewsSP
    click ViewsSP "https://github.com/ishwar-panchariya/zepto-clone-database-application-in-sql-server/blob/main/views, trigger, indexes, and stored procedure.sql"

    %% Trigger Example
    Orders -->|“OnOrderInsert → Update InventoryTrigger”| Vendors

    %% Automation Layer
    Agent["SQL Server Agent Jobs"]:::agent
    Agent -->|"runs Stored Procedures"| DB
    click Agent "https://github.com/ishwar-panchariya/zepto-clone-database-application-in-sql-server/blob/main/schedule a job.sql"

    %% Analytics / Reporting
    Analysis["Analytics / Reporting Queries"]:::analysis
    DB --> Analysis
    click Analysis "https://github.com/ishwar-panchariya/zepto-clone-database-application-in-sql-server/blob/main/Queries for Report, Data Analysis (helpful for interviews).sql"

    %% Styles
    classDef entity fill:#ADD8E6,stroke:#333,stroke-width:1px
    classDef db fill:#90EE90,stroke:#333,stroke-width:1px
    classDef logic fill:#FFFACD,stroke:#333,stroke-width:1px
    classDef data fill:#E0FFFF,stroke:#333,stroke-width:1px
    classDef security fill:#FFC0CB,stroke:#333,stroke-width:1px
    classDef agent fill:#FFA500,stroke:#333,stroke-width:1px
    classDef analysis fill:#D3D3D3,stroke:#333,stroke-width:1px,stroke-dasharray: 5 5
    classDef docs fill:#E6E6FA,stroke:#333,stroke-width:1px
    classDef external fill:#F0F8FF,stroke:#333,stroke-width:1px
```
