FYP Community

Tables (11)

ChatRoom (chatroom)

  • ChatRoomID (chatroom_id) — NOT NULL — Primary key — NOT NULL
  • ChatType (chat_type) — DEFAULT 'personal'
  • UserList (user_list) — NOT NULL

Comment (comments)

  • CommentID (comment_id) — NOT NULL — Primary key — NOT NULL
  • PostID (post_id) — NOT NULL — Foreign key → Post.PostID — NOT NULL
  • UserID (user_id) — NOT NULL — Foreign key → User.UserID — NOT NULL
  • Content (content) — NULL
  • RewardPoints (points) — NOT NULL
  • IsBest (is_best) — NOT NULL

Community (community)

  • CommunityID (community_id) — NOT NULL — Primary key — NOT NULL
  • Name (name) — NOT NULL
  • Description (description) — NULL
  • Permissions (privacy_type) — DEFAULT 'public'
  • DateCreated (created_at) — NOT NULL, DEFAULT CURRENT_TIMESTAMP
  • DateUpdated (updated_at) — NOT NULL, DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
  • CreatedBy (created_by) — NOT NULL
  • GroupID (group_id) — NULL — Foreign key → Group.GroupID — NOT NULL

Community Membership (community_membership)

  • Membership ID (membership_id) — NOT NULL — Primary key — NOT NULL
  • CommunityID (community_id) — NOT NULL — Foreign key → Community.CommunityID — NOT NULL
  • UserID (user_id) — NOT NULL — Foreign key → User.UserID — NOT NULL
  • Role (role) — DEFAULT 'member'

Follows (follows)

  • FriendshipID (friendship_id) — NOT NULL — Primary key — NOT NULL
  • FollowerID (user_id1) — NOT NULL — Foreign key → User.UserID — NOT NULL
  • FollowingID (user_id2) — NOT NULL — Foreign key → User.UserID — NOT NULL
  • DateCreated (created_at) — NOT NULL

Group (groups)

  • GroupID (group_id) — NOT NULL — Primary key — NOT NULL
  • AdminUserID (admin_user_id) — NOT NULL — Foreign key → User.UserID — NOT NULL
  • GroupName (name) — NOT NULL
  • Description (description) — NULL
  • DateCreated (created_at) — NOT NULL

GroupMember (group_members)

  • MemberID (member_id) — NOT NULL — Primary key — NOT NULL
  • GroupID (group_id) — NOT NULL — Foreign key → Group.GroupID — NOT NULL
  • UserID (user_id) — NOT NULL — Foreign key → User.UserID — NOT NULL
  • DateJoined (joined_at) — NOT NULL

Like (likes)

  • LikeID (like_id) — NOT NULL — Primary key — NOT NULL
  • Field
  • UserID (user_id) — NOT NULL — Foreign key → User.UserID — NOT NULL
  • PostID (post_id) — NOT NULL — Foreign key → Post.PostID — NOT NULL
  • CommentID (comment_id) — NOT NULL — Foreign key → Comment.CommentID — NOT NULL
  • DateCreated (created_at) — NOT NULL
  • DateUpdated (updated_at) — NOT NULL

Message (messages)

  • MessageID (message_id) — NOT NULL — Primary key — NOT NULL
  • ChatRoomID (chatroom_id) — NOT NULL — Foreign key → ChatRoom.ChatRoomID — NOT NULL
  • SenderID (user_id1) — NOT NULL — Foreign key → User.UserID — NOT NULL
  • Content (content) — NULL
  • TimeSent (sent_at) — NOT NULL
  • TimeReceived (received_at) — NOT NULL

Post (posts)

  • PostID (post_id) — NOT NULL — Primary key — NOT NULL
  • UserID (user_id) — NOT NULL — Foreign key → User.UserID — NOT NULL
  • CommunityID (community_id) — NOT NULL — Foreign key → Community.CommunityID — NOT NULL
  • DateCreated (created_at) — NOT NULL
  • DateUpdated (updated_at) — NOT NULL
  • Title (title) — NOT NULL
  • Content (content) — NULL
  • MediaURL (media_url) — NULL
  • TotalBiddingPoints (Field) — NOT NULL
  • BestSelected (best_selected) — NOT NULL

User (users)

  • UserID (user_id) — NOT NULL — Primary key — NOT NULL
  • Email (email) — NULL
  • HashPassword (hash_password) — NOT NULL
  • Name (name) — NULL
  • PhoneNumber (phone_number) — NULL
  • About (about) — NULL
  • BirthDate (birth_date) — NULL
  • Gender (gender) — NULL
  • ProfilePic (profile_url) — NULL
  • Point (points) — DEFAULT 100
  • Rank (rank) — NULL
  • DateCreated (created_at) — NULL, DEFAULT CURRENT_TIMESTAMP
  • DateUpdated (updated_at) — NOT NULL, DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
  • MatricNumber (matric_num) — UNIQUE, NULL
  • Year (year) — NULL
  • Field (last_login) — NULL
  • Status (status) — DEFAULT 'active'
  • Followers (followers_count) — DEFAULT 0
  • Followings (followings_count) — DEFAULT 0
  • Interests (Field) — NULL