Introduction 📝

In this tutorial, we will walk you through the process of creating a robust hackathon database using Outerbase and Supabase. This database will allow you to efficiently manage hackathon details, challenges, teams, submissions, judges, and judging criteria. 🏆🔍

Prerequisites 🧰✅

Before we begin, make sure you have the following:

1. An account on Supabase: Sign up here. 🌐🔗

2. A project set up on Supabase with credentials generated. 🗂️🔑

Getting Started 🏁🚀

1. Connecting to Supabase 🌐🔗

1. Create a project on Supabase, select your region, and generate a password. 🌍🔐

2. Go to project settings and then database to find and copy all the necessary connection data. 🖱️📋

2. Setting Up Outerbase 🧩🔌

1. Create an Outerbase account. 🆕🔐

2. Connect your Outerbase account to Supabase using the previously copied credentials. 🔗🔑

3. Creating a New Base 🗄️🆕

In Outerbase, create a new base to start building your hackathon database. 🛠️🏗️

Creating the Tables 🗃️📊

Let's begin by creating the necessary tables for our hackathon database. 🗃️📊

Hackathon Details Table 📅📝

• Hackathon_ID (Unique identifier for each hackathon)

• Hackathon_Name

• Start_Date

• End_Date

• Location

• Organizer_ID (Organizer's User_ID)

• Description

Challenges Table

• Challenge_ID (Unique identifier for each challenge)

• Hackathon_ID (Foreign key referencing the Hackathon Details Table)

• Challenge_Name

• Description

• Difficulty_Level

• Prize

Teams Table

• Team_ID (Unique identifier for each team)

• Team_Name

• Hackathon_ID (Foreign key referencing the Hackathon Details Table)

• Team_Leader_ID (User_ID of the team leader)

• Team_Size

Team Members Table

• Team_ID (Foreign key referencing the Teams Table)

• User_ID (Foreign key referencing the Users Table)

Submissions Table

• Submission_ID (Unique identifier for each submission)

• Team_ID (Foreign key referencing the Teams Table)

• Challenge_ID (Foreign key referencing the Challenges Table)

• Submission_Date

• Status (e.g., Submitted, In Review, Accepted, Rejected)

• Score

Judges Table

• Judge_ID (Unique identifier for each judge)

• First_Name

• Last_Name

• Email

Judging Criteria Table

• Criterion_ID (Unique identifier for each criterion)

• Challenge_ID (Foreign key referencing the Challenges Table)

• Criterion_Name

• Description

• Max_Score

Scores Table

• Submission_ID (Foreign key referencing the Submissions Table)

• Criterion_ID (Foreign key referencing the Judging Criteria Table)

• Score

Importing Data 📤📥

1. Import the CSV file containing hackathon details, challenges, teams, etc., into Supabase. 📤📥

2. Set the primary keys and save the data into the database. 🔑💾

Writing Queries 📝🔍

Now that we have set up our database, let's write some queries to retrieve and manage the data. 📝🔍

1. Retrieve all hackathons:

    SELECT * FROM Hackathon_Details;
   

   • Description: This query retrieves all records from the "Hackathon Details" table, providing information about all hackathons.

2. Retrieve all challenges for a specific hackathon:

    SELECT * FROM Challenges WHERE Hackathon_ID = <hackathon_id>;
   

   • Description: This query retrieves all challenges associated with a specific hackathon. Replace <hackathon_id> with the actual hackathon ID.

3. Retrieve all teams for a specific hackathon:

    SELECT * FROM Teams WHERE Hackathon_ID = <hackathon_id>;
   

   • Description: This query retrieves all teams participating in a specific hackathon. Replace <hackathon_id> with the actual hackathon ID.

4. Retrieve all team members for a specific team:

    SELECT * FROM Team_Members WHERE Team_ID = <team_id>;
   

   • Description: This query retrieves all members of a specific team. Replace <team_id> with the actual team ID.

5. Retrieve all submissions for a specific team:

    SELECT * FROM Submissions WHERE Team_ID = <team_id>;
   

   • Description: This query retrieves all submissions made by a specific team. Replace <team_id> with the actual team ID.

6. Retrieve all submissions for a specific challenge:

    SELECT * FROM Submissions WHERE Challenge_ID = <challenge_id>;
   

   • Description: This query retrieves all submissions made for a specific challenge. Replace <challenge_id> with the actual challenge ID.

7. Retrieve the highest scoring submission for a specific challenge:

    SELECT TOP 1 * FROM Submissions WHERE Challenge_ID = <challenge_id> ORDER BY Score DESC;
   

   • Description: This query retrieves the highest scoring submission for a specific challenge. Replace <challenge_id> with the actual challenge ID.

8. Retrieve the judges for a specific hackathon:

    SELECT * FROM Judges WHERE Judge_ID IN (SELECT Judge_ID FROM Judging_Criteria WHERE Challenge_ID IN (SELECT Challenge_ID FROM Challenges WHERE Hackathon_ID = <hackathon_id>));
   

   • Description: This query retrieves all judges associated with a specific hackathon. Replace <hackathon_id> with the actual hackathon ID.

9. Retrieve judging criteria for a specific challenge:

    SELECT * FROM Judging_Criteria WHERE Challenge_ID = <challenge_id>;
   

   • Description: This query retrieves all judging criteria for a specific challenge. Replace <challenge_id> with the actual challenge ID.

10. Calculate the average score for a specific team's submissions:

    SELECT AVG(Score) FROM Submissions WHERE Team_ID = <team_id>;
    

    • Description: This query calculates the average score for all submissions made by a specific team. Replace <team_id> with the actual team ID.

Queries 📝🔍

DataBase Tables📤

Conclusion 🎉🥳

Congratulations! You have successfully created a comprehensive hackathon database using Outerbase and Supabase. This database will allow you to efficiently manage hackathon events, challenges, teams, submissions, judges, and judging criteria. Feel free to customize and expand the database according to your specific needs. Happy hacking! 🎉🚀