GC Community

Project Overview

Background and Goals

The CG Community aims to provide an interactive platform for students to share learning experiences, seek academic support, and participate in community activities. The purpose of this platform is to facilitate communication among students, address issues like the scarcity of learning resources and information asymmetry, and establish a supportive student community.

Development Tools and Technologies

• Backend: Python Flask framework is used to implement business logic and API endpoints.
• Frontend: HTML, CSS, and JavaScript are used to develop a simple and intuitive user interface that enhances user interaction.
• Database: Azure SQL is selected to ensure stable and efficient data storage.
• Deployment: The system is hosted on Azure Web Services, ensuring high reliability and scalability in the cloud environment.

Key Features

• Discussion Forum: Students can post questions, answer others’ questions, and engage in academic discussions.
• Student Marketplace: A platform for trading second-hand items and services to meet students’ everyday needs.
• Peer Review System: Students can share reviews on courses, professors, and events, helping others make informed decisions.

Achievements

A basic functional demo has been developed and tested on a small scale. Feedback collected during this phase will help further refine and improve the system. GC Community

Database Design Overview

In the development of the Student Forum, the database is a crucial component used to store user information, post content, interaction records, etc. Below is a detailed explanation of the database design, including the primary table structures and their functions.

Database Overview

• Technology Choice: The database is based on Azure SQL, ensuring high stability and performance for data operations.
• Design Goals: Simple, efficient, and scalable, supporting the core features of the forum, including user management, post interactions, and notification systems.

---

Table Structure Design

1. User Table (UserModel)

• Purpose: Stores basic user information to support user authentication and profile management.
• Field Descriptions:
  • id: User ID, primary key, auto-increment.
  • username: User name, maximum length 100, not nullable.
  • password: Encrypted password, maximum length 200, not nullable.
  • email: Email address, unique, not nullable.
  • join_time: Registration time, default to the current time.
  • avatar: User avatar image path, default to a preset avatar.
• Relationships:
  • One-to-many relationship with the Notification table for managing notifications.
  • One-to-many relationship with the QuestionModel and AnswerModel tables, representing posts and answers by users.

2. Email Captcha Table (EmailCaptchaModel)

• Purpose: Stores email verification codes to support user registration and validation processes.
• Field Descriptions:
  • id: Primary key, auto-increment.
  • email: User email address.
  • captcha: Verification code.

3. Post Table (QuestionModel)

• Purpose: Stores information about forum posts, including titles, content, categories, and engagement metrics.
• Field Descriptions:
  • id: Post ID, primary key, auto-increment.
  • title: Post title, Unicode text.
  • content: Post content, Unicode text.
  • category: Post category, maximum length 50.
  • create_time: Creation time, default to the current time.
  • latest_answer_time: Latest answer time.
  • like_count, favorite_count: Like and favorite counts, default to 0.
• Relationships:
  • One-to-many relationship with the UserModel table, identifying the author of the post.
  • One-to-many relationship with the AnswerModel table, representing the answers to the post.
  • One-to-many relationships with the Like and Favorite tables, for like and favorite operations.

4. Answer Table (AnswerModel)

• Purpose: Stores answers to forum posts.
• Field Descriptions:
  • id: Answer ID, primary key, auto-increment.
  • content: Answer content, Unicode text.
  • create_time: Answer creation time, default to the current time.
  • like_count: Like count, default to 0.
• Relationships:
  • One-to-many relationship with the QuestionModel table, linking the answer to its post.
  • One-to-many relationship with the UserModel table, linking the answer to its author.
  • One-to-many relationship with the Like table, for managing likes on answers.

5. Like Table (Like)

• Purpose: Manages the likes that users give to posts or answers.
• Field Descriptions:
  • id: Like record ID, primary key.
  • user_id: User ID, foreign key, identifying the user who liked the post or answer.
  • question_id and answer_id: Foreign keys identifying the liked post or answer.

6. Notification Table (Notification)

• Purpose: Stores notifications for users to alert them of important events.
• Field Descriptions:
  • id: Notification ID, primary key.
  • user_id: Foreign key, identifying the user who receives the notification.
  • message: Notification message.
  • timestamp: Timestamp of when the notification was created, default to the current time.
  • is_read: Boolean flag to track whether the notification has been read, default to false.
  • url: URL related to the notification.
• Relationships:
  • Many-to-one relationship with the UserModel table.

7. Favorite Table (Favorite)

• Purpose: Manages users’ favorite posts.
• Field Descriptions:
  • id: Favorite record ID, primary key.
  • user_id: User ID, foreign key, identifying the user who favorited the post.
  • question_id: Post ID, foreign key, identifying the favorited post.

---

Database Optimization and Scalability

• Indexing: Indexes are applied on frequently queried fields (e.g., email, create_time) to improve query performance.
• Data Integrity: Foreign key constraints ensure data consistency, such as automatically deleting posts and answers when the user is deleted.
• Scalability: The database structure is designed to easily accommodate future features like voting systems or private messaging.

---

API Definitions

Authentication API

1. User Registration

• Endpoint: POST /api/register
• Description: Registers a new user.
• Request Body:

  {
    "username": "string",
    "email": "string",
    "password": "string"
  }
  

• Response:

  {
    "message": "Registration successful."
  }
  

2. User Login

• Endpoint: POST /api/login
• Description: Authenticates a user and returns a token.
• Request Body:

  {
    "email": "string",
    "password": "string"
  }
  

• Response:

  {
    "token": "jwt_token",
    "message": "Login successful."
  }
  

---

Forum API

1. Create a Post

• Endpoint: POST /api/posts
• Description: Creates a new forum post.
• Request Body:

  {
    "title": "string",
    "content": "string",
    "category": "string"
  }
  

• Response:

  {
    "post_id": "integer",
    "message": "Post created successfully."
  }
  

2. Answer a Post

• Endpoint: POST /api/posts/{post_id}/answers
• Description: Adds an answer to a specific post.
• Request Body:

  {
    "content": "string"
  }
  

• Response:

  {
    "answer_id": "integer",
    "message": "Answer added successfully."
  }
  

3. Like a Post

• Endpoint: POST /api/posts/{post_id}/like
• Description: Adds a like to a specific post.
• Response:

  {
    "message": "Post liked successfully."
  }
  

---

Notification API

1. Get Notifications

• Endpoint: GET /api/notifications
• Description: Retrieves a list of notifications for the authenticated user.
• Response:

  [
    {
      "id": "integer",
      "message": "string",
      "timestamp": "datetime",
      "is_read": "boolean",
      "url": "string"
    }
  ]
  

2. Mark Notification as Read

• Endpoint: PATCH /api/notifications/{id}
• Description: Marks a specific notification as read.
• Response:

  {
    "message": "Notification marked as read."
  }
  

---

User Profile API

1. Get User Profile

• Endpoint: GET /api/users/{user_id}
• Description: Retrieves profile details of a specific user.
• Response:

  {
    "id": "integer",
    "username": "string",
    "email": "string",
    "avatar": "string",
    "join_time": "datetime"
  }
  

2. Update User Profile

• Endpoint: PUT /api/users/{user_id}
• Description: Updates the profile details of a specific user.
• Request Body:

  {
    "username": "string",
    "avatar": "string"
  }
  

• Response:

  {
    "message": "Profile updated successfully."
  }