# ddhruvin/myretaildb architecture

> Architecture diagram of the GitHub repository ddhruvin/myretaildb, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2025-05-06). It is an AI-made overview: check details against the source.

- Interactive diagram: https://gitdiagram.com/ddhruvin/myretaildb
- Repository: https://github.com/ddhruvin/myretaildb

## Overview

##### Project type and purpose
- This is a single-component database project: a MySQL schema dump (`retail.sql`) plus a README explaining how to restore and query a “retail” database.
- It contains table definitions (employees, customers, products, orders), sample data, stored procedures, triggers, and example queries to analyze sales and enforce business rules.

##### File structure
- Top level:
- LICENSE
- README.md → contains restore instructions, sample outputs, query use cases, trigger/procedure descriptions
- retail.sql → full DDL/DML, triggers, stored-procedure code
- No frontend/backend code; the “application” is entirely in the database.

##### README for additional insights
- Explains:
- Tables and expected query outputs
- Stored procedure: inserts a new order and updates stock
- Trigger: prevents stock going negative
- Sample analytical queries (total revenue, top product, etc.)
- Restoration steps via CLI or Workbench

##### Elements
- Clear labels:
- Components (Client CLI/Workbench, MySQL Server, Retail DB)
- Tables with PK/FK relationships
- Stored Procedure and Trigger blocks
- From client tools to server (SQL commands)
- From stored procedure to tables it modifies
- From trigger to table it enforces
- Rectangles for tables (e.g., blue)
- Ovals for procedures (e.g., green)
- Diamonds for triggers (e.g., orange)

## Diagram (Mermaid)

```mermaid
flowchart TD
    %% Client Tools
    CLI["MySQL CLI"]:::client
    WB["MySQL Workbench"]:::client

    %% Import Script
    RetailSQL["retail.sql"]:::script

    %% Database Server and Schema
    Server["MySQL Server 8.0+"]:::server
    subgraph "Retail Database Schema" 
        direction TB
        Employees["Employees"]:::table
        Customers["Customers"]:::table
        Products["Products"]:::table
        Orders["Orders"]:::table

        CreateOrder(("CreateOrder()")):::proc
        PreventNegativeStock{"PreventNegativeStock"}:::trigger
    end

    %% Data & Control Flows
    CLI -->|"runs restore/query SQL"| RetailSQL
    WB -->|"runs restore/query SQL"| RetailSQL
    RetailSQL -->|"imports schema & data"| Server
    Server -->|"hosts schema"| Employees
    Server -->|"hosts schema"| Customers
    Server -->|"hosts schema"| Products
    Server -->|"hosts schema"| Orders

    Orders -->|"FK:customer_id"| Customers
    Orders -->|"FK:product_id"| Products
    Orders -->|"FK:employee_id"| Employees

    Server -->|"executes"| CreateOrder
    CreateOrder -->|"INSERT INTO Orders"| Orders
    CreateOrder -->|"UPDATE Products.stock"| Products

    Orders -->|"AFTER INSERT/UPDATE"| PreventNegativeStock
    PreventNegativeStock -->|"checks stock ≥ 0 on"| Products

    %% Click Events
    click RetailSQL "https://github.com/ddhruvin/myretaildb/blob/main/retail.sql"
    click CreateOrder "https://github.com/ddhruvin/myretaildb/blob/main/retail.sql"
    click PreventNegativeStock "https://github.com/ddhruvin/myretaildb/blob/main/retail.sql"
    click README["README.md"] "README.md"
    click License["LICENSE"] "LICENSE"

    %% Nodes for docs
    README["README.md"]:::doc
    License["LICENSE"]:::doc
    README --> RetailSQL
    License --> README

    %% Styles
    classDef client fill:#D6E9C6,stroke:#333,stroke-width:1px
    classDef script fill:#FFF2CC,stroke:#333,stroke-width:1px
    classDef server fill:#DDDDDD,stroke:#333,stroke-width:1px
    classDef table fill:#CFE2F3,stroke:#333,stroke-width:1px
    classDef proc fill:#D9EAD3,stroke:#333,stroke-width:1px
    classDef trigger fill:#F9CB9C,stroke:#333,stroke-width:1px
    classDef doc fill:#EAD1DC,stroke:#333,stroke-width:1px
```
