32 lines
1.4 KiB
SQL
32 lines
1.4 KiB
SQL
CREATE TABLE IF NOT EXISTS tournament_registrations (
|
|
id SERIAL PRIMARY KEY,
|
|
tournament_id INTEGER NOT NULL REFERENCES tournament_info(id) ON DELETE CASCADE,
|
|
entry_label TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS tournament_registration_values (
|
|
registration_id INTEGER NOT NULL REFERENCES tournament_registrations(id) ON DELETE CASCADE,
|
|
signup_field_id INTEGER NOT NULL REFERENCES tournament_signup_fields(id) ON DELETE CASCADE,
|
|
value TEXT NOT NULL,
|
|
PRIMARY KEY (registration_id, signup_field_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS tournament_participants (
|
|
id SERIAL PRIMARY KEY,
|
|
registration_id INTEGER NOT NULL REFERENCES tournament_registrations(id) ON DELETE CASCADE,
|
|
position INTEGER NOT NULL DEFAULT 0
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS tournament_participant_values (
|
|
participant_id INTEGER NOT NULL REFERENCES tournament_participants(id) ON DELETE CASCADE,
|
|
signup_field_id INTEGER NOT NULL REFERENCES tournament_signup_fields(id) ON DELETE CASCADE,
|
|
value TEXT NOT NULL,
|
|
PRIMARY KEY (participant_id, signup_field_id)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_tournament_registrations_tournament
|
|
ON tournament_registrations (tournament_id, created_at DESC, id DESC);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_tournament_participants_registration
|
|
ON tournament_participants (registration_id, position, id);
|