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:
An account on Supabase: Sign up here. 🌐🔗
A project set up on Supabase with credentials generated. 🗂️🔑
Getting Started 🏁🚀
1. Connecting to Supabase 🌐🔗
Create a project on Supabase, select your region, and generate a password. 🌍🔐
Go to project settings and then database to find and copy all the necessary connection data. 🖱️📋
2. Setting Up Outerbase 🧩🔌
Create an Outerbase account. 🆕🔐
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 📅📝
Challenges Table
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
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
Judging Criteria Table
Scores Table
Importing Data 📤📥
Import the CSV file containing hackathon details, challenges, teams, etc., into Supabase. 📤📥
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. 📝🔍
Retrieve all hackathons:
SELECT * FROM Hackathon_Details;
- Description: This query retrieves all records from the "Hackathon Details" table, providing information about all hackathons.
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.
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.
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.
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.
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.
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.
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.
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.
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! 🎉🚀
