# josedoria93/sql_project architecture

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

- Interactive diagram: https://gitdiagram.com/josedoria93/sql_project
- Repository: https://github.com/josedoria93/sql_project

## Overview

##### Project Type and Purpose
- This is an SQL‐only, data-centric project: a relational database implementation for a hardware store (“ferretería”) built entirely around MySQL.
- It focuses on schema definition, data import, stored objects (views, functions, procedures, triggers), backup/dump, user/role management, and containerized deployment for development and testing.

##### Main Components
a. Infrastructure Layer
- Docker Compose service running MySQL
- `.env` for credentials, `docker-compose.yml`, and `mysql_wait.sh` for startup orchestration
- Makefile targets (`test-db`, `access-db`, `backup-db`, `clean-db`) for developer/workbench operations
b. Persistence Layer
- MySQL Database `empresa_ferretera`
- Core tables (departments, clientes, proveedores, etc.)
- Reference vs. transactional vs. fact tables classification
c. Data Pipeline/Population
- CSV data folder (`sql_project/data_csv/*.csv`)
- SQL scripts for schema (`database_structure.sql`), DDL/DML (`database_objects/*.sql`), population (`population.sql`), and object imports (`check_db_objects.sql`)
d. Business Logic Layer
- Views (`.vistas.sql`), Functions, Stored Procedures, Triggers
- Role/user definitions and privileges (DBA role, user `josedp`)
e. Backup/Recovery
- `back-up/empresa_ferretera-backup.sql` and `sql_project/dump_backup/*.sql` dumps
- Makefile `backup-db` target

##### Relationships and Data Flow
- At container startup: `docker-compose` → MySQL instance
- Schema build: run `database_structure.sql` → core tables created
- Data ingestion: CSV files → population scripts (`population.sql`) → INSERT INTO tables
- Stored objects: apply `database_objects/*.sql` → views, functions, procs, triggers loaded
- Triggers automatically maintain `inventario` on inserts in `detalle_pedidos` and `detalles_compras_proveedores`
- Client access: Makefile & MySQL Workbench (via `make access-db`) → end-user queries call views/functions/procs
- Backup: `make backup-db` → dumps exported to `back-up/`
- Cleanup: `make clean-db` → resets database state

##### Architectural Patterns & Design Principles
- Layered separation: Infrastructure / Persistence / Business Logic / Data Pipeline / Administration
- Star schema-ish classification: fact tables (sales, purchases, returns), dimension/reference tables (clientes, productos, categorías)
- Encapsulation of business logic in stored procedures and functions
- Automation via Makefile and Docker for repeatable environment teardown and setup

##### Essential Elements Checklist
- [ ] Clear component labels (e.g., `docker-compose.yml`, `database_structure.sql`, `detalle_pedidos` table)
- [ ] Group related elements (e.g., under “sql_project/” folder)

## Diagram (Mermaid)

