Database Models Reference

This page provides detailed documentation for all database models in the School Clubs Management System.

Overview

The application uses SQLAlchemy ORM with the following models:

• User: Students and administrators
• Club: Student clubs
• Post: Posts, events, and gallery items
• Membership: Club membership records
• Application: Club membership applications
• follows: Association table for following clubs

---

User Model

class User(UserMixin, db.Model):
    __tablename__ = "users"

Represents system users including students and administrators.

Fields

Field	Type	Nullable	Default	Description
user_id	Integer	No	Auto	Primary key
first_name	String(100)	No	-	User's first name
last_name	String(100)	No	-	User's last name
email	String(80)	No	-	Unique email address
password_hash	String(100)	No	-	Hashed password
user_type	String(10)	No	-	'student' or 'admin'
created_at	DateTime	No	now()	Account creation timestamp

Relationships

followed_clubs = db.relationship("Club", secondary=follows, back_populates="followers")
memberships = db.relationship("Membership", back_populates="student", cascade="all, delete-orphan")
applications = db.relationship("Application", back_populates="student", cascade="all, delete-orphan")
created_posts = db.relationship("Post", back_populates="creator", cascade="all, delete-orphan")

Constraints

• email must be unique
• user_type must be 'student' or 'admin' (CHECK constraint)

Methods

get_id()

Required by Flask-Login to return the user identifier.

def get_id(self):
    return str(self.user_id)

Returns: String representation of user_id

Example Usage

# Create new student
new_user = User(
    first_name="John",
    last_name="Doe",
    email="john.doe@example.com",
    password_hash=generate_password_hash("password123"),
    user_type="student"
)
db.session.add(new_user)
db.session.commit()

# Query user by email
user = User.query.filter_by(email="john.doe@example.com").first()

# Check user type
if user.user_type == "admin":
    print("This is an admin user")

# Access relationships
clubs = user.followed_clubs
posts = user.created_posts

---

Club Model

class Club(db.Model):
    __tablename__ = "clubs"

Represents student clubs in the system.

Fields

Field	Type	Nullable	Default	Description
club_id	Integer	No	Auto	Primary key
club_name	String(100)	No	-	Name of the club
description	String	No	-	Club description
categories	String	No	-	Club categories/tags
is_verified	Boolean	No	False	Admin verification status

Relationships

followers = db.relationship("User", secondary=follows, back_populates="followed_clubs")
memberships = db.relationship("Membership", back_populates="club", cascade="all, delete-orphan")
applications = db.relationship("Application", back_populates="club", cascade="all, delete-orphan")
posts = db.relationship("Post", back_populates="club", cascade="all, delete-orphan")

Example Usage

# Create new club
new_club = Club(
    club_name="Chess Club",
    description="A club for chess enthusiasts",
    categories="Games, Strategy",
    is_verified=False
)
db.session.add(new_club)
db.session.commit()

# Query clubs
verified_clubs = Club.query.filter_by(is_verified=True).all()

# Access relationships
members = club.memberships
followers = club.followers
posts = club.posts

# Get president
president_membership = next((m for m in club.memberships if m.is_president), None)
if president_membership:
    president = president_membership.student

---

Post Model

class Post(db.Model):
    __tablename__ = "posts"

Represents posts, events, and gallery items created by clubs.

Fields

Field	Type	Nullable	Default	Description
post_id	Integer	No	Auto	Primary key
title	String	No	-	Post title
content	String	No	-	Post content/body
post_type	String	No	-	'post', 'event', or 'galerie'
is_pinned	Boolean	No	False	Pinned to top flag
file_urls	JSON	Yes	[]	List of file URLs for gallery
post_date	DateTime	No	now()	Creation timestamp
event_date	DateTime	Yes	-	Event date (events only)
location	String(200)	Yes	-	Event location (events only)
creator_id	Integer (FK)	No	-	User who created the post
club_id	Integer (FK)	No	-	Club that owns the post

Relationships

creator = db.relationship("User", back_populates="created_posts")
club = db.relationship("Club", back_populates="posts")

Constraints

