質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

Q&A

解決済

4回答

11035閲覧

2つのテーブルの主キーを結合条件にしているが片方だけでも取れるようにしたい

msx2

総合スコア174

MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

0グッド

0クリップ

投稿2016/08/23 06:25

商品情報のデータベースを作成しています。

商品には色やサイズのバリエーションがあるのでテーブルを「商品情報」「バリエーション情報」にわけました。
テーブル項目は以下とします。

■商品情報(item)
id
item_name
item_price

■バリエーション情報(variation)
id
item_id
color
size

商品情報には画像を登録したいので「画像」テーブルを追加します。

■画像(image)
id
item_id
variation_id
image_name
comment

商品はバリエーションがなくても1つのバリエーションの商品として登録されるのでimage_idとvariation_idで結合しています。

このテーブル構成で商品共通の画像も登録したいという要望があり、画像テーブルをどう結合するのが適当なのかを質問したいです。

とりあえず共通画像は画像テーブルのvariation_idを-1で登録するルールとし、今のところ問題なく動いているように思えます。

ただ、この様なデータの持ち方で今後問題が発生しないのか経験不足のため自信がありません。

もしもプログラムで困ることがあるとか、もっといいデータの持ち方がある等ありましたらアドバイスいただけると幸いです。

よろしくお願いします。

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答4

0

ベストアンサー

この様なデータの持ち方で今後問題が発生しないのか

私は、'画像'と'商品共通の画像'を同じテーブルで表現する方法には問題がある、と考えます。

なぜなら、この方法では'バリエーション'と'画像'の間に外部キー制約を使用できないからです。

外部キー制約が使用できないと、整合性のないデータがDBに登録されてしまうリスクが生じます。
(例えば、プログラムのバグにより、存在しない'variation_id'を持つ画像が登録できてしまう、など)

もっといいデータの持ち方がある等

私は、'画像'と'商品共通の画像'は、別々のテーブルで表現する方が良い、と考えます。

具体的な方法は、
「'商品'は、必ず1つの'商品共通の画像'を持つ」
か否かによっても変わります。

'商品共通の画像'を持たない'商品'があり得るのであれば、
以下の図のように'商品共通の画像'('common_image' テーブル)を直接、'商品'テーブルに紐づけてやるのが良いでしょう。
(両テーブルの'id'カラムに外部キー制約を適用)
イメージ説明

'商品'は必ず1つの'商品共通の画像'を持つのであれば、
いっそのこと'商品'と'商品共通の画像'を1つのテーブルに統合し、各カラムに NOT NULL 制約を課してしまう、という手もあります。

これなら、整合性の無いデータが登録されたり、データが欠落したりするのをDBのレイヤーで防ぐことができますので。


ちなみに、'バリエーション'テーブルの'id'カラムがプライマリキーであれば、'画像'テーブルの'id'カラムと'item_id'カラムは不要です。

'variation_id'カラムさえあれば各レコードの一意制を確保でき、'バリエーション'テーブルと1対1の紐付けも可能だからです。

(さらに言うと、'バリエーション'テーブルの'id'カラムがプライマリキーでなくとも、同様の理由により'画像'テーブルの'id'カラムは不要です。)

追記

共通画像とバリエーション画像のどちらも複数の画像を登録します

であれば、私の回答の
「'商品共通の画像'を持たない'商品'があり得る」
に若干の修正を加えるのが良さそうです。

具体的には、'商品共通の画像'テーブルに'item_id'カラムを追加し、
外部キー制約を

item.id <- common_image.item_id

という紐付けに変更します。

これなら、'商品共通の画像'も1つの商品につき複数、登録できます。

jawaさんの方法(中略)では不十分でしょうか?

「可能か不可能か?」
で言えば可能ではありますが、私はお勧めしません。

この方法は
「'商品共通の画像'は'商品'に属する('バリエーション'に属するわけではない)」
という、このデータモデルが持つ本来の関係性が崩れているからです。

関係性が崩れたテーブル設計は、予期しない別の問題を生む可能性があります。

現状は

バリエーションを結合させるときはvariation_idから0を除外する条件を付けるだけでいける

とお考えのようですが、今後、例えば
「商品ごとのバリエーションの数を表示したい」
となった場合、全てのCOUNT(*)から -1 した値を表示しなければならなくなるなど、他のあらゆる場所で
「variation_id = 0 は特別」
であることを意識しなければならなくなります。

