docs(contribution-guidelines): add database design diagrams

[HasithDeAlwis]

What feature should we improve? Please describe. We need to make sure that all database design documents are visible to all contributors so they can see exactly how our database works on a higher level.

Describe the solution you'd like

1. Provide images and a verbal description of the database design.
2. Give an overview of the tools used (pros and cons)
3. Explain how any contributor could edit and collaborate with the main team on database design

Additional Context See Figjam for Infrastructure Design for an idea on how our initial ideas were made.

[JeremyFriesenGitHub]

Here is what I think we had so far (this is done using mermaid):

erDiagram
    User {
        int id "Primary key, unique"
        varchar first_name "First name of the user"
        varchar middle_name "Middle name of the user, optional"
        varchar last_name "Last name of the user"
        varchar preferred_name "Preferred name of the user, optional"
        varchar email "Email of the user, must be unique, verification required"
        date date_of_birth "Date of birth of the user, verification required"
        enum gender "Gender of the user, e.g., male, female, etc."
        enum phone_number_country_code "Country code of phone number, e.g., +1"
        varchar phone_number "Phone number of the user, must be unique, verification required"
        int hackathons_attended "Number of hackathons attended by the user"
        longtext any_other_comments "Any additional comments from the user"
        bool international_or_domestic "Whether the user is international or domestic, optional"
        enum ethnicity "Ethnicity of the user, optional"
        int estimated_grad_year "Estimated graduation year of the user, optional"
        enum mlh_reqs "MLH requirements, default is 'No'"
        int resume_id "References Resume.id, mandatory relationship"
        int school_id "References School.id, mandatory relationship"
        int emergency_contact_id "References EmergencyContact.id, mandatory relationship"
        int team_id "References Team.id, mandatory relationship"
    }

    EmergencyContact {
        int id "Primary key, unique"
        varchar name "Name of the emergency contact"
        enum relationship "Relationship to the user, e.g., mother, father, etc."
        varchar phone_number "Phone number of the contact, must be unique, verification required"
        bool is_verified "Whether the phone number is verified, default is 'no'"
    }

    Team {
        int id "Primary key, unique"
        varchar name "Name of the team"
        varchar project_link_id "URL to the project, verification required"
        int team_admin "References User.id, one-to-one relationship"
        bool has_submitted "Whether the team has submitted a project, default is 'No'"
    }

    School {
        int id "Primary key, unique"
        varchar name "Name of the school, verification required"
        enum level_of_study "Level of study, e.g., graduate school, high school, etc."
    }

    Program {
        int id "Primary key, unique"
        varchar name "Name of the program"
        int school_id "References School.id"
        enum program_type "Type of program, e.g., bachelor, master, diploma, certificate, etc."
    }

    Resume {
        int id "Primary key, unique"
        varchar file_link "URL to the resume file, verification required"
        bool privacy_toggle "Privacy setting for the resume, default is false"
        timestamp uploaded_at "Timestamp when the resume was uploaded, default is CURRENT_TIMESTAMP"
    }

    SocialMedia {
        int id "Primary key, unique"
        int user_id "References User.id, optional relationship"
        varchar platform_name "Name of the social media platform, e.g., LinkedIn, GitHub, etc."
        varchar profile_url "URL to the social media profile, verification required"
    }

    UserPreferences {
        int id "Primary key, unique"
        int user_id "References User.id, mandatory relationship"
        bool dark_mode "Whether the user prefers dark mode, default is false"
        varchar language "Preferred language of the user, default is 'English'"
        enum event_preferences "Event preferences, e.g., hardware, software, etc."
        enum privacy_mode "Privacy mode, to be defined"
        bool is_subscribed "Whether the user is subscribed, default is 'No'"
        enum diet_restrictions "Dietary restrictions, e.g., allergies, vegan, none, etc."
        enum track_preferences "Track preferences, e.g., hardware, software, etc., optional"
        enum interests "Interests of the user, e.g., languages, etc., optional"
        enum shirt_size "Shirt size of the user"
        enum disabilities "Disabilities, if any"
        enum applicable_skills "Applicable skills of the user"
        enum pronouns "Preferred pronouns of the user, e.g., he/him, she/her, they/them"
    }

    %% Relationships
    User ||--o{ Resume : "References"
    User ||--o{ School : "References"
    User ||--o{ EmergencyContact : "References"
    User ||--o{ Team : "References"
    Team ||--|| User : "References"
    Program ||--o{ School : "References"
    SocialMedia ||--o{ User : "References"
    UserPreferences ||--o{ User : "References"

    %% Additional Notes
    %% New table for application questions should reference User
    %% Make rich text fields
    %% Example of a potential Events table
    %% Table Events {
    %%   int id "Primary key, unique"
    %%   varchar name "Name of the event"
    %% }
    %% Additional features to consider: team invites, grad year, job interest auto-fill, hackathon source, skills, agreements, personal website, Devpost link, travel accommodations, browser extension, user roles, dashboard, audit logs, CMS

Here is the code used for this diagram:

erDiagram
    User {
        int id "Primary key, unique"
        varchar first_name "First name of the user"
        varchar middle_name "Middle name of the user, optional"
        varchar last_name "Last name of the user"
        varchar preferred_name "Preferred name of the user, optional"
        varchar email "Email of the user, must be unique, verification required"
        date date_of_birth "Date of birth of the user, verification required"
        enum gender "Gender of the user, e.g., male, female, etc."
        enum phone_number_country_code "Country code of phone number, e.g., +1"
        varchar phone_number "Phone number of the user, must be unique, verification required"
        int hackathons_attended "Number of hackathons attended by the user"
        longtext any_other_comments "Any additional comments from the user"
        bool international_or_domestic "Whether the user is international or domestic, optional"
        enum ethnicity "Ethnicity of the user, optional"
        int estimated_grad_year "Estimated graduation year of the user, optional"
        enum mlh_reqs "MLH requirements, default is 'No'"
        int resume_id "References Resume.id, mandatory relationship"
        int school_id "References School.id, mandatory relationship"
        int emergency_contact_id "References EmergencyContact.id, mandatory relationship"
        int team_id "References Team.id, mandatory relationship"
    }

    EmergencyContact {
        int id "Primary key, unique"
        varchar name "Name of the emergency contact"
        enum relationship "Relationship to the user, e.g., mother, father, etc."
        varchar phone_number "Phone number of the contact, must be unique, verification required"
        bool is_verified "Whether the phone number is verified, default is 'no'"
    }

    Team {
        int id "Primary key, unique"
        varchar name "Name of the team"
        varchar project_link_id "URL to the project, verification required"
        int team_admin "References User.id, one-to-one relationship"
        bool has_submitted "Whether the team has submitted a project, default is 'No'"
    }

    School {
        int id "Primary key, unique"
        varchar name "Name of the school, verification required"
        enum level_of_study "Level of study, e.g., graduate school, high school, etc."
    }

    Program {
        int id "Primary key, unique"
        varchar name "Name of the program"
        int school_id "References School.id"
        enum program_type "Type of program, e.g., bachelor, master, diploma, certificate, etc."
    }

    Resume {
        int id "Primary key, unique"
        varchar file_link "URL to the resume file, verification required"
        bool privacy_toggle "Privacy setting for the resume, default is false"
        timestamp uploaded_at "Timestamp when the resume was uploaded, default is CURRENT_TIMESTAMP"
    }

    SocialMedia {
        int id "Primary key, unique"
        int user_id "References User.id, optional relationship"
        varchar platform_name "Name of the social media platform, e.g., LinkedIn, GitHub, etc."
        varchar profile_url "URL to the social media profile, verification required"
    }

    UserPreferences {
        int id "Primary key, unique"
        int user_id "References User.id, mandatory relationship"
        bool dark_mode "Whether the user prefers dark mode, default is false"
        varchar language "Preferred language of the user, default is 'English'"
        enum event_preferences "Event preferences, e.g., hardware, software, etc."
        enum privacy_mode "Privacy mode, to be defined"
        bool is_subscribed "Whether the user is subscribed, default is 'No'"
        enum diet_restrictions "Dietary restrictions, e.g., allergies, vegan, none, etc."
        enum track_preferences "Track preferences, e.g., hardware, software, etc., optional"
        enum interests "Interests of the user, e.g., languages, etc., optional"
        enum shirt_size "Shirt size of the user"
        enum disabilities "Disabilities, if any"
        enum applicable_skills "Applicable skills of the user"
        enum pronouns "Preferred pronouns of the user, e.g., he/him, she/her, they/them"
    }

    %% Relationships
    User ||--o{ Resume : "References"
    User ||--o{ School : "References"
    User ||--o{ EmergencyContact : "References"
    User ||--o{ Team : "References"
    Team ||--|| User : "References"
    Program ||--o{ School : "References"
    SocialMedia ||--o{ User : "References"
    UserPreferences ||--o{ User : "References"

    %% Additional Notes
    %% New table for application questions should reference User
    %% Make rich text fields
    %% Example of a potential Events table
    %% Table Events {
    %%   int id "Primary key, unique"
    %%   varchar name "Name of the event"
    %% }
    %% Additional features to consider: team invites, grad year, job interest auto-fill, hackathon source, skills, agreements, personal website, Devpost link, travel accommodations, browser extension, user roles, dashboard, audit logs, CMS

[JeremyFriesenGitHub]

This was the original dbml that we had (used in dbdiagram.io):

// Use DBML to define your database structure
// Docs: https://dbml.dbdiagram.io/docs

Table User {
  id integer [pk, unique]
  first_name varchar
  middle_name varchar //optional
  last_name varchar
  preferred_name varchar // optional 
  email varchar unique [note: 'verification required']
  date_of_birth date [note: 'verification require ']
  gender enum [note: 'male, female, etc.']
  phone_number_country_code enum [note: '+1']
  phone_number varchar unique [note: 'verification required']
  hackathons_attended integer
  any_other_comments longtext
  international_or_domestic bool // optional --
  ethnicity enum // optional, null 
  estimated_grad_year integer  // optional, null
  mlh_reqs enum [default: 'No'] 
  resume_id integer [ref: > Resume.id] //mandatory relationship
  school_id integer [ref: > School.id] //mandatory relationship
  emergency_contact_id integer [ref: > EmergencyContact.id] //mandatory relationship
  team_id integer [ref: > Team.id] //mandatory relationship
}

Table EmergencyContact {
  id integer [pk, unique]
  name varchar
  relationship enum [note: 'mother, father, etc.']
  phone_number varchar unique [note: 'verification required']
  is_verified bool [default: 'no']
}

Table Team {
  id integer [pk, unique]
  name varchar
  project_link_id varchar [note: 'URL to Project, verification required']
  team_admin integer [ref: <> User.id] // one-to-one
  has_submitted bool [default: 'No']
}

Table School {
  id integer [pk, unique]
  name varchar [note: 'verification required']
  level_of_study enum [note: 'graduate school, high school, etc.']  //tbd
}

Table Program {
  id integer [pk, unique]
  name varchar
  school_id integer [ref: > School.id]
  program_type enum [note: 'bachelor, master, diploma, certificate, etc.']
}

Table Resume {
  id integer [pk, unique]
  file_link varchar [note: 'URL to resume file, verification required']
  privacy_toggle boolean [default: false]
  uploaded_at timestamp [default: 'CURRENT_TIMESTAMP']
}

Table SocialMedia {
  id integer [pk, unique]
  user_id integer [ref: > User.id] // optional relationship
  platform_name varchar [note: 'LinkedIn, GitHub, etc.', null]
  profile_url varchar [note: 'URL to the social media profile, verification required', null]
}

Table UserPreferences {
  id integer [pk, unique]
  user_id integer [ref: > User.id]  // mandatory relationship
  dark_mode boolean [default: 'false']  
  language varchar [default: 'English']  
  event_preferences enum [note: 'hardware, software, etc.']
  privacy_mode enum [note: ''] // tbd 
  is_subscribed bool [default: 'No']
  diet_restrictions enum [note: 'allergies, vegan, none etc.'] // make fk
  track_preferences enum [null, note: 'hardware, software, etc.'] // make fk (multiple interests)
  interests enum [null, note: 'langs, etc.'] // make fk
  shir_size enum
  disabilities enum
  applicable_skills enum
  pronouns enum
}

// new table application questions should reference user

//make rich text 

// Table Events { cu events during yr
//   id integer pk
//   name varchar
// }// coming from the cms 

/// team invites

///grad year

/// simplify auto-fill (job interest)

//how did u hear abt this hackathon

// skills and accomplishments

// mlh agreements, our own agreements, sponsor aggrements

// personal website

//big questions later

//devpost profile link

//travel accomodiations

//a browser extension to apply to other hackathons

//different user groups/roles

//dashboard

// audit logs endpoints

///cms 

Here is the result :

[JeremyFriesenGitHub]

I'll be looking more into more tools (that feature collaboration) and into the database design.

[MFarabi619]

@JeremyFriesenGitHub Can you a draft PR with this db design on the docs site and ask @sourcery-ai to review it?