gistfile1.txt

create table member (
    id INT NOT NULL AUTO_INCREMENT COMMENT 'ID',
    name VARCHAR(100) DEFAULT '' NOT NULL COMMENT '氏名',
    created_date DATETIME NOT NULL COMMENT '作成日時',
    updated_date DATETIME NOT NULL COMMENT '更新日時',
    deleted_date DATETIME COMMENT '削除日時',
    version INT DEFAULT 0 NOT NULL COMMENT 'オプティミスティックロック用',
    CONSTRAINT member_pk PRIMARY KEY(id)
) COMMENT = 'メンバー' DEFAULT CHARSET=UTF8;

create table depertment (
    id INT NOT NULL AUTO_INCREMENT COMMENT 'ID',
    name VARCHAR(100) DEFAULT '' NOT NULL COMMENT '部署名',
    created_date DATETIME NOT NULL COMMENT '作成日時',
    updated_date DATETIME NOT NULL COMMENT '更新日時',
    deleted_date DATETIME COMMENT '削除日時',
    version INT DEFAULT 0 NOT NULL COMMENT 'オプティミスティックロック用',
    CONSTRAINT depertment_pk PRIMARY KEY(id)
) COMMENT = '部署' DEFAULT CHARSET=UTF8;

-- 多対多用リレーションテーブル
create table relation_depertment_member (
    id INT NOT NULL AUTO_INCREMENT COMMENT 'ID',
    depertment_id INT COMMENT '部署ID',
    member_id INT COMMENT 'メンバーID',
    version INT DEFAULT 0 NOT NULL COMMENT 'オプティミスティックロック用',
    CONSTRAINT r_news_product_pk PRIMARY KEY(id)
) COMMENT = '部署-メンバー マッピング' DEFAULT CHARSET=UTF8;

-- relation_depertment_member.depertment_id に FK を張る
ALTER TABLE relation_depertment_member
  ADD CONSTRAINT fk_relation_depertment_member_depertment_id
  FOREIGN KEY (depertment_id) REFERENCES depertment (ID);

-- relation_depertment_member.member_id に FK を張る
ALTER TABLE relation_depertment_member
  ADD CONSTRAINT fk_relation_depertment_member_member_id
  FOREIGN KEY (member_id) REFERENCES member (ID);