database 설계

[BBSSJJ]

---

-- Schema carboncheck_db

---

CREATE SCHEMA IF NOT EXISTS carboncheck_db DEFAULT CHARACTER SET utf8 ; USE carboncheck_db ;

---

-- Table carboncheck_db.group

---

CREATE TABLE IF NOT EXISTS carboncheck_db.group ( group_id VARCHAR(255) NOT NULL, PRIMARY KEY (group_id)) ENGINE = InnoDB;

---

-- Table carboncheck_db.user

---

CREATE TABLE IF NOT EXISTS carboncheck_db.user ( user_id INT NOT NULL AUTO_INCREMENT, group_id VARCHAR(255) NOT NULL, email VARCHAR(255) NULL DEFAULT NULL, password VARCHAR(60) NULL DEFAULT NULL, auth_type VARCHAR(30) NULL DEFAULT NULL, name VARCHAR(30) NULL DEFAULT NULL, start_time DATETIME NULL, end_time DATETIME NULL, PRIMARY KEY (user_id), INDEX fk_user_group_idx (group_id ASC) VISIBLE, CONSTRAINT fk_user_group FOREIGN KEY (group_id) REFERENCES carboncheck_db.group (group_id) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB AUTO_INCREMENT = 4 DEFAULT CHARACTER SET = utf8mb3;

---

-- Table carboncheck_db.water_usage

---

CREATE TABLE IF NOT EXISTS carboncheck_db.water_usage ( water_usage_id INT NOT NULL AUTO_INCREMENT, user_user_id INT NOT NULL, start_time DATETIME NULL, end_time DATETIME NULL, place VARCHAR(45) NULL, amount FLOAT NULL, PRIMARY KEY (water_usage_id), INDEX fk_water_usage_user1_idx (user_user_id ASC) VISIBLE, CONSTRAINT fk_water_usage_user1 FOREIGN KEY (user_user_id) REFERENCES carboncheck_db.user (user_id) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;

---

-- Table carboncheck_db.score

---

CREATE TABLE IF NOT EXISTS carboncheck_db.score ( score_id INT NOT NULL AUTO_INCREMENT, user_user_id INT NOT NULL, date DATE NULL, score INT NULL, PRIMARY KEY (score_id), INDEX fk_score_user1_idx (user_user_id ASC) VISIBLE, CONSTRAINT fk_score_user1 FOREIGN KEY (user_user_id) REFERENCES carboncheck_db.user (user_id) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;

---

-- Table carboncheck_db.electricity_usage

---

CREATE TABLE IF NOT EXISTS carboncheck_db.electricity_usage ( electricitiy_usage_id INT NOT NULL AUTO_INCREMENT, user_user_id INT NOT NULL, PRIMARY KEY (electricitiy_usage_id), INDEX fk_electricity_usage_user1_idx (user_user_id ASC) VISIBLE, CONSTRAINT fk_electricity_usage_user1 FOREIGN KEY (user_user_id) REFERENCES carboncheck_db.user (user_id) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;

[BBSSJJ]

수정된 DB

-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

---

-- Schema mydb

---

---

-- Schema carboncheck_db

---

---

-- Schema carboncheck_db

---

CREATE SCHEMA IF NOT EXISTS carboncheck_db DEFAULT CHARACTER SET utf8mb3 ; USE carboncheck_db ;

---

-- Table carboncheck_db.home_server

---

CREATE TABLE IF NOT EXISTS carboncheck_db.home_server ( home_server_id VARCHAR(255) NOT NULL, PRIMARY KEY (home_server_id)) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8mb3;

---

-- Table carboncheck_db.user

---

CREATE TABLE IF NOT EXISTS carboncheck_db.user ( user_id INT NOT NULL AUTO_INCREMENT, auth_type VARCHAR(30) NULL DEFAULT NULL, email VARCHAR(255) NULL DEFAULT NULL, name VARCHAR(60) CHARACTER SET 'utf8' NULL DEFAULT NULL, password VARCHAR(60) NULL DEFAULT NULL, home_server_id VARCHAR(255) NULL, PRIMARY KEY (user_id), INDEX fk_user_home_server1_idx (home_server_id ASC) VISIBLE, CONSTRAINT fk_user_home_server1 FOREIGN KEY (home_server_id) REFERENCES carboncheck_db.home_server (home_server_id) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB AUTO_INCREMENT = 5 DEFAULT CHARACTER SET = utf8mb3;

---

-- Table carboncheck_db.electricity_usage

---

CREATE TABLE IF NOT EXISTS carboncheck_db.electricity_usage ( electricitiy_usage_id INT NOT NULL AUTO_INCREMENT, user_id INT NOT NULL, PRIMARY KEY (electricitiy_usage_id), INDEX fk_electricity_usage_user1_idx (user_id ASC) VISIBLE, CONSTRAINT fk_electricity_usage_user1 FOREIGN KEY (user_id) REFERENCES carboncheck_db.user (user_id)) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8mb3;

---

-- Table carboncheck_db.score

---

CREATE TABLE IF NOT EXISTS carboncheck_db.score ( score_id INT NOT NULL AUTO_INCREMENT, user_id INT NOT NULL, date DATE NULL DEFAULT NULL, score INT NULL DEFAULT NULL, PRIMARY KEY (score_id), INDEX fk_score_user1_idx (user_id ASC) VISIBLE, CONSTRAINT fk_score_user1 FOREIGN KEY (user_id) REFERENCES carboncheck_db.user (user_id)) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8mb3;

---

-- Table carboncheck_db.water_usage

---

CREATE TABLE IF NOT EXISTS carboncheck_db.water_usage ( water_usage_id INT NOT NULL AUTO_INCREMENT, user_id INT NOT NULL, start_time DATETIME NULL DEFAULT NULL, end_time DATETIME NULL DEFAULT NULL, place VARCHAR(45) NULL DEFAULT NULL, amount FLOAT NULL DEFAULT NULL, PRIMARY KEY (water_usage_id), INDEX fk_water_usage_user1_idx (user_id ASC) VISIBLE, CONSTRAINT fk_water_usage_user1 FOREIGN KEY (user_id) REFERENCES carboncheck_db.user (user_id)) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8mb3;

SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

[BBSSJJ]

최종 db

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

---

-- Schema mydb

---

---

-- Schema carboncheck_db

---

---

-- Schema carboncheck_db

---

CREATE SCHEMA IF NOT EXISTS carboncheck_db DEFAULT CHARACTER SET utf8mb3 ; USE carboncheck_db ;

---

-- Table carboncheck_db.home_server

---

CREATE TABLE IF NOT EXISTS carboncheck_db.home_server ( home_server_id VARCHAR(255) NOT NULL, PRIMARY KEY (home_server_id)) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8mb3;

---

-- Table carboncheck_db.user

---

CREATE TABLE IF NOT EXISTS carboncheck_db.user ( user_id INT NOT NULL AUTO_INCREMENT, auth_type VARCHAR(30) NULL DEFAULT NULL, email VARCHAR(255) NULL DEFAULT NULL, name VARCHAR(60) CHARACTER SET 'utf8mb3' NULL DEFAULT NULL, password VARCHAR(60) NULL DEFAULT NULL, home_server_id VARCHAR(255) NULL DEFAULT NULL, traget_amount INT DEFAULT '1000', PRIMARY KEY (user_id), INDEX fk_user_home_server1_idx (home_server_id ASC) VISIBLE, CONSTRAINT fk_user_home_server1 FOREIGN KEY (home_server_id) REFERENCES carboncheck_db.home_server (home_server_id)) ENGINE = InnoDB AUTO_INCREMENT = 11 DEFAULT CHARACTER SET = utf8mb3;

---

-- Table carboncheck_db.plug

---

CREATE TABLE IF NOT EXISTS carboncheck_db.plug ( plug_id VARCHAR(100) NOT NULL, user_id INT NOT NULL, PRIMARY KEY (plug_id), INDEX fk_user_plug_user1_idx (user_id ASC) VISIBLE, CONSTRAINT fk_user_plug_user1 FOREIGN KEY (user_id) REFERENCES carboncheck_db.user (user_id) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;

---

-- Table carboncheck_db.electricity_usage

---

CREATE TABLE IF NOT EXISTS carboncheck_db.electricity_usage ( electricity_usage_id INT NOT NULL AUTO_INCREMENT, plug_id VARCHAR(100) NOT NULL, date DATE NULL, amount INT NULL, PRIMARY KEY (electricity_usage_id), INDEX fk_electricity_usage_user_plug1_idx (plug_id ASC) VISIBLE, CONSTRAINT fk_electricity_usage_user_plug1 FOREIGN KEY (plug_id) REFERENCES carboncheck_db.plug (plug_id) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8mb3;

---

-- Table carboncheck_db.score

---

CREATE TABLE IF NOT EXISTS carboncheck_db.score ( score_id INT NOT NULL AUTO_INCREMENT, user_id INT NOT NULL, date DATE NULL DEFAULT NULL, score INT NULL DEFAULT NULL, PRIMARY KEY (score_id), INDEX fk_score_user1_idx (user_id ASC) VISIBLE, CONSTRAINT fk_score_user1 FOREIGN KEY (user_id) REFERENCES carboncheck_db.user (user_id)) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8mb3;

---

-- Table carboncheck_db.water_usage

---

CREATE TABLE IF NOT EXISTS carboncheck_db.water_usage ( water_usage_id INT NOT NULL AUTO_INCREMENT, user_id INT NULL DEFAULT NULL, start_time DATETIME NULL DEFAULT NULL, end_time DATETIME NULL DEFAULT NULL, place VARCHAR(45) NULL DEFAULT NULL, amount FLOAT NULL DEFAULT NULL, PRIMARY KEY (water_usage_id), CONSTRAINT fk_water_usage_user1 FOREIGN KEY (user_id) REFERENCES carboncheck_db.user (user_id)) ENGINE = InnoDB AUTO_INCREMENT = 31 DEFAULT CHARACTER SET = utf8mb3;

SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;