Creating a Comprehensive Hackathon Database with Outerbase and Supabase 🚀🛠️

Introduction 📝 Link to heading

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 🧰✅ Link to heading

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 🏁🚀 Link to heading

1. Connecting to Supabase 🌐🔗 Link to heading

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 🧩🔌 Link to heading

Create an Outerbase account. 🆕🔐
Connect your Outerbase account to Supabase using the previously copied credentials. 🔗🔑

3. Creating a New Base 🗄️🆕 Link to heading

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

Creating the Tables 🗃️📊 Link to heading

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

Hackathon Details Table 📅📝 Link to heading

Hackathon_ID (Unique identifier for each hackathon)
Hackathon_Name
Start_Date
End_Date
Location
Organizer_ID (Organizer’s User_ID)
Description

Challenges Table Link to heading

Challenge_ID (Unique identifier for each challenge)
Hackathon_ID (Foreign key referencing the Hackathon Details Table)
Challenge_Name
Description
Difficulty_Level
Prize

Teams Table Link to heading

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 Link to heading

Team_ID (Foreign key referencing the Teams Table)
User_ID (Foreign key referencing the Users Table)

Submissions Table Link to heading

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 Link to heading

Judge_ID (Unique identifier for each judge)
First_Name
Last_Name
Email

Judging Criteria Table Link to heading

Criterion_ID (Unique identifier for each criterion)
Challenge_ID (Foreign key referencing the Challenges Table)
Criterion_Name
Description
Max_Score

Scores Table Link to heading

Submission_ID (Foreign key referencing the Submissions Table)
Criterion_ID (Foreign key referencing the Judging Criteria Table)
Score

Importing Data 📤📥 Link to heading

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 📝🔍 Link to heading

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 📝🔍 Link to heading

DataBase Tables📤 Link to heading

Conclusion 🎉🥳 Link to heading

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! 🎉🚀