```mermaid
flowchart TB
    %% Infrastructure Layer
    subgraph "Infrastructure Layer"
        DC["docker-compose.yml"]:::infraScript
        Env[".env"]:::infraScript
        Wait["mysql_wait.sh"]:::infraScript
        Make["Makefile"]:::infraScript
    end

    %% Persistence Layer
    subgraph "Persistence Layer"
        DB["empresa_ferretera (MySQL)"]:::db
        subgraph "Tables"
            Ref["Reference Tables"]:::table
            Trans["Transactional Tables"]:::table
            Fact["Fact Tables"]:::table
        end
    end

    %% Data Pipeline / Population
    subgraph "Data Pipeline / Population"
        CSV["sql_project/data_csv/*.csv"]:::data
        Schema["sql_project/database_structure.sql"]:::data
        CheckObj["sql_project/check_db_objects.sql"]:::data
        Pop["sql_project/population.sql"]:::data
    end

    %% Business Logic Layer
    subgraph "Business Logic Layer"
        Views["sql_project/database_objects/vistas.sql"]:::logic
        Func["sql_project/database_objects/funciones.sql"]:::logic
        Procs["sql_project/database_objects/stored_procedures.sql"]:::logic
        Trigs["sql_project/database_objects/triggers.sql"]:::logic
        Users["sql_project/database_objects/User_database.sql"]:::logic
    end

    %% Backup / Recovery
    subgraph "Backup / Recovery"
        PrjBack["back-up/empresa_ferretera-backup.sql"]:::backup
        IndBack["sql_project/dump_backup/*.sql"]:::backup
        BackStore["Backup Storage"]:::backupStorage
        MakeBackup["Makefile (backup-db)"]:::infraScript
    end

    %% Administration
    subgraph "Administration"
        CLI["Make targets (test-db, access-db, clean-db)"]:::admin
    end

    %% Connections
    DC -->|"starts"| DB
    Env -->|"provides credentials"| DC
    Wait -->|"orchestrates startup"| DC
    Make -->|"defines targets"| CLI
    CLI -->|"executes operations"| DB

    Schema -->|"creates schema"| DB
    CSV -->|"feeds"| Pop
    Pop -->|"inserts data"| DB
    CheckObj -->|"validates objects"| DB

    Views -->|"loads views"| DB
    Func -->|"loads functions"| DB
    Procs -->|"loads procedures"| DB
    Trigs -->|"loads triggers"| DB
    Users -->|"sets users/roles"| DB

    MakeBackup -->|"executes backup"| PrjBack
    PrjBack -->|"stores dump"| BackStore
    IndBack -->|"stores dumps"| BackStore

    %% Click Events
    click DC "https://github.com/josedoria93/sql_project/blob/main/docker-compose.yml"
    click Env "https://github.com/josedoria93/sql_project/blob/main/.env"
    click Wait "https://github.com/josedoria93/sql_project/blob/main/mysql_wait.sh"
    click Make "https://github.com/josedoria93/sql_project/tree/main/Makefile"
    click Schema "https://github.com/josedoria93/sql_project/blob/main/sql_project/database_structure.sql"
    click CheckObj "https://github.com/josedoria93/sql_project/blob/main/sql_project/check_db_objects.sql"
    click CSV "https://github.com/josedoria93/sql_project/tree/main/sql_project/data_csv/"
    click Pop "https://github.com/josedoria93/sql_project/blob/main/sql_project/population.sql"
    click Views "https://github.com/josedoria93/sql_project/blob/main/sql_project/database_objects/vistas.sql"
    click Func "https://github.com/josedoria93/sql_project/blob/main/sql_project/database_objects/funciones.sql"
    click Procs "https://github.com/josedoria93/sql_project/blob/main/sql_project/database_objects/stored_procedures.sql"
    click Trigs "https://github.com/josedoria93/sql_project/blob/main/sql_project/database_objects/triggers.sql"
    click Users "https://github.com/josedoria93/sql_project/blob/main/sql_project/database_objects/User_database.sql"
    click PrjBack "https://github.com/josedoria93/sql_project/blob/main/back-up/empresa_ferretera-backup.sql"
    click IndBack "https://github.com/josedoria93/sql_project/tree/main/sql_project/dump_backup/"

    %% Styles
    classDef infraScript fill:#D8BFD8,stroke:#9370DB,stroke-width:1px
    classDef db fill:#ADD8E6,stroke:#0000CD,stroke-width:1px,stroke-dasharray: 5 5
    classDef table fill:#E0FFFF,stroke:#00CED1,stroke-width:1px
    classDef data fill:#98FB98,stroke:#228B22,stroke-width:1px
    classDef logic fill:#FFA500,stroke:#FF8C00,stroke-width:1px
    classDef backup fill:#F08080,stroke:#CD5C5C,stroke-width:1px
    classDef backupStorage fill:#FA8072,stroke:#B22222,stroke-width:1px,stroke-dasharray: 3 3
    classDef admin fill:#DDA0DD,stroke:#8A2BE2,stroke-width:1px
```
