データベース - MySQL - DB設計ツール

fr-sato commented 10 years ago

以下のテーブルをDB設計ツールを用いて設計してもらいます。

■ユーザー情報

日本語名	カラム名	型
ユーザーID	user_id	INT
姓	last_name	TEXT
名	first_name	TEXT
メールアドレス	email	varchar(255)
都道府県ID	pref_id	ID
登録日時	created_at	datetime
更新日時	updated_at	timestamp

■都道府県情報

日本語名	カラム名	型
都道府県ID	pref_id	INT
都道府県名	pref_name	TEXT

■目的

MySQL Workbench を用いてDB設計、SQL化を学びます。

■課題1

公式サイトからMySQL Workbenchをインストールして下さい。

■課題2

MySQL Workbenchを使って課題のテーブルを持つDBを構築して下さい。
構築する際には正規化、外部キー制約など考慮して構築して下さい。
構築し終えたらSQL化しIssueのコメントに記載して下さい。

fr-itaya commented 10 years ago

本課題は13：50開始、工数見積5時間といたします。よろしくお願いします！

fr-itaya commented 10 years ago

14：05　課題1完了しました。昼休み後課題2に着手します。

fr-itaya commented 10 years ago

課題2で構築したDBのSQLです。

-- MySQL Script generated by MySQL Workbench
-- 06/05/14 17:26:06
-- Model: New Model    Version: 1.0
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='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`UserData`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`UserData` (
  `user_id` INT NOT NULL,
  `last_name` TEXT NOT NULL,
  `first_name` TEXT NOT NULL,
  `email` VARCHAR(255) NOT NULL,
  `pref_id` INT NOT NULL,
  `created_at` DATETIME NOT NULL,
  `updated_at` TIMESTAMP NULL,
  PRIMARY KEY (`user_id`))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`PrefData`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`PrefData` (
  `pref_id` INT NOT NULL,
  `pref_name` TEXT NOT NULL,
  `UserData_user_id` INT NOT NULL,
  PRIMARY KEY (`pref_id`, `UserData_user_id`),
  INDEX `fk_PrefData_UserData_idx` (`UserData_user_id` ASC),
  CONSTRAINT `fk_PrefData_UserData`
    FOREIGN KEY (`UserData_user_id`)
    REFERENCES `mydb`.`UserData` (`user_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

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

fr-sato commented 10 years ago

確認しました。以下、一部共有漏れもありますが確認と対応をお願いします。あと、MySQL Workbenchで作成したER図のキャプチャも載せてください。

mydbの部分は課題で作成したDB名を指定する
テーブル名はスネークケース(小文字)で記述する
テーブル名の命名規則は以下の通り
- 中間テーブル以外の場合：複数形users
- 中間テーブルの場合：紐づくテーブル名を連結content_tag(contentsテーブルとtagsテーブルの中間テーブルの場合)
UserData、PrefDataテーブルの主キーにAUTO_INCREMENTを付ける
UserDataテーブルのupdated_atはnull不可にする

fr-itaya commented 10 years ago

修正致しました！

-- MySQL Script generated by MySQL Workbench
-- 06/05/14 17:57:05
-- Model: New Model    Version: 1.0
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='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema mysql_test
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `mysql_test` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `mysql_test` ;

-- -----------------------------------------------------
-- Table `mysql_test`.`usersdata`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mysql_test`.`usersdata` (
  `user_id` INT NOT NULL AUTO_INCREMENT,
  `last_name` TEXT NOT NULL,
  `first_name` TEXT NOT NULL,
  `email` VARCHAR(255) NOT NULL,
  `pref_id` INT NOT NULL,
  `created_at` DATETIME NOT NULL,
  `updated_at` TIMESTAMP NOT NULL,
  PRIMARY KEY (`user_id`))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mysql_test`.`prefdata`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mysql_test`.`prefdata` (
  `pref_id` INT NOT NULL AUTO_INCREMENT,
  `pref_name` TEXT NOT NULL,
  `usersdata_user_id` INT NOT NULL,
  PRIMARY KEY (`pref_id`, `usersdata_user_id`),
  INDEX `fk_prefdata_usersdata_idx` (`usersdata_user_id` ASC),
  CONSTRAINT `fk_prefdata_usersdata`
    FOREIGN KEY (`usersdata_user_id`)
    REFERENCES `mysql_test`.`usersdata` (`user_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

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

dc294ea69229f259ca796ae770e6aa8e

fr-sato commented 10 years ago

命名規則

UserDataをスネークケースで書いた場合、user_dataとなります。単語で区切るのが見やすいかなと思います。で複数形は最後に付くのでuser_datasと最終的にはなります。

あと、だいたいのテーブルがxxxxx_datasとなってしまい冗長なので、usersとかにしておくと見やすくなります。

d84c1c6dd458a024df67bec39ab2955d あと、中間テーブルは上記のように2つ以上のテーブルで1:N、もしくはN:Nの関係にある場合に用いるテーブルの事です。この場合は、お気に入りユーザーが1会員に対して1:Nの関係にあるため中間テーブルを用いています。

正規化

prefdataにuserdata_user_idがありますが、user_idはあくまでusersdataのIDとしか使わないです。今回の場合はusersdataとprefdataの都道府県データを正規化する事で一貫性を保つことが必要になるので再度正規化してみましょう。

fr-itaya commented 10 years ago

再度修正しました！

テーブル命名規則対応
- dataは元々datumの複数形で、datasとするのは違和感があるため、_dataを消してそれぞれusers, prefecturesにしました。
正規化対応
- 参照先/参照元、外部キー制約を持ったカラムの作り方を間違えていた模様。

9f32497856362ebc6d380b9733bde1dd

-- MySQL Script generated by MySQL Workbench
-- 06/06/14 13:55:34
-- Model: New Model    Version: 1.0
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='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema mysql_test
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `mysql_test` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `mysql_test` ;

-- -----------------------------------------------------
-- Table `mysql_test`.`prefectures`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mysql_test`.`prefectures` (
  `pref_id` INT NOT NULL AUTO_INCREMENT,
  `pref_name` TEXT NOT NULL,
  PRIMARY KEY (`pref_id`))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mysql_test`.`users`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mysql_test`.`users` (
  `user_id` INT NOT NULL AUTO_INCREMENT,
  `last_name` TEXT NOT NULL,
  `first_name` TEXT NOT NULL,
  `email` VARCHAR(255) NOT NULL,
  `pref_id` INT NOT NULL,
  `created_at` DATETIME NOT NULL,
  `updated_at` TIMESTAMP NOT NULL,
  PRIMARY KEY (`user_id`),
  INDEX `fk_users_prefectures_idx` (`pref_id` ASC),
  CONSTRAINT `fk_users_prefectures`
    FOREIGN KEY (`pref_id`)
    REFERENCES `mysql_test`.`prefectures` (`pref_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

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

fr-sasaki commented 10 years ago

確認しました。OKです！

fr-itaya / training