戻る

DB設計とER図

ER図

SQL文

-- 顧客マスター
CREATE TABLE mtb_kokyaku (
id INT PRIMARY KEY AUTO_INCREMENT,
kokyaku VARCHAR(10) UNIQUE NOT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8;

-- 契約マスター
CREATE TABLE mtb_keiyaku (
id INT PRIMARY KEY AUTO_INCREMENT,
tanka INT NOT NULL,
start DATE NOT NULL,
end DATE NOT NULL,
kokyaku_id INT UNIQUE NOT NULL,
syohin_id INT UNIQUE NOT NULL
) ENGINE=INNODB;

-- 商品マスター
CREATE TABLE mtb_syohin (
id INT PRIMARY KEY AUTO_INCREMENT,
syohin VARCHAR(10) UNIQUE NOT NULL,
tanka INT NOT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8;

-- 伝票テーブル
CREATE TABLE dtb_denpyo (
id INT PRIMARY KEY AUTO_INCREMENT,
denpyo_no VARCHAR(10) UNIQUE NOT NULL,
hiduke DATE NOT NULL,
gokei INT NOT NULL,
kokyaku_id INT UNIQUE NOT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8;

-- 明細テーブル
CREATE TABLE dtb_meisai (
id INT PRIMARY KEY AUTO_INCREMENT,
kazu INT NOT NULL,
tanka INT NOT NULL,
kingaku INT NOT NULL,
denpyo_id INT UNIQUE NOT NULL,
syohin_id INT UNIQUE NOT NULL
) ENGINE=INNODB;

-- 外部キー制約
ALTER TABLE mtb_keiyaku ADD FOREIGN KEY (kokyaku_id) REFERENCES mtb_kokyaku(id);
ALTER TABLE mtb_keiyaku ADD FOREIGN KEY (syohin_id) REFERENCES mtb_syohin(id);
ALTER TABLE dtb_denpyo ADD FOREIGN KEY (kokyaku_id) REFERENCES mtb_kokyaku(id);
ALTER TABLE dtb_meisai ADD FOREIGN KEY (denpyo_id) REFERENCES dtb_denpyo(id);
ALTER TABLE dtb_meisai ADD FOREIGN KEY (syohin_id) REFERENCES mtb_syohin(id);

Copyright © 2012 YOHSUKE NAKANO