🗄️ Diseño de Base de Datos (Modelo Relacional en PostgreSQL)

1. Sistema de Usuarios

2. Gestión Académica (AdminDesk)

3. Control de Asistencia y Rendimiento (ClassTrack)

Código del diagrama

erDiagram
    %% Relationships
    User ||--o{ AttendanceSession : "validates (Teacher)"
    User ||--o{ RetentionAlert : "registers (Admin)"
    User ||--o{ StudentModule : "sells (Seller)"
    Student ||--o{ StudentModule : "contracts"
    Student ||--o{ AttendanceSession : "attends"
    Student ||--o{ RetentionAlert : "receives"
    Module ||--o{ StudentModule : "is_included_in"
    AttendanceSession ||--o{ LessonLog : "contains_lessons"

    %% Entities
    User {
        int us_id PK
        string us_full_name
        string us_email
        string us_password_hash
        string us_role "ADMIN, TEACHER"
    }

    Student {
        int st_id PK
        string st_identification_card
        string st_full_name
        string st_phone_number
        date st_start_date
        boolean st_is_graduated
        string st_contract_status "ACTIVE, FROZEN, INACTIVE"
    }

    Module {
        int mo_id PK
        string mo_name
        string mo_description
    }

    StudentModule {
        int st_mod_id PK
        int st_mod_student_id FK
        int st_mod_module_id FK
        int st_mod_seller_id FK
        string st_mod_status "ACTIVE, CLOSED, LOCKED"
        date st_mod_purchase_date
    }

    AttendanceSession {
        int at_se_id PK
        int at_se_student_id FK
        int at_se_teacher_id FK "Nullable"
        date at_se_session_date
        time at_se_entry_time
        time at_se_exit_time
        int at_se_total_minutes
        string at_se_status "IN_PROGRESS, PENDING_APPROVAL, APPROVED"
    }

    LessonLog {
        int le_lo_id PK
        int le_lo_attendance_session_id FK
        int le_lo_lesson_number
        string le_lo_notes
    }

    RetentionAlert {
        int re_al_id PK
        int re_al_student_id FK
        int re_al_user_id FK
        date re_al_contact_date
        boolean re_al_has_responded
        int re_al_days_absent
        boolean re_al_is_justified
        string re_al_justification_reason "Nullable"
        date re_al_return_deadline "Nullable"
        string re_al_observations "Nullable"
        string re_al_status "PENDING, RESOLVED, CLOSED_FROZEN"
        date re_al_resolution_date "Nullable"
    }

Bootstrapping tasks