61 lines
2.5 KiB
MySQL
61 lines
2.5 KiB
MySQL
|
CREATE TABLE IF NOT EXISTS `users` (
|
||
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
||
|
|
`lastname` VARCHAR(255) NOT NULL,
|
||
|
|
`email` VARCHAR(255) NOT NULL UNIQUE,
|
||
|
|
`password` VARCHAR(255) NOT NULL,
|
||
|
|
`is_admin` BOOLEAN DEFAULT FALSE,
|
||
|
|
`gets_reminder` BOOLEAN DEFAULT TRUE,
|
||
|
|
`iban` VARCHAR(34),
|
||
|
|
`bankname` VARCHAR(255),
|
||
|
|
`blz` VARCHAR(11),
|
||
|
|
`mobile` VARCHAR(20),
|
||
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
`deleted_at` TIMESTAMP NULL DEFAULT NULL
|
||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS `seasons` (
|
||
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
||
|
|
`name` VARCHAR(255) NOT NULL,
|
||
|
|
`max_identical_jokertipps` INT DEFAULT 1,
|
||
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
`updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS `rounds` (
|
||
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
||
|
|
`name` VARCHAR(255) NOT NULL,
|
||
|
|
`season_id` INT NOT NULL,
|
||
|
|
`report` TEXT,
|
||
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
CONSTRAINT `fk_rounds_season` FOREIGN KEY (`season_id`) REFERENCES `seasons` (`id`) ON DELETE CASCADE
|
||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS `games` (
|
||
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
||
|
|
`round_id` INT NOT NULL,
|
||
|
|
`team1` VARCHAR(255) NOT NULL,
|
||
|
|
`team2` VARCHAR(255) NOT NULL,
|
||
|
|
`score1` INT DEFAULT NULL,
|
||
|
|
`score2` INT DEFAULT NULL,
|
||
|
|
`kickoff` DATETIME NOT NULL,
|
||
|
|
CONSTRAINT `fk_games_round` FOREIGN KEY (`round_id`) REFERENCES `rounds` (`id`) ON DELETE CASCADE
|
||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS `payments` (
|
||
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
||
|
|
`season_id` INT NOT NULL,
|
||
|
|
`user_id` INT NOT NULL,
|
||
|
|
`paid_date` DATE DEFAULT NULL,
|
||
|
|
CONSTRAINT `fk_payments_season` FOREIGN KEY (`season_id`) REFERENCES `seasons` (`id`) ON DELETE CASCADE,
|
||
|
|
CONSTRAINT `fk_payments_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
|
||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS `quotas` (
|
||
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
||
|
|
`season_id` INT NOT NULL,
|
||
|
|
`name` VARCHAR(255) NOT NULL,
|
||
|
|
`order` INT NOT NULL,
|
||
|
|
`prc` DECIMAL(5,2) NOT NULL,
|
||
|
|
CONSTRAINT `fk_quotas_season` FOREIGN KEY (`season_id`) REFERENCES `seasons` (`id`) ON DELETE CASCADE
|
||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|