db/init.sql - develop - yu.leontyev/TrainerDb

db/init.sql
105 строк5 KB
Yura Leontyev
Устранил закольцованность, актуализировал документацию
18 фев 2026, 21:34
18 фев 2026, 21:34ffbda7a
-- таблица групп мышц
CREATE TABLE muscle_group (
	id SMALLINT NOT NULL PRIMARY KEY,		-- перв ключ, неинкремент, чтоб использовать smallint
	group_full_name VARCHAR(40) NOT NULL,	-- полное наименование группы мышц
	group_short_name VARCHAR(10) NULL,		-- короткое наименование группы мышц
	sort_order  SMALLINT NOT NULL DEFAULT 0	-- для задания порядка сортировки
);

-- таблица мышц
CREATE TABLE muscle (
	id SMALLINT NOT NULL,				-- перв ключ, неинкремент, чтоб использовать smallint
	muscle_name VARCHAR(60) NOT NULL,	-- полное наименование мышцы
	group_id SMALLINT,					-- связь с группой мышц
	muscle_short VARCHAR(20),			-- короткое наименование мышцы
	PRIMARY KEY(id),					-- назначить первичный ключ
	FOREIGN KEY(group_id) REFERENCES muscle_group(id)	--внешний ключ
	ON DELETE SET NULL	--при удалении группы заменить значение на null, упражнение остается
	ON UPDATE CASCADE	--при обновлении идентификатора обновить в текущей таблице		
);

-- таблица типа упражнений
CREATE TABLE exercise_type (
	id SMALLINT NOT NULL PRIMARY KEY,
	type_code VARCHAR(20) NOT NULL UNIQUE,	-- 'base', 'accessory', 'compound', 'isolation'...
	name VARCHAR(50) NOT NULL				-- 'Базовое', 'Добивка', 'Изолирующее'...
);

-- таблица упражнений
CREATE TABLE exercise (
	id INTEGER PRIMARY KEY AUTOINCREMENT,
	name VARCHAR(120) NOT NULL,
	description  TEXT NULL,
	exercise_type_id SMALLINT NULL,		-- связь с типом упражнения
	animation_path VARCHAR(255) NULL,	-- путь для храния gif-анимации упражнения
	FOREIGN KEY (exercise_type_id) REFERENCES exercise_type(id)
	ON DELETE SET NULL 
	ON UPDATE CASCADE
);

-- таблица для реализации связи многие-ко-многим упражнение-мышца
CREATE TABLE exercise_muscle (
	id INTEGER PRIMARY KEY AUTOINCREMENT, --суррогатный ключ
	exercise_id	INTEGER	NOT NULL,
	muscle_id SMALLINT NOT NULL,
	is_primary BOOLEAN DEFAULT FALSE,		-- основная целевая мышца?
	FOREIGN KEY (exercise_id) REFERENCES exercise(id)
	ON DELETE CASCADE,                        -- если удалить упражнение, удаляем все связи
	FOREIGN KEY (muscle_id) REFERENCES muscle(id)
	ON DELETE SET NULL              -- 
);

-- таблица тренировочного плана
CREATE TABLE training_plan (
	id SMALLINT NOT NULL PRIMARY KEY,
	plan_name VARCHAR(30) NOT NULL,
	week_count SMALLINT NOT NULL DEFAULT 8,
	trainigs_per_week SMALLINT NOT NULL CHECK (trainigs_per_week BETWEEN 1 AND 5),
	start_date DATE,
	is_active BOOLEAN DEFAULT TRUE
);

-- таблица шаблон тренировки
CREATE TABLE training_template (
	id INTEGER NOT NULL PRIMARY KEY,
	plan_id SMALLINT NOT NULL,
	day_number  SMALLINT NOT NULL CHECK (day_number BETWEEN 1 AND 7), --в коде приложения предусмотреть логику для проверки "Номер дня" не превышает количество тренировок в неделю из таблицы тренировочного плана
	day_name VARCHAR (20) NOT NULL,
	FOREIGN KEY (plan_id) REFERENCES training_plan(id) ON DELETE CASCADE,
	UNIQUE (plan_id, day_number)
);

-- шаблон дня тренировки 
CREATE TABLE training_day_muscle_exercise (
	id INTEGER NOT NULL PRIMARY KEY,
	day_template_id  INTEGER  NOT NULL,
	muscle_group_id  SMALLINT NOT NULL,
	max_exercises SMALLINT NOT NULL DEFAULT 3,
	--PRIMARY KEY (day_template_id, muscle_group_id),
	FOREIGN KEY (day_template_id)	REFERENCES training_template(id) 
	ON DELETE CASCADE,
	FOREIGN KEY (muscle_group_id)	REFERENCES muscle_group(id) 
	ON DELETE RESTRICT,
	CONSTRAINT  training_day_muscle_group_unique 
	UNIQUE (day_template_id, muscle_group_id)
);

-- тренировка 
CREATE TABLE trainig (
    id                       INTEGER PRIMARY KEY AUTOINCREMENT,
    training_number          INTEGER NOT NULL,
    day_template_id          INTEGER NOT NULL,
    exercise_muscle_id       INTEGER NOT NULL,               -- ← ключевое изменение
    training_date            DATE NULL,
    load                     FLOAT DEFAULT 0.0,
    set_count                INTEGER DEFAULT 1,
    repetition_count         INTEGER DEFAULT 1,
    order_num                SMALLINT DEFAULT 0,
    notes                    TEXT NULL,

    FOREIGN KEY (day_template_id)     REFERENCES training_template(id) ON DELETE RESTRICT,
    FOREIGN KEY (exercise_muscle_id)  REFERENCES exercise_muscle(id)   ON DELETE RESTRICT
);

-- исправление косяка с отстуствием уникальности имени упражнения
CREATE UNIQUE INDEX idx_exercise_name_unique ON exercise(name);