This document describes the normalized database schema for the VGLUG Application Form 2025. The schema is designed for optimal performance, data integrity, and easy querying.

1. Normalization: Data is split into logical tables to avoid redundancy
2. Referential Integrity: Foreign key constraints ensure data consistency
3. Indexing: Strategic indexes for fast queries and reporting
4. Cascading Deletes: Deleting an application removes all related data
5. Type Safety: Proper data types for each field

application (main record)
    ├── basic_info (1:1)
    ├── educational_info (1:1)
    ├── family_info (1:1)
    ├── income_info (1:1)
    └── course_info (1:1)

The central table that holds the application metadata. Anonymous application - no user authentication required.

Composite Indexes:

• (status, created_at) - For filtering by status and date
• (application_number, status) - For lookup with status

Stores personal information from the basic_info section.

Composite Indexes:

• (full_name, email) - For duplicate detection
• (gender, differently_abled) - For demographic analysis

Stores complete educational background.

Composite Indexes:

• (college_name, department) - For college-wise reports
• (degree, year) - For academic year analysis
• (received_scholarship, transport_mode) - For socioeconomic analysis

Stores family and guardian information.

Composite Indexes:

• (family_environment, single_parent_info) - For family structure analysis
• (family_members_count, earning_members_count) - For economic dependency analysis

Stores housing status and geographical information.

Composite Indexes:

• (district, pincode) - For location-based queries
• (house_ownership) - For housing status analysis

Stores training preferences and VGLUG awareness.

Composite Indexes:

• (preferred_course, heard_about_vglug) - For course awareness analysis
• (preferred_course, participated_in_vglug_events) - For course engagement analysis

• 6 tables: 1 main + 5 section tables
• 43 data fields: Matching the form structure
• 35+ indexes: Optimized for common queries
• 5 one-to-one relationships: Clean normalized structure

cd backend
python migrate_to_normalized.py

This will create all 6 tables with proper indexes.

from helpers import save_normalized_application

# Structured data from frontend
structured_data = {
    "basic_info": [...],
    "educational_info": [...],
    "family_info": [...],
    "income_info": [...],
    "course_info": [...]
}

application = save_normalized_application(
    structured_data=structured_data,
    application_number="APP20251216001",
    uuid="abc-123-def-456",
    form_config_id=1
)

from helpers import get_application_with_details

data = get_application_with_details(application_id="abc-123-def-456")
# Returns complete application with all sections

SELECT
    a.application_number,
    b.full_name,
    b.email,
    c.preferred_course
FROM application a
JOIN basic_info b ON a.id = b.application_id
JOIN course_info c ON a.id = c.application_id
WHERE c.preferred_course = 'Web Development'
ORDER BY a.created_at DESC;

SELECT
    i.district,
    COUNT(*) as count
FROM application a
JOIN income_info i ON a.id = i.application_id
GROUP BY i.district
ORDER BY count DESC;

SELECT
    a.application_number,
    b.full_name,
    b.contact,
    c.preferred_course
FROM application a
JOIN basic_info b ON a.id = b.application_id
JOIN course_info c ON a.id = c.application_id
WHERE b.differently_abled = TRUE;

SELECT
    a.application_number,
    b.full_name,
    f.single_parent_info,
    f.earning_members_count
FROM application a
JOIN basic_info b ON a.id = b.application_id
JOIN family_info f ON a.id = f.application_id
WHERE f.single_parent_info IS NOT NULL;

SELECT
    a.application_number,
    b.full_name,
    e.scholarship_details,
    e.college_name
FROM application a
JOIN basic_info b ON a.id = b.application_id
JOIN educational_info e ON a.id = e.application_id
WHERE e.received_scholarship = TRUE;

• Indexed fields enable fast searches
• Composite indexes optimize complex queries
• Smaller table sizes improve JOIN performance

• Foreign key constraints prevent orphaned records
• NOT NULL constraints ensure required data
• CASCADE DELETE maintains referential integrity

• Easy to add new fields to specific sections
• Can add new tables without affecting existing structure
• Optimized for millions of records

• Easy to generate section-specific reports
• Simple JOINs for cross-section analysis
• Efficient aggregation queries

• Clear separation of concerns
• Easy to backup/restore specific sections
• Simple to add validation rules per table

Used for frequent filters and lookups:

• Primary keys (automatic)
• Foreign keys (application_id)
• Search fields (name, email, contact)
• Filter fields (status, gender, district, course)
• Boolean flags (differently_abled, has_laptop, received_scholarship)

Used for common query patterns:

• (status, created_at) - Admin dashboard filtering
• (full_name, email) - Duplicate detection
• (college_name, department) - College reports
• (district, pincode) - Location-based queries
• (preferred_course, heard_about_vglug) - Course analysis

The legacy submission table is retained for backwards compatibility. New submissions should use the normalized tables via the save_normalized_application() helper function.

• models.py - SQLAlchemy models
• helpers.py - Helper functions for saving/retrieving data
• migrate_to_normalized.py - Migration script
• schema_normalized.sql - SQL schema documentation

1. Run migration script to create tables
2. Update backend submit endpoint to use save_normalized_application()
3. Update admin dashboard to query normalized tables
4. Add reporting queries for analytics
5. Create data export functionality

Created: 2025-12-16