erDiagram
%% Core User Management
user {
string id PK
string email UK
string image
string name
boolean emailVerified
datetime createdAt
datetime updatedAt
}
UserData {
string id PK
int avatar
string referalCode
boolean isBanned
boolean isStaff
datetime createdAt
datetime updatedAt
string userId UK,FK
string lineId
string discordId
string phoneNumber
string jobInstitution
datetime birthDate
string lastEducation
string domicile
int currentSemester
}
account {
string id PK
string scope
string password
string accessToken
datetime accessTokenExpiresAt
string accountId
datetime createdAt
string idToken
string providerId
string refreshToken
datetime refreshTokenExpiresAt
datetime updatedAt
string userId FK
}
session {
string id PK
string token UK
datetime createdAt
datetime expiresAt
string ipAddress
datetime updatedAt
string userAgent
string userId FK
}
verification {
string id PK
string identifier
string value
datetime createdAt
datetime expiresAt
datetime updatedAt
}
rateLimit {
string id PK
string key
int count
bigint lastRequest
}
%% Referral System
ReferalCodeUsage {
string id PK
string provideById FK
string usedById FK
boolean isValidated
datetime createdAt
datetime updatedAt
}
%% Events System
Event {
string id PK
string name UK
string description
enum eventType UK
datetime createdAt
datetime updatedAt
}
EventDate {
string id PK
string name
string description
string eventId FK
datetime startDate
datetime endDate
datetime createdAt
datetime updatedAt
}
EventRegistration {
string id PK
string eventId FK
string userId FK
datetime createdAt
datetime updatedAt
}
GrandLaunchingRegistration {
string id PK
string eventRegistrationId UK,FK
enum knowEventSource
datetime createdAt
datetime updatedAt
}
XcelerateRegistration {
string id PK
string eventRegistrationId UK,FK
string jobCategory
enum knowEventSource
datetime createdAt
datetime updatedAt
}
XcelerateWorkshopTask {
string id PK
string title
string description
string imageUrl
datetime deadline
datetime createdAt
datetime updatedAt
}
XcelerateWorkshopTaskSubmission {
string id PK
string taskId FK
string userId FK
string fileUrl
enum status
string feedback
datetime createdAt
datetime updatedAt
}
%% Quiz System
MiniQuiz {
string id PK
string quizName
datetime createdAt
datetime updatedAt
}
MiniQuizAttempt {
string id PK
string quizId FK
string userId FK
json answerJson
datetime attemptedAt
datetime createdAt
datetime updatedAt
}
MiniQuizQuestion {
string id PK
string question
boolean isTrue
string quizId FK
datetime createdAt
datetime updatedAt
}
%% Feedback System
Feedback {
string id PK
string eventId UK,FK
boolean isOpen
datetime createdAt
datetime updatedAt
}
FeedbackQuestion {
string id PK
string question
enum questionType
string feedbackId FK
datetime createdAt
datetime updatedAt
}
FeedbackOption {
string id PK
string questionId FK
string option
datetime createdAt
datetime updatedAt
}
FeedbackSubmission {
string id PK
string feedbackId FK
string userId FK
json answerJson
datetime createdAt
datetime updatedAt
}
%% Playground & Gaming System
UserPlayground {
string id PK
string userId UK,FK
string username
int virtualPoint
datetime createdAt
datetime updatedAt
}
UserPlaygroundDetention {
string id PK
string userPlaygroundId UK,FK
enum state
string description
int penaltyPoint
boolean isRead
datetime createdAt
datetime updatedAt
}
Game {
string id PK
string name
string description
string rules
string arcadeCode UK
string arcadeUrl
boolean isGetPoint
enum orientation
datetime createdAt
datetime updatedAt
}
GameRule {
string id PK
string gameId FK
string title
string rule
datetime createdAt
datetime updatedAt
}
GameDeveloper {
string id PK
string name
string description
string imageUrl
string instagramUrl
string linkedInUrl
string gameId FK
datetime createdAt
datetime updatedAt
}
UserPlayGameHistory {
string id PK
string userPlaygroundId FK
string gameId FK
string tokenUsedId FK
boolean isFinished
datetime finishedAt
datetime createdAt
datetime updatedAt
}
PlaygroundToken {
string id PK
string code UK
string userPlaygroundId FK
boolean isUsed
datetime usedAt
enum tokenType
datetime expiredAt
datetime createdAt
datetime updatedAt
}
Advertisement {
string id PK
string contentUrl
boolean isVideo
int contentLength
datetime createdAt
datetime updatedAt
}
AdWatchSession {
string id PK
string userPlaygroundId FK
string adId FK
int adLength
int startTimeUnix
int lockUntil
datetime createdAt
datetime updatedAt
}
AdWatchHistory {
string id PK
string userPlaygroundId FK
string adId FK
datetime watchedAt
datetime createdAt
datetime updatedAt
}
PlaygroundRewardHistory {
string id PK
string userPlaygroundId FK
string description
enum rewardType
int amount
datetime createdAt
datetime updatedAt
}
PlaygroundExpenseHistory {
string id PK
string userPlaygroundId FK
string description
int amount
datetime createdAt
datetime updatedAt
}
%% CV Clinic System
CVClinicVouchers {
string code PK
}
CVClinicClaim {
string id PK
string userId UK,FK
string usedVoucherCode FK
datetime createdAt
datetime updatedAt
}
%% Content Management
ExploreITFunfact {
string id PK
string title
string imageUrl
string content
datetime createdAt
datetime updatedAt
}
Article {
string id PK
string title
string imageUrl
string description
string url
datetime createdAt
datetime updatedAt
}
LeaderBoardHistory {
string id PK
json jsonData
datetime createdAt
datetime updatedAt
}
Exhibitor {
string id PK
string companyName
string name
string email
string phoneNumber
datetime createdAt
datetime updatedAt
}
%% Notification System
DashboardNotification {
string id PK
enum type
string title
string content
boolean isPrivate
datetime createdAt
datetime updatedAt
}
DashboardNotificationUserRead {
string id PK
boolean isOpened
string userId FK
string notificationId FK
datetime createdAt
datetime updatedAt
}
%% Academy & Competition System
Program {
string id PK
enum code UK
enum type
int minAge
int maxAge
array allowedEducationLevel
int minMember
int maxMember
boolean isAbleToRegisterAsTeam
string name
string logoUrl
int paymentBills
datetime startDate
datetime endDate
datetime extendStartDate
string guideBookUrl
string assignmentUrl
string coordinationGroupUrl
string about
array timeline
array testimony
array FAQ
array contactPerson
json content
datetime createdAt
datetime updatedAt
}
ProgramRegistration {
string id PK
string programId FK
string userId FK
datetime createdAt
datetime updatedAt
}
SingleParticipant {
string id PK
string userId FK
string programId FK
datetime createdAt
datetime updatedAt
}
Team {
string id PK
string code UK
enum status
string notes
string name
string programId FK
datetime createdAt
datetime updatedAt
}
Member {
string id PK
boolean isLeader
string userId FK
string teamId FK
datetime createdAt
datetime updatedAt
}
ProgramTask {
string id PK
string title
datetime deadline
string description
array acceptedFileTypes
string guideBookUrl
enum type
datetime createdAt
datetime updatedAt
}
ProgramTaskExtraDescription {
string id PK
string taskId FK
enum forProgram
string description
datetime createdAt
datetime updatedAt
}
ProgramTaskSubmission {
string id PK
string fileUrl
string fileName
enum status
string feedback
string taskId FK
enum submissionForProgram
string teamId FK
string userId FK
datetime createdAt
datetime updatedAt
}
Announcement {
string id PK
string title
string description
string programId FK
datetime createdAt
datetime updatedAt
}
%% Voting System
Nominee {
string id PK
enum type
string name
string developer
string description
array thumbnailUrl
array url
boolean isActive
int point
datetime createdAt
datetime updatedAt
}
NomineeVote {
string id PK
enum type
string firstChoiceNomineeId FK
string secondChoiceNomineeId FK
string thirdChoiceNomineeId FK
string userId FK
datetime createdAt
datetime updatedAt
}
Project {
string id PK
string name
string type
string imageUrl
string description
string youtubeUrl
string teamName
int votes
datetime createdAt
datetime updatedAt
}
%% Company Exhibition System
CompanyExhibition {
string id PK
string name
string location
string iconUrl
string description
array tags
array benefits
string vision
array mission
datetime createdAt
datetime updatedAt
}
CompanyExhibitionVacancies {
string id PK
string companyExhibitionId FK
string position
array tags
string description
array benefits
enum jobType
string salary
datetime deadline
array responsibilities
array requirements
string vacancyUrl
datetime createdAt
datetime updatedAt
}
%% E-commerce System
SupermarketItem {
string id PK
string name
string description
string imageUrl
int price
int stock
array category
datetime createdAt
datetime updatedAt
}
SupermarketUserCart {
string id PK
string userId UK,FK
int totalPrice
enum status
datetime createdAt
datetime updatedAt
}
SupermarketUserCartItem {
string id PK
string cartId FK
string itemId FK
int quantity
datetime createdAt
datetime updatedAt
}
%% Walk-in Interview System
WalkInInterviewCompany {
string id PK
string name
string description
string iconUrl
int slots
string session
array roles
array tags
array benefits
array criteria
int currentApplicants
datetime createdAt
datetime updatedAt
}
WalkInInterviewApplicant {
string id PK
string userId FK
string walkInInterviewCompany FK
string resumeUrl
enum status
string referralCode
datetime appliedAt
datetime updatedAt
}
%% Relationships
user ||--o{ UserData : "has profile"
user ||--o{ account : "has accounts"
user ||--o{ session : "has sessions"
user ||--|| UserPlayground : "has playground"
user ||--o{ EventRegistration : "registers for"
user ||--o{ ProgramRegistration : "registers for"
user ||--o{ SingleParticipant : "participates as"
user ||--o{ Member : "member of"
user ||--o{ NomineeVote : "votes"
user ||--o{ FeedbackSubmission : "submits"
user ||--o{ MiniQuizAttempt : "attempts"
user ||--o{ DashboardNotificationUserRead : "reads"
user ||--o{ CVClinicClaim : "claims"
user ||--o{ XcelerateWorkshopTaskSubmission : "submits"
user ||--o{ ProgramTaskSubmission : "submits"
user ||--|| SupermarketUserCart : "has cart"
user ||--o{ WalkInInterviewApplicant : "applies to"
user ||--o{ DashboardNotification : "creates private"
UserData ||--o{ ReferalCodeUsage : "provides"
UserData ||--o{ ReferalCodeUsage : "uses"
Event ||--o{ EventDate : "has dates"
Event ||--o{ EventRegistration : "has registrations"
Event ||--|| Feedback : "has feedback"
EventRegistration ||--o| GrandLaunchingRegistration : "extends to"
EventRegistration ||--o| XcelerateRegistration : "extends to"
XcelerateWorkshopTask ||--o{ XcelerateWorkshopTaskSubmission : "has submissions"
MiniQuiz ||--o{ MiniQuizAttempt : "has attempts"
MiniQuiz ||--o{ MiniQuizQuestion : "has questions"
Feedback ||--o{ FeedbackQuestion : "has questions"
Feedback ||--o{ FeedbackSubmission : "has submissions"
FeedbackQuestion ||--o{ FeedbackOption : "has options"
UserPlayground ||--o| UserPlaygroundDetention : "may have detention"
UserPlayground ||--o{ UserPlayGameHistory : "plays games"
UserPlayground ||--o{ PlaygroundToken : "has tokens"
UserPlayground ||--o{ AdWatchSession : "watches ads"
UserPlayground ||--o{ AdWatchHistory : "has ad history"
UserPlayground ||--o{ PlaygroundRewardHistory : "has rewards"
UserPlayground ||--o{ PlaygroundExpenseHistory : "has expenses"
Game ||--o{ GameRule : "has rules"
Game ||--o{ GameDeveloper : "developed by"
Game ||--o{ UserPlayGameHistory : "played in"
PlaygroundToken ||--o{ UserPlayGameHistory : "used in"
Advertisement ||--o{ AdWatchSession : "shown in"
Advertisement ||--o{ AdWatchHistory : "watched in"
CVClinicVouchers ||--o{ CVClinicClaim : "claimed by"
DashboardNotification ||--o{ DashboardNotificationUserRead : "read by"
Program ||--o{ ProgramRegistration : "has registrations"
Program ||--o{ SingleParticipant : "has participants"
Program ||--o{ Team : "has teams"
Program ||--o{ Announcement : "has announcements"
Program ||--o{ ProgramTask : "has tasks"
Team ||--o{ Member : "has members"
Team ||--o{ ProgramTaskSubmission : "submits tasks"
ProgramTask ||--o{ ProgramTaskExtraDescription : "has extra descriptions"
ProgramTask ||--o{ ProgramTaskSubmission : "has submissions"
Nominee ||--o{ NomineeVote : "receives first choice votes"
Nominee ||--o{ NomineeVote : "receives second choice votes"
Nominee ||--o{ NomineeVote : "receives third choice votes"
CompanyExhibition ||--o{ CompanyExhibitionVacancies : "has vacancies"
SupermarketUserCart ||--o{ SupermarketUserCartItem : "contains items"
SupermarketItem ||--o{ SupermarketUserCartItem : "in carts"
WalkInInterviewCompany ||--o{ WalkInInterviewApplicant : "receives applications"
This database schema supports a comprehensive event management platform for COMPFEST 17, featuring:
- User Management - Authentication, profiles, and user data
- Event System - Event management, registrations, and specialized event types
- Academy & Competition - Program management, teams, tasks, and submissions
- Playground & Gaming - Interactive games, tokens, rewards, and detention system
- Feedback System - Event feedback collection and management
- Voting System - Nominee voting for competitions
- E-commerce - Supermarket items and shopping cart functionality
- Company Exhibition - Company profiles and job vacancies
- Walk-in Interview - Interview application management
- Content Management - Articles, announcements, and notifications
- Multi-tenancy: Supports different program types (Academy/Competition)
- Flexible Event System: Handles various event types with specific registration flows
- Gamification: Virtual points, tokens, ads, and rewards system
- Team Management: Support for both individual and team participation
- Task Management: File submissions with status tracking
- Referral System: User referral code tracking and validation
- Notification System: Dashboard notifications with read status tracking
The schema uses PostgreSQL with UUID primary keys and includes proper indexing through unique constraints and foreign key relationships.