---
tags: SQL
---
# CREATE TABLE SQL

CREATE TABLE `members` (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `no` VARCHAR(12) NOT NULL COMMENT '會員編號',
    `group_id` INT(11) DEFAULT 0 COMMENT '對應權限',
    `account` VARCHAR(125) NOT NULL,
    `password` VARCHAR(255) NOT NULL,
    `name` VARCHAR(50) NOT NULL,
    `email` VARCHAR(255) NULL,
    `points` BIGINT(20) NOT NULL DEFAULT 50 COMMENT '總點數',
    `m_points` BIGINT(20) NOT NULL DEFAULT 50 COMMENT '當月點數',
    `coins` BIGINT(20)  DEFAULT 0 COMMENT '金幣',
    `status` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '0=> 建立, 1=> 正常, 9=>凍結',
    `inner_type` VARCHAR(5) DEFAULT "ADV" COMMENT '內部類別：BA,ADV,PRO',
    `show_type` VARCHAR(5) DEFAULT 0 COMMENT '外顯類別',
    `age` INT(100) NULL,
    `gender` VARCHAR(5) NULL,
    `skype_id` VARCHAR(50) NULL,
    `line_id` VARCHAR(50) NULL,
    `can_revised` TINYINT DEFAULT 0 COMMENT '1=>想被糾正',
    `can_recommand` TINYINT DEFAULT 0 COMMENT '1=>想被推薦',
    `job_title` VARCHAR(50) NULL COMMENT '抬頭',
    `sector` VARCHAR(50) NULL COMMENT '職業類別',
    `tag_id` VARCHAR(255) NULL,
    `ielts` INT(2) NULL,
    `ielts_speaking` INT(2) NULL,
    `tolft` INT(3) NULL,
    `tolft_speaking` INT(3) NULL,
    `motivation` VARCHAR(225) NULL,
    `introduction` VARCHAR(225) NULL,
    `remark` VARCHAR(225) NULL COMMENT '備註離開理由/回歸原因',
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `created_ip` VARCHAR(50) NOT NULL,
    `updated_ip` VARCHAR(50) NOT NULL,
    `deleted_at` TIMESTAMP NULL,
    PRIMARY KEY (`id`),
    UNIQUE (`no`)
);

CREATE TABLE `member_tag_logs`(
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `tag_id` BIGINT(20) NOT NULL,
    `member_id` BIGINT(20) NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `deleted_at` TIMESTAMP NULL,
    PRIMARY KEY (`id`),
    INDEX (`member_id`)
);

CREATE TABLE `tags`(
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) NOT NULL,
    `catalog` VARCHAR(50) NOT NULL,
     PRIMARY KEY (`id`)
);

/*狀態一覽表  for 對照 & 翻譯*/
CREATE TABLE `status`(
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `no` VARCHAR(50) NOT NULL,
    `name` VARCHAR(50) NOT NULL,
     PRIMARY KEY (`id`)
);

/*取號表*/
CREATE TABLE `nos`(
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `type` VARCHAR(50) NOT NULL COMMENT 'ect. skype',
    `no` VARCHAR(50) NOT NULL COMMENT 'C,M',
    `current` BIGINT(20) NOT NULL COMMENT '目前號碼',
     PRIMARY KEY (`id`)
);

/*skype room link*/
CREATE TABLE `skype_rooms`(
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `url` VARCHAR(255) NOT NULL,
    PRIMARY KEY (`id`)
);


CREATE TABLE `classes`(
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `no` VARCHAR(12) NOT NULL ,
    `date_s` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '開始時間 GTM+0', 
    `date_e` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '結束時間 GTM+0',
    `link` VARCHAR(255) NOT NULL,
    `title` VARCHAR(255) NOT NULL,
    `level` VARCHAR(5) COMMENT '內部類別：BA,ADV,PRO' NULL,
    `tag_id` VARCHAR(255) NULL,
    `want_revised` TINYINT DEFAULT 0 COMMENT '1=>想被糾正',
    `want_recommand` TINYINT DEFAULT 1 COMMENT '1=>想被推薦',
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `created_by` INT(11) NOT NULL COMMENT '主持人：會員id',
    `updated_by` INT(11) NULL COMMENT 'member_id',
    `deleted_at` TIMESTAMP NULL,
    PRIMARY KEY (`id`),
    INDEX (`no`)
);

CREATE TABLE `classes_joins`(
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `class_id` BIGINT(20) NOT NULL,
    `parent_id` ???
    `member_id` INT(11) NOT NULL,
    `type` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '0=>參加者, 1=>host, 2=>助理host',
    `uploaded_at` TIMESTAMP NULL COMMENT '上傳音檔 null=>尚未上傳',
    `reported_status` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '1=>正常, 2=>遲到, 3=>未到, 4=>早退',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_by` INT(11) NULL,
    `reported_at` TIMESTAMP NULL,
    `reported_by` INT(11) NULL COMMENT '回報者之member_id',
    `deleted_at` TIMESTAMP NULL,
    `deleted_by` INT(11) NULL,
    PRIMARY KEY (`id`),
    INDEX (`class_id`),
    INDEX (`member_id`)
);


CREATE TABLE `classes_questions`(
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `class_id` BIGINT(20) NOT NULL,
    `questions` JSON NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `deleted_at` TIMESTAMP NULL,
    PRIMARY KEY (`id`),
    INDEX (`class_id`)
);

CREATE TABLE `recordings`(
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `class_id` BIGINT(20) NOT NULL,
    `member_id` INT(11) NOT NULL,
    `recording_url` VARCHAR(255) NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `deleted_at` TIMESTAMP NULL,
    PRIMARY KEY (`id`),
    INDEX (`class_id`),
    INDEX (`member_id`)
)