mySQL5に関する質問です。

ショップマスタと商品マスタというテーブルがあったとします。
補足に添付したようなテーブル構造だと、商品マスタの削除・挿入を繰り返すうちに6桁を超える可能性があります。
なぜ6桁かというと商品IDをバーコード化するので、6桁以上持つことができません。
PRIMARY KEY を item_id と shop_id にし、 item_id を shop_id ごとに自動連番する方法があれば桁数を押さえられるでしょうが、そのような方法はありますか?
また、マニュアルで item_id を入力する方法は避けたいです。
なにか解決方法はありますか?

phpは5.5です。
よろしくお願いします。

回答の条件
  • 1人1回まで
  • 13歳以上
  • 登録:2014/11/12 12:32:38
  • 終了:2014/11/17 14:47:39
id:wsapp

質問者から

wsapp2014/11/13 05:04:36

ショップマスタ

CREATE TABLE shop_master (
	shop_id MEDIUMINT NOT NULL AUTO_INCREMENT,
	user_id MEDIUMINT NOT NULL,
	shop_name VARCHAR(256) NOT NULL,
	PRIMARY KEY (shop_id),
	INDEX idx_userid(user_id),
	INDEX idx_name(shop_name)
);

商品マスタ

CREATE TABLE item_master (
	item_id INT(6) NOT NULL AUTO_INCREMENT,
	shop_id MEDIUMINT NOT NULL,
	item_name VARCHAR(256) NOT NULL,
	item_price INT(13) NOT NULL,
	PRIMARY KEY (item_id),
	INDEX idx_shopid(shop_id)
);

ベストアンサー

id:gizmo5 No.2

gizmo5回答回数484ベストアンサー獲得回数1382014/11/13 02:03:13

ポイント50pt

採番用のテーブルを持つのが良いと思います。

CREATE TABLE numbering_table {
	item_id INT(6) NOT NULL,
	shop_id MEDIUMINT NOT NULL
};

shop_id が存在しなければ、新たにレコードを挿入して item_id は 1 。
shop_id が存在している場合には、item_id をインクリメントして、その item_id を使って item_master のレコードを作成します。
ここはプログラムで制御することになります。

バーコードとして item_id を使うということですから、取り扱いをしなくなった商品でも item_master からレコードを削除するのは運用上の問題が出ると思います。
取り扱わないことを決めてマスタをメンテナンスするタイミングと、実際に店舗から商品を引き上げるタイミングにはタイムラグがあるはずですので。

item_master に、取り扱い中という意味のフラグを設けるか、取扱期間という意味の開始日付・終了日付を持たせる方が良いと思います。

他1件のコメントを見る
id:gizmo5

確認ですが採番用テーブルを用いるのは、item_masterにshop_idを持たせないということですよね?

いえ、店ごとに item_id を振るわけですから番号が重複しますので shop_id は必要です。
採番テーブルには、店ごとに次に附番するitem_idが分かる情報だけを格納します。
item_master から shop_id で select して max(item_id) でも分かる情報ですが、別テーブルにした方が管理しやすいと思います。

2014/11/17 08:47:25
id:wsapp

なるほど、ありがとうございましたm(_ _)m

2014/11/17 14:47:18

その他の回答(1件)

id:wsapp

質問者から

wsapp2014/11/12 12:44:08

質問文を編集しました。詳細はこちら

id:pogpi No.1

pogpi回答回数376ベストアンサー獲得回数502014/11/12 14:03:47

ポイント50pt

削除した時に、空きIDができるので、新たに登録するときにその空きIDで登録するようにすればできそうですね。バーコード化するなら、商品マスタのプライマリーキーはそのままでいいと思いますよ。

他3件のコメントを見る
id:pogpi

商品マスタのshop_idは別テーブルにした方がスマートかも知れません。直す必要が出てくる範囲にもよりますね。

2014/11/12 16:17:39
id:wsapp

直す必要も考えないといけないでした。
ありがとうございますm(_ _)m

2014/11/12 16:29:55
id:gizmo5 No.2

gizmo5回答回数484ベストアンサー獲得回数1382014/11/13 02:03:13ここでベストアンサー

ポイント50pt

採番用のテーブルを持つのが良いと思います。

CREATE TABLE numbering_table {
	item_id INT(6) NOT NULL,
	shop_id MEDIUMINT NOT NULL
};

shop_id が存在しなければ、新たにレコードを挿入して item_id は 1 。
shop_id が存在している場合には、item_id をインクリメントして、その item_id を使って item_master のレコードを作成します。
ここはプログラムで制御することになります。

バーコードとして item_id を使うということですから、取り扱いをしなくなった商品でも item_master からレコードを削除するのは運用上の問題が出ると思います。
取り扱わないことを決めてマスタをメンテナンスするタイミングと、実際に店舗から商品を引き上げるタイミングにはタイムラグがあるはずですので。

item_master に、取り扱い中という意味のフラグを設けるか、取扱期間という意味の開始日付・終了日付を持たせる方が良いと思います。

他1件のコメントを見る
id:gizmo5

確認ですが採番用テーブルを用いるのは、item_masterにshop_idを持たせないということですよね?

いえ、店ごとに item_id を振るわけですから番号が重複しますので shop_id は必要です。
採番テーブルには、店ごとに次に附番するitem_idが分かる情報だけを格納します。
item_master から shop_id で select して max(item_id) でも分かる情報ですが、別テーブルにした方が管理しやすいと思います。

2014/11/17 08:47:25
id:wsapp

なるほど、ありがとうございましたm(_ _)m

2014/11/17 14:47:18

コメントはまだありません

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません