Data Model Design: School Activities App

Data Model Design: School Activities App

Overview

This document presents a complete relational data model for the School Activities App. The system allows students to browse and join activities, enables leaders and faculty to manage them, and provides administrators with approval and oversight capabilities.

The design is realistic for a React / Node / MariaDB web application while remaining appropriate for a student software development project.

Core Assumptions

  • Students create accounts and log in
  • Students browse and join activities
  • Leaders or faculty manage activities
  • Administrators approve or reject activities
  • Users receive notifications about updates

Main Entities

  • Users
  • Organizations
  • Organization Members
  • Activities
  • Activity Memberships
  • Events
  • Event RSVPs
  • Approvals
  • Notifications

Relationships Summary

  • A user may belong to many organizations
  • An organization may have many users
  • An organization may create many activities
  • An activity belongs to one organization
  • An activity may have many events
  • A user may join many activities
  • An activity may have many users
  • A user may RSVP to many events
  • An event may have many RSVPs
  • Activities and organizations go through approval workflows

Complete Table Design

1. users

Purpose: Stores all users including students, leaders, faculty, and administrators.

Columns:

  • id – INT, primary key, auto increment
  • first_name – VARCHAR(50), not null
  • last_name – VARCHAR(50), not null
  • email – VARCHAR(100), not null, unique
  • password_hash – VARCHAR(255), not null
  • role – ENUM(‘student’,’leader’,’faculty’,’admin’), not null
  • class_year – INT, nullable
  • major – VARCHAR(100), nullable
  • is_active – BOOLEAN, default true
  • created_at – DATETIME
  • updated_at – DATETIME

2. organizations

Purpose: Stores clubs, teams, and school groups.

Columns:

  • id – INT, primary key, auto increment
  • name – VARCHAR(100), not null, unique
  • description – TEXT
  • category – VARCHAR(50)
  • email_contact – VARCHAR(100)
  • advisor_user_id – INT
  • created_by_user_id – INT, not null
  • status – ENUM(‘pending’,’approved’,’inactive’)
  • created_at – DATETIME
  • updated_at – DATETIME

Foreign Keys:

  • advisor_user_id → users.id
  • created_by_user_id → users.id

3. organization_members

Purpose: Many-to-many relationship between users and organizations.

Columns:

  • id – INT, primary key
  • organization_id – INT
  • user_id – INT
  • membership_role – ENUM(‘member’,’leader’,’advisor’)
  • joined_at – DATETIME
  • status – ENUM(‘active’,’inactive’,’pending’)

Constraints:

  • Unique (organization_id, user_id)

4. activities

Purpose: Core table representing activities students can join.

Columns:

  • id – INT, primary key
  • organization_id – INT
  • title – VARCHAR(150)
  • description – TEXT
  • activity_type – ENUM(‘club’,’meeting’,’volunteer’,’sports’,’arts’,’academic’,’social’,’other’)
  • location – VARCHAR(150)
  • start_date – DATE
  • end_date – DATE
  • meeting_pattern – VARCHAR(100)
  • capacity – INT
  • visibility – ENUM(‘public’,’members_only’,’private’)
  • status – ENUM(‘draft’,’pending’,’approved’,’rejected’,’archived’)
  • created_by_user_id – INT
  • approved_by_user_id – INT
  • approved_at – DATETIME
  • created_at – DATETIME
  • updated_at – DATETIME

5. activity_memberships

Purpose: Tracks which users are part of which activities.

Columns:

  • id – INT, primary key
  • activity_id – INT
  • user_id – INT
  • membership_status – ENUM(‘requested’,’approved’,’waitlisted’,’rejected’,’left’)
  • joined_at – DATETIME
  • notes – TEXT

Constraints:

  • Unique (activity_id, user_id)

6. events

Purpose: Specific scheduled instances within an activity.

Columns:

  • id – INT, primary key
  • activity_id – INT
  • title – VARCHAR(150)
  • description – TEXT
  • event_date – DATE
  • start_time – TIME
  • end_time – TIME
  • location – VARCHAR(150)
  • max_attendees – INT
  • created_by_user_id – INT
  • status – ENUM(‘scheduled’,’cancelled’,’completed’)
  • created_at – DATETIME
  • updated_at – DATETIME

7. event_rsvps

Purpose: Tracks attendance responses for events.

Columns:

  • id – INT, primary key
  • event_id – INT
  • user_id – INT
  • rsvp_status – ENUM(‘yes’,’no’,’maybe’)
  • responded_at – DATETIME

Constraints:

  • Unique (event_id, user_id)

8. approvals

Purpose: Tracks approval workflows.

Columns:

  • id – INT, primary key
  • entity_type – ENUM(‘organization’,’activity’)
  • entity_id – INT
  • submitted_by_user_id – INT
  • reviewed_by_user_id – INT
  • decision – ENUM(‘pending’,’approved’,’rejected’)
  • comments – TEXT
  • submitted_at – DATETIME
  • reviewed_at – DATETIME

9. notifications

Purpose: Stores system notifications for users.

Columns:

  • id – INT, primary key
  • user_id – INT
  • title – VARCHAR(150)
  • message – TEXT
  • notification_type – ENUM(‘approval’,’reminder’,’membership’,’system’)
  • is_read – BOOLEAN
  • created_at – DATETIME

Recommended Relationships

  • Users ↔ Organizations: many-to-many (organization_members)
  • Organizations → Activities: one-to-many
  • Users ↔ Activities: many-to-many (activity_memberships)
  • Activities → Events: one-to-many
  • Users ↔ Events: many-to-many (event_rsvps)
  • Users → Notifications: one-to-many

Suggested Data Types

  • INT for IDs
  • VARCHAR for short text
  • TEXT for long descriptions
  • ENUM for status and roles
  • BOOLEAN for flags
  • DATE, TIME, DATETIME for temporal data

Minimum Viable Product (MVP)

Start with:

  • users
  • organizations
  • activities
  • activity_memberships
  • approvals

Add later:

  • events
  • event_rsvps
  • notifications
  • organization_members

Conclusion

This data model provides a clean, scalable relational structure for the School Activities App. It supports user roles, activity management, event scheduling, participation tracking, and approval workflows, while remaining extensible for future features.

Scroll to Top