How josedoria93/sql_project works
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)