Example
CREATE SCHEMA IF NOT EXISTS `backgammon`;
USE `backgammon`;
DROP TABLE IF EXISTS `user_in_game_room`;
DROP TABLE IF EXISTS `game_users`;
DROP TABLE IF EXISTS `user_in_game_room`;
CREATE TABLE `game_users`
(
`user_id` BIGINT NOT NULL AUTO_INCREMENT,
`first_name` VARCHAR(255) NOT NULL,
`last_name` VARCHAR(255) NOT NULL,
`email` VARCHAR(255) NOT NULL UNIQUE,
`user_name` VARCHAR(255) NOT NULL UNIQUE,
`password` VARCHAR(255) NOT NULL,
`role` VARCHAR(255) NOT NULL,
`last_updated_date` DATETIME NOT NULL,
`last_updated_by` BIGINT NOT NULL,
`created_date` DATETIME NOT NULL,
`created_by` BIGINT NOT NULL,
PRIMARY KEY(`user_id`)
);
DROP TABLE IF EXISTS `game_rooms`;
CREATE TABLE `game_rooms`
(
`game_room_id` BIGINT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL,
`private` BIT(1) NOT NULL,
`white` BIGINT DEFAULT NULL,
`black` BIGINT DEFAULT NULL,
`opened_by` BIGINT NOT NULL,
`speed` BIT(3) NOT NULL,
`last_updated_date` DATETIME NOT NULL,
`last_updated_by` BIGINT NOT NULL,
`created_date` DATETIME NOT NULL,
`created_by` BIGINT NOT NULL,
`token` VARCHAR(255) AS (SHA1(CONCAT(`name`, "This is a qwe secret 123", `created_by`, `created_date`))),
PRIMARY KEY(`game_room_id`)
);
CREATE TABLE `user_in_game_room`
(
`user_id` BIGINT NOT NULL,
`game_room_id` BIGINT NOT NULL,
`last_updated_date` DATETIME NOT NULL,
`last_updated_by` BIGINT NOT NULL,
`created_date` DATETIME NOT NULL,
`created_by` BIGINT NOT NULL,
PRIMARY KEY(`user_id`, `game_room_id`),
FOREIGN KEY (`user_id`) REFERENCES `game_users`(`user_id`),
FOREIGN KEY (`game_room_id`) REFERENCES `game_rooms`(`game_room_id`)
);
