Creating a Comprehensive Hackathon Database with Outerbase and Supabase ππ οΈ
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 π π
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 π€π₯
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.
- Description: This query retrieves all challenges associated with a specific hackathon. Replace
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.
- Description: This query retrieves all teams participating in a specific hackathon. Replace
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.
- Description: This query retrieves all members of a specific team. Replace
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.
- Description: This query retrieves all submissions made by a specific team. Replace
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.
- Description: This query retrieves all submissions made for a specific challenge. Replace
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.
- Description: This query retrieves the highest scoring submission for a specific challenge. Replace
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.
- Description: This query retrieves all judges associated with a specific hackathon. Replace
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.
- Description: This query retrieves all judging criteria for a specific challenge. Replace
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.
- Description: This query calculates the average score for all submissions made by a specific team. Replace
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! ππ