• post_type must be 'post', 'event', or 'galerie' (CHECK constraint)
• Event posts should have event_date populated

Example Usage

# Create regular post
post = Post(
    title="Welcome Message",
    content="Welcome to our club!",
    post_type="post",
    is_pinned=True,
    creator_id=current_user.user_id,
    club_id=1
)
db.session.add(post)
db.session.commit()

# Create event
event = Post(
    title="Annual Meeting",
    content="Join us for our annual meeting",
    post_type="event",
    event_date=datetime(2026, 3, 15, 18, 0),
    location="Room 301",
    is_pinned=False,
    creator_id=current_user.user_id,
    club_id=1
)
db.session.add(event)
db.session.commit()

# Query posts
pinned_posts = Post.query.filter_by(club_id=1, is_pinned=True).all()
events = Post.query.filter_by(post_type="event").order_by(Post.event_date.asc()).all()

# Access creator
creator_name = f"{post.creator.first_name} {post.creator.last_name}"

---

Membership Model

class Membership(db.Model):
    __tablename__ = "memberships"

Represents a student's membership in a club, including president designation.

Fields

Field	Type	Nullable	Default	Description
student_id	Integer (FK, PK)	No	-	User ID of the member
club_id	Integer (FK, PK)	No	-	Club ID
joined_at	DateTime	No	now()	Membership start timestamp
is_president	Boolean	No	False	President flag

Composite Primary Key

(student_id, club_id) - A user can only be a member of each club once.

Relationships

student = db.relationship("User", back_populates="memberships")
club = db.relationship("Club", back_populates="memberships")

Methods

__repr__()

def __repr__(self):
    return f"{self.student.first_name} {self.student.last_name} ({self.club.club_name})"

Returns: Human-readable string representation

Example Usage

# Create membership
membership = Membership(
    student_id=5,
    club_id=1,
    is_president=False
)
db.session.add(membership)
db.session.commit()

# Query memberships
club_members = Membership.query.filter_by(club_id=1).all()

# Find president
president_membership = Membership.query.filter_by(
    club_id=1, 
    is_president=True
).first()

# Promote to president (demote old president first)
old_president = Membership.query.filter_by(club_id=1, is_president=True).first()
if old_president:
    old_president.is_president = False

new_president = Membership.query.filter_by(student_id=5, club_id=1).first()
new_president.is_president = True
db.session.commit()

# Check if user is member
is_member = Membership.query.filter_by(
    student_id=current_user.user_id, 
    club_id=1
).first() is not None

---

Application Model

class Application(db.Model):
    __tablename__ = "applications"

Tracks club membership applications.

Fields

Field	Type	Nullable	Default	Description
student_id	Integer (FK, PK)	No	-	Applicant user ID
club_id	Integer (FK, PK)	No	-	Target club ID
applied_at	DateTime	No	now()	Application timestamp
status	String(20)	No	'pending'	Application status

Composite Primary Key

(student_id, club_id) - A user can only have one application per club.

Relationships

student = db.relationship("User", back_populates="applications")
club = db.relationship("Club", back_populates="applications")

Constraints

• status must be 'pending', 'accepted', or 'denied' (CHECK constraint)

Example Usage

# Create application
application = Application(
    student_id=current_user.user_id,
    club_id=1,
    status="pending"
)
db.session.add(application)
db.session.commit()

# Query pending applications for a club
pending = Application.query.filter_by(
    club_id=1, 
    status="pending"
).all()

# Accept application
app = Application.query.filter_by(student_id=5, club_id=1).first()
app.status = "accepted"

# Create corresponding membership
membership = Membership(
    student_id=5,
    club_id=1,
    is_president=False
)
db.session.add(membership)
db.session.commit()

# Check if user has pending application
user_application = Application.query.filter_by(
    student_id=current_user.user_id,
    club_id=1
).first()

if user_application and user_application.status == "pending":
    print("Application pending review")

---

Follows Association Table

follows = db.Table(
    "follows",
    db.Column("student_id", db.Integer, db.ForeignKey("users.user_id"), primary_key=True),
    db.Column("club_id", db.Integer, db.ForeignKey("clubs.club_id"), primary_key=True),
    db.Column("followed_at", db.DateTime(timezone=True), server_default=func.now()),
)

