SoR — Schule ohne Rassismus Workshop Assignment System

A full-stack web application that optimally assigns students to workshops for Schule ohne Rassismus (School without Racism) project days using linear programming.

Students submit their top 3 workshop choices, and the system solves the assignment as a constrained optimization problem — maximizing overall satisfaction while respecting capacity limits and grade-level restrictions.

Features

• Optimal Assignment Algorithm — Uses GLPK linear programming solver to maximize student satisfaction across hundreds of students and workshops simultaneously
• Student Portal — Browse available workshops (filtered by grade level), view details, and submit ranked preferences
• Teacher Dashboard — Import student/workshop data via CSV, trigger the assignment algorithm, monitor job status, and export results
• Role-Based Access Control — Separate interfaces and permissions for students and teachers
• Async Processing — Algorithm runs in a background thread with real-time job tracking (status, duration, cost)
• Bulk Data Import/Export — CSV import for students and workshops; CSV export of final assignments with auto-generated credentials

Tech Stack

Layer	Technology
Backend	Python, Flask, Flask-Login, WTForms
Database	MySQL, SQLAlchemy ORM, Alembic (migrations)
Optimization	PyMProg (GLPK), Linear Programming
Frontend	Jinja2, Bootstrap 4, Font Awesome
Data Processing	Pandas, openpyxl

How It Works

The core algorithm models the assignment as a linear program:

• Objective: Maximize total satisfaction — 1st choice scores 100, 2nd scores 50, 3rd scores 25
• Constraints:
  • Each student is assigned to exactly one workshop
  • Workshop enrollment stays within min/max capacity
  • Students can only be assigned to workshops matching their grade level

This guarantees a globally optimal solution, not just a greedy approximation.

Project Structure

├── main.py                  # Application entry point
├── structure.py             # SQLAlchemy models (Person, Workshop, Job)
├── util.py                  # CSV processing, credential generation
├── website/
│   ├── __init__.py          # Flask app factory
│   ├── auth.py              # Authentication routes
│   ├── schueler.py          # Student routes (browse, select preferences)
│   ├── lehrer.py            # Teacher routes (import, algorithm, export)
│   └── templates/           # Jinja2 templates
├── algorithmus/
│   └── algorithmus_glpk.py  # LP-based assignment solver
├── alembic/                 # Database migrations
└── test/                    # Test data generators

Setup

1. Install dependencies

pip install -r requirements.txt

2. Configure the database

Copy config.py.dist to config.py and fill in your MySQL credentials and Flask secret key.

3. Run migrations

alembic upgrade head

4. Start the server

python main.py

The app runs at http://localhost:8080.

Database Migrations

alembic current                              # Check current version
alembic revision --autogenerate -m "message" # Generate migration
alembic upgrade +1                           # Apply next migration
alembic downgrade -1                         # Rollback one step