Data Model Design: School Activities App MVP

Overview

This version strips the design down to the minimum needed for an MVP. The system has only three tables:

  • Users
  • Organizations
  • Activities

Each organization has one designated organization representative, and only that representative can propose or create activities for that organization.

Because each activity has only one event in this simplified version, event information is stored directly in the Activities table rather than in a separate Events table.

Main Entities

  • Users
  • Organizations
  • Activities

Relationships Summary

  • A user can have a role in the system.
  • An organization has one representative user.
  • A representative user may represent more than one organization if the system allows it.
  • An organization can propose many activities.
  • An activity belongs to exactly one organization.
  • An activity can only be created by the representative of its organization.

Complete Table Design

1. Users

Purpose: Stores all system users, including students, organization representatives, and administrators.

Fields:

  • id (PK) – INT, 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’, ‘org_rep’, ‘admin’), not null
  • is_active – BOOLEAN, default true
  • created_at – DATETIME
  • updated_at – DATETIME

Notes:

  • The role field controls permissions in the system.
  • Only users with the appropriate role should be allowed to act as organization representatives.

2. Organizations

Purpose: Stores the organizations that can sponsor activities.

Fields:

  • id (PK) – INT, auto increment
  • name – VARCHAR(100), not null, unique
  • description – TEXT
  • rep_user_id (FK → Users.id), not null
  • created_at – DATETIME
  • updated_at – DATETIME

Notes:

  • rep_user_id identifies the one user who represents the organization in the system.
  • This user is the only one allowed to propose or create activities for that organization.

3. Activities

Purpose: Stores proposed or scheduled activities.

Fields:

  • id (PK) – INT, auto increment
  • organization_id (FK → Organizations.id), not null
  • title – VARCHAR(150), not null
  • description – TEXT
  • event_date – DATE, not null
  • start_time – TIME
  • end_time – TIME
  • location – VARCHAR(150)
  • status – ENUM(‘proposed’, ‘approved’, ‘rejected’, ‘scheduled’, ‘completed’, ‘cancelled’), not null
  • created_by_user_id (FK → Users.id), not null
  • created_at – DATETIME
  • updated_at – DATETIME

Notes:

  • Each activity belongs to one organization.
  • Since this MVP assumes one event per activity, date, time, and location are stored directly in this table.
  • The application should enforce the rule that created_by_user_id must match the representative user for the linked organization.

Business Rules

  • Every organization must have exactly one representative user.
  • Only the representative of an organization can create an activity for that organization.
  • Every activity must belong to one organization.
  • Every activity stores its own event details directly.
  • User permissions are determined by the role field.

Recommended Relationships

  • Users → Organizations: one-to-many
    One user may represent multiple organizations, but each organization has one representative.
  • Organizations → Activities: one-to-many
    One organization may have many activities.
  • Users → Activities: one-to-many
    One user may create many activities, but only when authorized as the representative of the organization.

Minimum Viable Product Rationale

This three-table design is appropriate for an MVP because it:

  • keeps the database small and easy to understand
  • supports user roles and permissions
  • supports organizations and their representatives
  • supports activity proposal and scheduling
  • avoids unnecessary complexity such as separate event and membership tables

It also leaves room for future expansion, such as adding participant signups, approvals, notifications, or separate event records later.

ER Diagram (Text Version)

Entities

Users

  • id (PK)
  • first_name
  • last_name
  • email
  • password_hash
  • role
  • is_active
  • created_at
  • updated_at

Organizations

  • id (PK)
  • name
  • description
  • rep_user_id (FK → Users.id)
  • created_at
  • updated_at

Activities

  • id (PK)
  • organization_id (FK → Organizations.id)
  • title
  • description
  • event_date
  • start_time
  • end_time
  • location
  • status
  • created_by_user_id (FK → Users.id)
  • created_at
  • updated_at

Relationship View

Users to Organizations

  • One user can represent many organizations.
  • Each organization has exactly one representative user.

Organizations to Activities

  • One organization can have many activities.
  • Each activity belongs to exactly one organization.

Users to Activities

  • One user can create many activities.
  • Each activity is created by one user.
  • The creating user must be the representative of the associated organization.

Short ER Narrative

The MVP data model centers on three entities: users, organizations, and activities. Users have roles that determine what they are allowed to do in the system. Each organization is linked to one representative user. Activities are linked to organizations and include their event details directly in the same table. Only the representative of an organization may create activities for that organization.

Scroll to Top