Simple many-to-many association table for users following clubs.

Fields

Field	Type	Nullable	Description
student_id	Integer (FK, PK)	No	User ID
club_id	Integer (FK, PK)	No	Club ID
followed_at	DateTime	No	Follow timestamp

Example Usage

# Follow a club
club = Club.query.get(1)
current_user.followed_clubs.append(club)
db.session.commit()

# Unfollow a club
current_user.followed_clubs.remove(club)
db.session.commit()

# Check if following
is_following = club in current_user.followed_clubs

# Get all followed clubs
followed = current_user.followed_clubs

# Get all followers of a club
followers = club.followers

# Query follows with timestamp
from sqlalchemy import select
stmt = select(follows).where(follows.c.student_id == current_user.user_id)
results = db.session.execute(stmt).all()

---

Database Schema Diagram

erDiagram
    users ||--o{ memberships : "has"
    users ||--o{ applications : "makes"
    users ||--o{ posts : "creates"
    users }o--o{ clubs : "follows"
    clubs ||--o{ memberships : "has"
    clubs ||--o{ applications : "receives"
    clubs ||--o{ posts : "owns"

    users {
        int user_id PK
        varchar first_name
        varchar last_name
        varchar email UK
        varchar password_hash
        varchar user_type
        datetime created_at
    }

    clubs {
        int club_id PK
        varchar club_name
        text description
        varchar categories
        boolean is_verified
    }

    posts {
        int post_id PK
        varchar title
        text content
        varchar post_type
        boolean is_pinned
        json file_urls
        datetime post_date
        datetime event_date
        varchar location
        int creator_id FK
        int club_id FK
    }

    memberships {
        int student_id PK_FK
        int club_id PK_FK
        datetime joined_at
        boolean is_president
    }

    applications {
        int student_id PK_FK
        int club_id PK_FK
        datetime applied_at
        varchar status
    }

    follows {
        int student_id PK_FK
        int club_id PK_FK
        datetime followed_at
    }

---

Common Query Patterns

Get Club with All Related Data

from sqlalchemy.orm import joinedload

club = Club.query.options(
    joinedload(Club.memberships).joinedload(Membership.student),
    joinedload(Club.posts).joinedload(Post.creator),
    joinedload(Club.applications).joinedload(Application.student),
    joinedload(Club.followers)
).get(club_id)

Get User's Clubs Where They Are President

president_memberships = Membership.query.filter_by(
    student_id=current_user.user_id,
    is_president=True
).all()

president_clubs = [m.club for m in president_memberships]

Get All Upcoming Events

from datetime import datetime

upcoming_events = Post.query.filter(
    Post.post_type == "event",
    Post.event_date >= datetime.now()
).order_by(Post.event_date.asc()).all()

Get Posts from Followed Clubs

followed_club_ids = [club.club_id for club in current_user.followed_clubs]

posts = Post.query.filter(
    Post.club_id.in_(followed_club_ids)
).order_by(Post.post_date.desc()).all()

Count Members by Club

from sqlalchemy import func

member_counts = db.session.query(
    Club.club_name,
    func.count(Membership.student_id).label("member_count")
).join(Membership).group_by(Club.club_id).all()

---

Best Practices

Use Relationships

Access related data through relationships rather than manual joins:

# Good
posts = club.posts

# Avoid
posts = Post.query.filter_by(club_id=club.club_id).all()

Cascade Deletes

Models use cascade="all, delete-orphan" on one-to-many relationships. Deleting a user or club will cascade delete related records:

db.session.delete(club)  # Also deletes memberships, applications, and posts
db.session.commit()

Transaction Safety

Always use try-except blocks for database operations:

try:
    db.session.add(new_record)
    db.session.commit()
except Exception as e:
    db.session.rollback()
    flash(f"Database error: {str(e)}", "danger")

Lazy Loading

Relationships are lazy-loaded by default. Use joinedload() or subqueryload() for eager loading to avoid N+1 queries.