開発者が(今後もずっと)msx2 様お一人なら問題が生じる可能性は低いかもしれませんが、
複数人で開発していたり、担当が交代する可能性があるのであれば、この「意識」を全員が維持しつづけるのは意外に難しいものです。

投稿2016/08/24 03:19

編集2016/08/24 06:08
KiyoshiMotoki

総合スコア4791

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

msx2

2016/08/24 04:12

詳しい回答ありがとうございます。 外部キー制約などを使用して整合性の確保をデータベースで行うという考え方は今まであまり意識しておらず大変参考になりました。 ご回答の内容をみていると画像の持ち方など設計についてはもう一度よく考え直してみる必要がありそうです。(共通画像を別テーブルにするなんて考えたこともなかったので…) 本案件の要件として画像は複数登録したいとあります。共通画像とバリエーション画像のどちらも複数の画像を登録します。先にご回答いただいたjawaさんの方法は私のイメージに近く、バリエーションと画像テーブルの間に外部キー制約を設けられると思うのですがこれでは不十分でしょうか?
KiyoshiMotoki

2016/08/24 06:08

msx2 様 返信ありがとうございます。 回答欄に追記させていただきました。 確認願います。
msx2

2016/08/24 06:35

詳しく追記・ご説明していただき感謝感激です! テーブルを増やすのはシステムが複雑になりそうで敬遠しがちでしたが、それよりもデータの関係性や意味合いを崩さないことの方が重要ということですね。 データの論理性が保たれていれば誰が考えても同じ結果に行き着きますが、特別なルールはそれを知っている人しかわからない。現状は私しか開発する人間はいませんが将来どうなるかわかりませんし、3日後の自分は他人といっても差し支えないくらいに忘れてしまう人間なので極力イレギュラーな仕様は作らないのが正解なのだろうと思いました。 大変勉強になりました、本当にありがとうございます。
KiyoshiMotoki

2016/08/24 07:02

msx2 様 > データの関係性や意味合いを崩さないことの方が重要 > データの論理性が保たれていれば誰が考えても同じ結果に行き着きますが、特別なルールはそれを知っている人しかわからない。 まさに仰るとおりです。
guest

0

今の方針でも問題ないように思います。

おそらくvalidation_id=-1という架空のバリエーションを使用することが気持ち悪いと感じているのではないでしょうか?

自分が設計するなら、例えばvalidation_id=0は共通設定用バリエーションとして使うものとし、全ての商品に対し必ず1件登録するバリエーションとしてしまいます。

そのうえで、商品⇒バリエーション(共通含む)⇒画像の親子関係を構成すると思います。

■商品情報(item)
item_id
item_name
item_price

■バリエーション情報(variation)
item_id
valietion_id
color
size

■画像(image)
item_id
variation_id
image_id
image_name
comment

■データ例

[item] item_id, item_name, item_price -------------------------------------- S01,商品1,100 S02,商品2,200 -------------------------------------- [variation] item_id, valietion_id, color, size -------------------------------------- S01, 0, NULL , NULL S01, 1, RED , 50 S01, 2, BLUE , 50 S02, 0, NULL , NULL S02, 1, BLACK, 80 -------------------------------------- [image] item_id, valietion_id, image_id, image_name, comment -------------------------------------- S01, 0, S01_0 , S01_PACKAGE , CMT1 S01, 1, S01_1_1, S01_RED_FRONT , CMT2 S01, 1, S01_1_2, S01_RED_SIDE , CMT3 S01, 2, S01_2_1, S01_BLUE_FRONT, CMT4 S01, 2, S01_2_2, S01_BLUE_SIDE , CMT5 S02, 0, S02_0 , S02_PACKAGE , CMT6 S02, 1, S02_1 , S02_BLACK , CMT7 --------------------------------------

投稿2016/08/23 11:50

jawa

総合スコア3013

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

msx2

2016/08/24 01:03

ありがとうございます! -1という架空のバリエーションを使うのが少し心配だったのですが問題なさそうとのことで少し安心しました。 共通のバリエーションを登録する方法もいいですね。これなら画像を持ってくるSQLはそのまま使えますし、バリエーションを結合させるときはvariation_idから0を除外する条件を付けるだけでいけるので修正がすくなくて済みそうです。 架空のバリエーションIDを使う方法と共通のバリエーションを登録する方法のどちらがいいのか…ちょっと悩みますね。
guest

0

多対多のリレーションについてリンク先のページはとても参考になりました。
おすすめ本はポチってしまいました。

お役に立ててよかったです!

例えば3色バリエーションの商品があるとして

赤、緑、黄色みたいなイメージですよね。
複数の商品で共通のバリエーションを使わないなら、variationにitem_idをもたせた方が良さそうですね。
共通のバリエーションを使いたいなら画像と同じように関連テーブルを用意した方が良いでしょう。

例えば
color -> red
size -> small
みたいなレコードを商品Aだけしか使わないなら、variationにitem_idを追加した方が良いです。
商品A、B、Cでも使うなら関連テーブルを用意した方が良いです。

投稿2016/08/23 09:27

maromaro0013

総合スコア19

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

maromaro0013

2016/08/23 09:29

間違ってコメントではなく新たに回答してしまいました;;
msx2

2016/08/24 00:46

ご回答ありがとうございます! variationはマスタ化して使い回すことはないのでvariationにitem_idを持たせることにします。imageは複数のitemから使われる可能性があるので関連テーブルですね。
guest

0

itemがimage_id、variation_idを持つようにしたほうが自然なように思います。
これによって複数の商品が同じ画像を参照することができます。

■商品情報(item)
id
item_name
item_price
variation_id
image_id

■バリエーション情報(variation)
id
color
size

■画像(image)
id
image_name
comment

投稿2016/08/23 07:06

maromaro0013

総合スコア19

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

msx2

2016/08/23 07:16

すいません、質問させてください。 itemがvariation_idとimage_idを持った場合、画像を複数持たせるにはどうすればいいでしょうか?
maromaro0013

2016/08/23 07:37 編集

「itemが複数のimageを持つ場合がある」 「imageが複数のitemを持つ場合がある」 この場合は多対多のリレーションになるので、関連テーブルを使うのが良いとされています。 http://d.hatena.ne.jp/asakichy/20120730/1343597050 以下のようなテーブルを用意すれば、itemとimageのどちらが複数になっても大丈夫になります。 ■商品情報と画像の関連テーブル(item_images) id item_id image_id 例えば商品情報が複数の画像を持っている場合には SELECT image_id FROM item_images WHERE item_id=xxx; 画像が複数の商品から参照されている場合には SELECT item_id FROM item_images WHERE image_id=xxx; このようなクエリで取得できるようになります。 ※この本おすすめです https://www.oreilly.co.jp/books/9784873115894/
msx2

2016/08/23 08:28

ご返信ありがとうございます。 多対多のリレーションについてリンク先のページはとても参考になりました。 おすすめ本はポチってしまいました。 itemとimageは多対多なので関連テーブルのやり方がよさそうですね。 itemとvariationは1対多の関係となりますがitemにvariation_idを持たせる方がいいでしょうか? 今回の質問として、例えば3色バリエーションの商品があるとして、外箱は同じだから共通の画像として持たせたい。itemテーブルにvariation_idを持っているので3色あればitemに3レコードデータができ、それぞれが同じ共通画像を持つことになります。するとこの共通画像を変更する場合は3レコードすべてに変更をかける必要がありますが、バグなどにより共通の画像が異なってしまうケースも考えられます。 プログラム側が少し複雑になってしまいそうですが、variation_idをitemに持たせるメリットなどあればお願い致します。
jawa

2016/08/23 11:52 編集

商品情報テーブルにvariation_idとimage_idを持たせた場合、同じitem_idの商品を複数レコード持つことになると思います。 本来商品情報テーブルで一元管理しようとしていたであろうitem_name やitem_priceの項目もバリエーションや画像違いの各レコードでそれぞれに持つことになります。 例えば単価が改定された場合、同じitem_idのレコードを一括更新する必要がでてくるのではないかと思いますが、大丈夫でしょうか? (見当違いなことを言っていたらすみません)
msx2

2016/08/24 00:43

そうですよね、よくよく考えるとitem_nameやitem_priceも複数レコードで持つことになってしまうのでこれはよくなさそうです。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問