-- Users Table
CREATE TABLE Users (
user_id SERIAL PRIMARY KEY,
username VARCHAR(255),
email VARCHAR(255) UNIQUE,
password VARCHAR(255),
role VARCHAR(50), -- Assuming roles like 'coach', 'athlete', etc.
date_of_birth DATE,
contact_info VARCHAR(255)
);
-- Teams Table
CREATE TABLE Teams (
team_id SERIAL PRIMARY KEY,
team_name VARCHAR(255),
coach_id INTEGER REFERENCES Users(user_id),
description TEXT
);
-- Athletes_Teams Table (Many-to-Many Relationship)
CREATE TABLE Athletes_Teams (
athlete_id INTEGER REFERENCES Users(user_id),
team_id INTEGER REFERENCES Teams(team_id),
join_date DATE,
leave_date DATE,
PRIMARY KEY (athlete_id, team_id)
);
-- Workouts Table
CREATE TABLE Workouts (
workout_id SERIAL PRIMARY KEY,
workout_name VARCHAR(255),
coach_id INTEGER REFERENCES Users(user_id),
team_id INTEGER REFERENCES Teams(team_id),
date DATE,
duration INTERVAL,
description TEXT
);
-- Swim Meets Table
CREATE TABLE Swim_Meets (
meet_id SERIAL PRIMARY KEY,
meet_name VARCHAR(255),
meet_date DATE,
location VARCHAR(255),
description TEXT
);
-- Attendance Table
CREATE TABLE Attendance (
attendance_id SERIAL PRIMARY KEY,
athlete_id INTEGER REFERENCES Users(user_id),
meet_id INTEGER REFERENCES Swim_Meets(meet_id),
attendance_status VARCHAR(50),
date DATE,
notes TEXT
);
-- Messages Table
CREATE TABLE Messages (
message_id SERIAL PRIMARY KEY,
sender_id INTEGER REFERENCES Users(user_id),
recipient_id INTEGER REFERENCES Users(user_id),
message_content TEXT,
timestamp TIMESTAMP
);
-- Performance Metrics Table
CREATE TABLE Performance_Metrics (
performance_id SERIAL PRIMARY KEY,
athlete_id INTEGER REFERENCES Users(user_id),
workout_id INTEGER REFERENCES Workouts(workout_id),
metric_type VARCHAR(100),
value FLOAT,
date DATE,
notes TEXT
);
-- Files Table
CREATE TABLE Files (
file_id SERIAL PRIMARY KEY,
file_name VARCHAR(255),
file_type VARCHAR(50),
file_size BIGINT,
file_url TEXT,
uploader_id INTEGER REFERENCES Users(user_id),
upload_date TIMESTAMP,
description TEXT
);
-- Swim Meet Events Table
CREATE TABLE Swim_Meet_Events (
event_id SERIAL PRIMARY KEY,
meet_id INTEGER REFERENCES Swim_Meets(meet_id),
event_name VARCHAR(255),
event_type VARCHAR(100), -- e.g., freestyle, breaststroke, relay, etc.
event_distance INTEGER, -- distance
distance_unit VARCHAR(10), -- meters or yards
event_gender VARCHAR(10), -- gender category (optional)
event_age_group VARCHAR(20) -- age group category (optional)
);
-- Swimmers_Events Table (Many-to-Many Relationship)
CREATE TABLE Swimmers_Events (
swimmer_event_id SERIAL PRIMARY KEY,
event_id INTEGER REFERENCES Swim_Meet_Events(event_id),
athlete_id INTEGER REFERENCES Users(user_id),
swim_time INTERVAL -- the time taken by the swimmer in the event
);
-- Event Results Table
CREATE TABLE Event_Results (
result_id SERIAL PRIMARY KEY,
meet_id INTEGER REFERENCES Swim_Meets(meet_id),
event_id INTEGER REFERENCES Swim_Meet_Events(event_id),
athlete_id INTEGER REFERENCES Users(user_id),
time_result INTERVAL, -- time taken by the swimmer in the event
placement INTEGER,
points_earned INTEGER
);