53 lines
1.6 KiB
MySQL
53 lines
1.6 KiB
MySQL
|
-- Migration 002: Organization (Clubs, Divisions, Training Groups)
|
||
|
|
-- Erstellt: 2026-04-21
|
||
|
|
-- Beschreibung: Organisationsstruktur für Vereine und Trainingsgruppen
|
||
|
|
|
||
|
|
-- Clubs (Vereine)
|
||
|
|
CREATE TABLE clubs (
|
||
|
|
id SERIAL PRIMARY KEY,
|
||
|
|
name VARCHAR(200) NOT NULL,
|
||
|
|
abbreviation VARCHAR(50),
|
||
|
|
description TEXT,
|
||
|
|
status VARCHAR(50) DEFAULT 'active',
|
||
|
|
created_at TIMESTAMP DEFAULT NOW(),
|
||
|
|
updated_at TIMESTAMP DEFAULT NOW()
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX idx_clubs_status ON clubs(status);
|
||
|
|
|
||
|
|
-- Divisions (Sparten) - optional
|
||
|
|
CREATE TABLE divisions (
|
||
|
|
id SERIAL PRIMARY KEY,
|
||
|
|
club_id INT REFERENCES clubs(id) ON DELETE CASCADE,
|
||
|
|
name VARCHAR(200) NOT NULL,
|
||
|
|
focus_area VARCHAR(100), -- karate, selbstverteidigung, gewaltschutz
|
||
|
|
created_at TIMESTAMP DEFAULT NOW(),
|
||
|
|
updated_at TIMESTAMP DEFAULT NOW()
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX idx_divisions_club ON divisions(club_id);
|
||
|
|
|
||
|
|
-- Training Groups (Trainingsgruppen)
|
||
|
|
CREATE TABLE training_groups (
|
||
|
|
id SERIAL PRIMARY KEY,
|
||
|
|
club_id INT REFERENCES clubs(id) ON DELETE CASCADE,
|
||
|
|
division_id INT REFERENCES divisions(id),
|
||
|
|
name VARCHAR(200) NOT NULL,
|
||
|
|
focus VARCHAR(100),
|
||
|
|
level VARCHAR(50),
|
||
|
|
age_group VARCHAR(50),
|
||
|
|
weekday VARCHAR(20),
|
||
|
|
time_start TIME,
|
||
|
|
time_end TIME,
|
||
|
|
location VARCHAR(200),
|
||
|
|
trainer_id INT REFERENCES profiles(id),
|
||
|
|
co_trainer_ids JSONB, -- [1, 2, 3]
|
||
|
|
status VARCHAR(50) DEFAULT 'active',
|
||
|
|
created_at TIMESTAMP DEFAULT NOW(),
|
||
|
|
updated_at TIMESTAMP DEFAULT NOW()
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX idx_groups_club ON training_groups(club_id);
|
||
|
|
CREATE INDEX idx_groups_trainer ON training_groups(trainer_id);
|
||
|
|
CREATE INDEX idx_groups_status ON training_groups(status);
|