Create Postgres Schema

[Frostzeichen]

Create a database schema using Postgres based on the ERD at #1.

[Frostzeichen]

Public Schema

-- SCHEMA: public

-- DROP SCHEMA IF EXISTS public ;

CREATE SCHEMA IF NOT EXISTS public
    AUTHORIZATION pg_database_owner;

COMMENT ON SCHEMA public
    IS 'standard public schema';

GRANT USAGE ON SCHEMA public TO PUBLIC;

GRANT ALL ON SCHEMA public TO pg_database_owner;

Tables

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(50),
    username VARCHAR(50),
    created_at TIMESTAMPTZ DEFAULT LOCALTIMESTAMP,
    avatar VARCHAR(250)
);

CREATE TABLE hackathons (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(250),
    description TEXT,
    created_at TIMESTAMPTZ DEFAULT LOCALTIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT LOCALTIMESTAMP
);

CREATE TABLE hackathon_teams (
    id BIGSERIAL PRIMARY KEY,
    hackathon_id BIGINT REFERENCES hackathons(id) ON DELETE SET NULL,
    name VARCHAR(250)
);

CREATE TABLE hackathon_participants (
    id BIGSERIAL PRIMARY KEY,
    hackathon_id BIGINT REFERENCES hackathons(id) ON DELETE SET NULL,
    hackathon_team_id BIGINT REFERENCES hackathon_teams(id) ON DELETE SET NULL,
    user_id BIGINT REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE user_votes (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT REFERENCES users(id) ON DELETE SET NULL,
    hackathon_id BIGINT REFERENCES hackathons(id) ON DELETE SET NULL,
    hackathon_team_id BIGINT REFERENCES hackathon_teams(id) ON DELETE SET NULL,
    created_at TIMESTAMPTZ DEFAULT LOCALTIMESTAMP
);

CREATE TABLE hackathon_judges (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT REFERENCES users(id) ON DELETE SET NULL,
    hackathon_id BIGINT REFERENCES hackathons(id) ON DELETE SET NULL
);

CREATE TABLE judge_votes (
    id BIGSERIAL PRIMARY KEY,
    hackathon_judge_id BIGINT REFERENCES hackathon_judges(id) ON DELETE SET NULL,
    score INTEGER,
    comments TEXT,
    hackathon_id BIGINT REFERENCES hackathons(id),
    hackathon_team BIGINT REFERENCES hackathon_teams(id) ON DELETE SET NULL,
    created_at TIMESTAMPTZ DEFAULT LOCALTIMESTAMP
);

Temporary Tables Version

CREATE TEMPORARY TABLE users (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(50),
    username VARCHAR(50),
    created_at TIMESTAMPTZ DEFAULT LOCALTIMESTAMP,
    avatar VARCHAR(250)
);

CREATE TEMPORARY TABLE hackathons (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(250),
    description TEXT,
    created_at TIMESTAMPTZ DEFAULT LOCALTIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT LOCALTIMESTAMP
);

CREATE TEMPORARY TABLE hackathon_teams (
    id BIGSERIAL PRIMARY KEY,
    hackathon_id BIGINT REFERENCES hackathons(id) ON DELETE SET NULL,
    name VARCHAR(250)
);

CREATE TEMPORARY TABLE hackathon_participants (
    id BIGSERIAL PRIMARY KEY,
    hackathon_id BIGINT REFERENCES hackathons(id) ON DELETE SET NULL,
    hackathon_team_id BIGINT REFERENCES hackathon_teams(id) ON DELETE SET NULL,
    user_id BIGINT REFERENCES users(id) ON DELETE SET NULL
);

CREATE TEMPORARY TABLE user_votes (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT REFERENCES users(id) ON DELETE SET NULL,
    hackathon_id BIGINT REFERENCES hackathons(id) ON DELETE SET NULL,
    hackathon_team_id BIGINT REFERENCES hackathon_teams(id) ON DELETE SET NULL,
    created_at TIMESTAMPTZ DEFAULT LOCALTIMESTAMP
);

CREATE TEMPORARY TABLE hackathon_judges (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT REFERENCES users(id) ON DELETE SET NULL,
    hackathon_id BIGINT REFERENCES hackathons(id) ON DELETE SET NULL
);

CREATE TEMPORARY TABLE judge_votes (
    id BIGSERIAL PRIMARY KEY,
    hackathon_judge_id BIGINT REFERENCES hackathon_judges(id) ON DELETE SET NULL,
    score INTEGER,
    comments TEXT,
    hackathon_id REFERENCES hackathon(id),
    hackathon_team BIGINT REFERENCES hackathon_teams(id) ON DELETE SET NULL,
    created_at TIMESTAMPTZ DEFAULT LOCALTIMESTAMP
);