# scmosoeu/database-design architecture

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

- Interactive diagram: https://gitdiagram.com/scmosoeu/database-design
- Repository: https://github.com/scmosoeu/database-design

## Overview

##### Project Type and Purpose
- This is a Python‐based ETL/data-engineering utility (not a user‐facing web service) that uses Flask + Flask-SQLAlchemy purely to define, normalize, and load data into a relational database in a star/snowflake schema. Its goal is to:
- Read from a source database or files
- Normalize raw data via SQL scripts or ORM models
- Generate a dimensional model (fact & dimension tables) in a target RDBMS

##### File Structure
- Top level:
- app.py – the orchestrator that runs ingestion jobs
- dbconnector.py / utils.py – handle connections to source/target databases
- models.py – ORM class definitions (fact & dimension tables)
- resources/extensions.py – Flask app & SQLAlchemy initialization
- sql_scripts/ – pure-SQL “initial_ingestion.sql” & “delta_ingestion.sql”
- Patterns:
- Layered code: connection layer (dbconnector/utils), ORM/model layer (resources+models), orchestration layer (app.py), SQL‐script layer.
- Reliance on Flask-SQLAlchemy for metadata management despite no HTTP endpoints.

##### README for Insights
- Confirms use of Flask/SQLAlchemy for DDL & DML
- Describes fact table (product_sales) and its foreign key links to dimension tables (products, containers, combinations)
- Highlights two ingestion modes: full (“initial_ingestion.sql”) vs. incremental (“delta_ingestion.sql”)

##### Main Components & Relationships
a. ETL Orchestrator (app.py)
– Loads configuration, picks ingestion mode, calls SQL scripts or ORM sessions.
b. Connection Layer (dbconnector.py, utils.py)
– Establishes connections to source systems and the target RDBMS.
c. ORM Layer (resources/extensions.py + models.py)
– Defines ORM classes, metadata, relationships; creates tables if needed.
d. SQL Script Layer (sql_scripts/)
– Contains pure-SQL routines for initial or delta normalization.
e. Relational Database (external service)
– Stores dimension tables (products, containers, combinations) and the central fact table (product_sales).
f. Documentation/Artifacts (imgs/)

##### Architectural Patterns & Design Principles
- Layered Architecture: separation of concerns between connection, modeling, orchestration, and raw SQL.
- Star/Snowflake Schema: central fact table with FK links to dimensions.
- Dual Ingestion Paths: full vs. incremental.

## Diagram (Mermaid)

```mermaid
flowchart TD
    %% Code Modules
    subgraph "Code Modules"
        ETO["ETL Orchestrator"]:::python
        subgraph "Connection Layer"
            DBCon["dbconnector.py"]:::python
            Utils["utils.py"]:::python
        end
        subgraph "ORM Layer"
            Ext["resources/extensions.py"]:::python
            Models["models.py"]:::python
        end
    end

    %% SQL Scripts
    subgraph "SQL Scripts"
        Init["initial_ingestion.sql"]:::script
        Delta["delta_ingestion.sql"]:::script
    end

    %% Artifacts and Configurations
    Docs["ER Diagram (imgs/)"]:::docs
    Req["requirements.txt"]:::docs
    Readme["README.md"]:::docs

    %% Database Service
    DB[(Relational Database)]:::db

    %% Star Schema Inset
    subgraph "Star Schema" 
        PS["product_sales"]:::tbl
        P["products"]:::dim
        Cn["containers"]:::dim
        Comb["combinations"]:::dim
        PS --> P
        PS --> Cn
        PS --> Comb
    end

    %% Data Flow Arrows
    ETO -->|uses| DBCon
    ETO -->|uses| Utils
    ETO -->|initial load| Init
    ETO -->|delta load| Delta
    ETO -->|ORM operations| Ext
    ETO -->|ORM operations| Models

    DBCon -->|connects to| DB
    Utils -->|connects to| DB
    Init -->|executes on| DB
    Delta -->|executes on| DB
    Ext -->|defines metadata on| DB
    Models -->|defines tables on| DB

    DB -->|hosts schema| PS

    %% Click Events
    click ETO "https://github.com/scmosoeu/database-design/blob/main/app.py"
    click DBCon "https://github.com/scmosoeu/database-design/blob/main/dbconnector.py"
    click Utils "https://github.com/scmosoeu/database-design/blob/main/utils.py"
    click Ext "https://github.com/scmosoeu/database-design/blob/main/resources/extensions.py"
    click Models "https://github.com/scmosoeu/database-design/blob/main/models.py"
    click Init "https://github.com/scmosoeu/database-design/blob/main/sql_scripts/initial_ingestion.sql"
    click Delta "https://github.com/scmosoeu/database-design/blob/main/sql_scripts/delta_ingestion.sql"
    click Docs "https://github.com/scmosoeu/database-design/tree/main/imgs/"
    click Req "https://github.com/scmosoeu/database-design/blob/main/requirements.txt"
    click Readme "https://github.com/scmosoeu/database-design/blob/main/README.md"

    %% Styling
    classDef python fill:#bbdefb,stroke:#333,stroke-width:1px
    classDef script fill:#ffcc80,stroke:#333,stroke-width:1px
    classDef db fill:#c8e6c9,stroke:#333,stroke-width:1px
    classDef docs fill:#eeeeee,stroke:#333,stroke-width:1px
    classDef dim fill:#dcedc8,stroke:#333,stroke-width:1px
    classDef tbl fill:#ffcdd2,stroke:#333,stroke-width:1px
```
