戻る

DB設計（SQL暫定）

-- 伝票システムデータベース
CREATE DATABASE denpyo_db DEFAAULT CHARACTER SET utf8; 

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

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

-- 商品テーブル
CREATE TABLE dtb_syohin (
id INT PRIMARY KEY AUTO_INCREMENT,
syohin VARCHAR(16) NOT NULL,
tanka INT,
date TIMESTAMP NOT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8;

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

-- 明細テーブル
CREATE TABLE dtb_meisai (
id INT PRIMARY KEY AUTO_INCREMENT,
suryo INT NOT NULL,
denpyo_id INT NOT NULL,
syohin_id INT NOT NULL,
date TIMESTAMP 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 dtb_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