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

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

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

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

データベース設計

データベース設計はデータベースの論理的や物理的な部分を特定する工程です。

Q&A

解決済

2回答

4342閲覧

会員制Webシステムにおける個別メッセージ機能のDB設計

退会済みユーザー

退会済みユーザー

総合スコア0

MySQL

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

データベース設計

データベース設計はデータベースの論理的や物理的な部分を特定する工程です。

0グッド

3クリップ

投稿2017/05/26 03:38

編集2017/05/26 05:51

簡易的な会員制WebシステムをLAMP環境で開発していますが、個別メッセージ機能のDB設計でどうしても解決できない点があり苦慮しています。

長文となりますが、現在の設計と疑問点を記載しますので、アドバイスをいただけませんでしょうか。


【DB設計】

・tbl_board(メッセージボードテーブル)
※やりとりする相手毎に、ユニークな掲示板IDを発行

sql

1CREATE TABLE `tbl_board` ( 2 `id` int(11) NOT NULL COMMENT 'ID', 3 `user_name_1` varchar(100) NOT NULL COMMENT 'ユーザー名1', 4 `user_name_2` varchar(100) NOT NULL COMMENT 'ユーザー名2', 5 `del_flg_1` int(1) NOT NULL DEFAULT '0' COMMENT '削除フラグ(ユーザー1)', 6 `del_flg_2` int(1) NOT NULL DEFAULT '0' COMMENT '削除フラグ(ユーザー2)' 7) 8ALTER TABLE `tbl_board` ADD UNIQUE( `user_name_1`, `user_name_2`); 9 10INSERT INTO `tbl_board` (`id`, `user_name_1`, `user_name_2`, `del_flg_1`, `del_flg_2`) VALUES 11(1, '田中', '山田', 0, 0), 12(2, '田中', '高橋', 0, 0), 13(3, '山田', '高橋', 0, 0); 14(2, 1, 3, 0, 0), 15(3, 2, 3, 0, 0);

|id|user_name_1|user_name_2|del_flg_1|del_flg_2|
|:--:|:--:|:--:|:--:|:--:|:--:|
|1|田中|山田|0|0|
|2|田中|高橋|0|0|
|3|山田|高橋|0|0|

※説明のためにユーザー名を使用していますが、実際はユーザーIDで管理しています。
※「del_flg_1」、「del_flg_2」カラムは、やり取りするどちらか一方がメッセージを削除したい時のために使っています。

・tbl_message(メッセージテーブル)
※メッセージボードIDに紐づけて、送信元やメッセージ本文を保存

sql

1CREATE TABLE `tbl_message_pre` ( 2 `id` int(11) NOT NULL COMMENT 'ID', 3 `board_id` int(11) NOT NULL COMMENT 'メッセージボードID', 4 `from_user_name` varchar(100) NOT NULL COMMENT '送信ユーザ名', 5 `read_flg` int(10) NOT NULL DEFAULT '0' COMMENT '受信者既読フラグ', 6 `contents` text NOT NULL COMMENT 'メッセージ本文', 7 `created_at` datetime NOT NULL COMMENT '送信日時' 8) 9 10INSERT INTO `tbl_message_pre` (`id`, `board_id`, `from_user_name`, `read_flg`, `contents`, `created_at`) VALUES 11(1, 1, '田中', 0, '山田さん、はじめまして。田中と言います。', '2017-05-01 00:00:00'), 12(2, 1, '山田', 0, '田中さん、連絡ありがとうございます。山田と言います。', '2017-05-02 00:00:00'), 13(3, 2, '田中', 0, '高橋さん、はじめまして。田中と言います。', '2017-05-03 00:00:00'), 14(4, 3, '田中', 0, '高橋さん、はじめまして。山田と言います。', '2017-05-04 00:00:00'), 15

|id|board_id|from_user_name|read_flg|contents|created_at|
|:--:|:--:|:--:|:--:|:--|
|1|1|田中|0|山田さん、はじめまして。田中と言います。|2017-05-01 00:00:00|
|2|1|山田|0|田中さん、連絡ありがとうございます。山田と言います。|2017-05-02 00:00:00|
|3|2|田中|0|高橋さん、はじめまして。田中と言います。|2017-05-03 00:00:00|
|4|3|山田|0|高橋さん、はじめまして。山田と言います。|2017-05-04 00:00:00|


【画面とクエリ】

・メッセージ一覧画面
※例:田中さんが、相手別の最新メッセージを一覧表示する場合

sql

1SELECT 2t1.from_user_name, 3t1.read_flg, 4t1.contents, 5t1.created_at 6FROM tbl_board 7LEFT JOIN tbl_message t1 8ON tbl_board.id = t1.board_id 9WHERE EXISTS 10(SELECT * FROM tbl_message t2 WHERE t2.board_id = t1.board_id HAVING MAX(t2.created_at) = t1.created_at) 11AND 12(tbl_board.user_name_1='田中' OR tbl_board.user_name_2='田中')

|from_user_name|read_flg|contents|created_at|
|:--:|:--:|:--|:--:|:--|
|山田|0|田中さん、連絡ありがとうございます。山田と言います。|2017-05-02 00:00:00|
|田中|0|高橋さん、はじめまして。田中と言います。|2017-05-03 00:00:00|


【疑問点】

1.【画面とクエリ】>「メッセージ一覧画面」で発行したSQLの通り、メッセージ一覧画面用のクエリは出来たのですが、これを最新日付順(created_atカラムを降順)で表示したいと思っています。
単純に、SQLの最後に「ORDER BY t1.created_at DESC」を付加し、以下のSQLを作ったのですが、そうすると検索結果が0件となってしまい、ソート出来ませんでした。

sql

1SELECT 2t1.from_user_name, 3t1.read_flg, 4t1.contents, 5t1.created_at 6FROM tbl_board 7LEFT JOIN tbl_message t1 8ON tbl_board.id = t1.board_id 9WHERE EXISTS 10(SELECT * FROM tbl_message t2 WHERE t2.board_id = t1.board_id HAVING MAX(t2.created_at) = t1.created_at) 11AND 12(tbl_board.user_name_1='田中' OR tbl_board.user_name_2='田中') 13ORDER BY t1.created_at DESC

ORDER BYでソートするとなぜ結果が得られないのか、どうしても分かりませんでした。
この原因と解決方法がありましたら、教えていただけませんでしょうか。

2.個別メッセージ機能の開発自体が初めてのため、手探りで設計しており、テーブルの設計がこれで適切なのか不安に感じています。

ご経験者の方で、こういった機能は一般的にどういった作りをする事が多いのか、教えていただければ幸いです。


最終的なクエリ

ご回答くださった方、ありがとうございます。
いただいた情報を元に当方で改めて考え、最終的に以下のクエリに落ち着きました。

yambejp様の回答を元に、一部非相関サブクエリ(IN)を相関サブクエリ(EXISTS)に置き換えてみました。

SQL

1SELECT * FROM tbl_message as tm1 2WHERE tm1.id IN( 3 SELECT max(id) FROM tbl_message AS tm2 4 WHERE EXISTS ( 5 SELECT id FROM tbl_board AS tb 6 WHERE 7 (user_name_1='田中' OR user_name_2='田中') 8 AND 9 tm2.board_id = tb.id 10 ) 11 GROUP BY board_id 12) 13ORDER BY created_at DESC 14

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

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

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

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

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

guest

回答2

0

以前、SNSシステムで個別メッセージ機能もあった案件の改修に関わったことがあります。
個人的な意見なのであくまで参考程度にとどめておいてください。

tbl_boardは不要じゃないかな・・・

田中さんがuser_1で山田さんがuser_2の場合と山田さんがuser_1で田中さんがuser_2の場合で何か違いがあるのであれば別ですが、個別メッセージはあくまで「当人同士のやり取り」で掲示板でいうスレッドが立つのとは意味が違います。
メッセージボードテーブルではひとつひとつのメッセージの削除ではなく、例えば田中さんが山田さんに送ったメッセージ全てを論理削除するということになるのではないでしょうか。

tbl_messageひとつで管理できそう

メッセージIDをキーとしているのであれば全てメッセージテーブルで管理できそうに思います。
管理すべきは「誰が」「誰に」「どんな内容」を送ったかということです。

message_idfrom_userto_usermessageread_flgdelete_flgcreated_at
1山田田中こんにちは田中さん102017-05-01 00:00:00
2田中山田メッセージありがとうございます山田さん002017-05-02 00:00:00
3山田高橋こんにちは高橋さん002017-05-02 00:00:00
・・・・

メッセージはユーザの画面で見るとして、ログインユーザは「to_user」となります。
集約しても「自分が誰に送信したメッセージか」「自分が誰からメッセージを受け取ったか」というのは取得できると思います。
特定ユーザーとのメッセージやり取りを表示する際には若干コツが必要ですが・・・。

山田さんログインの場合に田中さんとのやり取りをとる場合
※未検証 イメージだけ伝わればと思います

SQL

1select 2 * -- 省略します 3from tbl_message_pre 4 where (from_user = '山田' or to_user = '山田') 5 and (from_user = '田中' or to_user = '田中') 6 order by created_at desc 7

投稿2017/05/26 04:29

編集2017/05/26 04:32
m.ts10806

総合スコア80765

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

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

退会済みユーザー

退会済みユーザー

2017/05/26 06:00

ご回答ありがとうございます。 記載いただいたような、メッセージテーブル一つで管理する方法については、以下情報を参考に当初設計してみました。 会員同士のメッセージやりとりのDB設計方法について教えて下さい。 https://teratail.com/questions/39925 ただ、やりとりする相手別にグルーピング(from_userまたはto_userが自分)し、その中で最新のレコードを取得する、という方法が分からなかったため断念しました。 また、「この相手とのやりとりを全て削除したい」となった時、自身のメッセージボックスからだけ削除し、相手のメッセージボックスには残したままにしておきたい、というケースがあると思いました。 そのため、メッセージテーブルに論理削除フラグを設けるのではなく、メッセージボードテーブルに論理削除フラグを二つ設ける設計としました(del_flg_1、del_flg_2)。
m.ts10806

2017/05/26 06:53

>ただ、やりとりする相手別にグルーピング(from_userまたはto_userが自分)し、その中で最新のレコードを取得する、という方法が分からなかったため断念しました。 SQL1発で難しければSQLで「ログイン者とやり取りしている相手の一覧を取得」したあとにプログラム側でループし、SQL「それぞれの相手とのやり取りメッセージの最新を取得」するのもありと思います。 僕はよくSQL一発だと複雑になりそうな場合はその手でやってます。あまり複雑すぎるSQLはそれはそれで動作に影響があることもありますので。 >また、「この相手とのやりとりを全て削除したい」となった時、自身のメッセージボックスからだけ削除し、相手のメッセージボックスには残したままにしておきたい、というケースがあると思いました。 確かにその仕様だとdeleteが2つ必要ですね。んー、確かに2つのテーブルでした方が分かりやすいかな・・・ 既に回答となるようなSQLも出ていることですしね。
guest

0

ベストアンサー

ちょっと精査してみないとなんともいえませんが
とりあえず動かすだけなら、サブクエリに突っ込んだ上でorder byすればよいでしょう

SELECT * FROM ( SELECT t1.from_user_name, t1.read_flg, t1.contents, t1.created_at FROM tbl_board LEFT JOIN tbl_message t1 ON tbl_board.id = t1.board_id WHERE EXISTS (SELECT * FROM tbl_message t2 WHERE t2.board_id = t1.board_id HAVING MAX(t2.created_at) = t1.created_at) AND (tbl_board.user_name_1='田中' OR tbl_board.user_name_2='田中') ) AS sub ORDER BY created_at DESC;

検証

ざっと検証しました
まず、田中を含むboard_idを取るのはこう

SQL

1SELECT id FROM `tbl_board` WHERE user_name_1='田中' OR user_name_2='田中';

該当するboard_id毎のmaxのメッセージidをとるのがこう

SQL

1SELECT board_id,max(id) FROM `tbl_message_pre` 2WHERE board_id IN (SELECT id FROM `tbl_board` WHERE user_name_1='田中' OR user_name_2='田中') 3GROUP BY board_id;

※ただし、実際のほしいのはmax(id)だけ

maxのメッセージidから全データを抜き出してソートするのがこう

SQL

1SELECT * FROM `tbl_message_pre` 2WHERE id in( 3SELECT max(id) FROM `tbl_message_pre` 4WHERE board_id IN (SELECT id FROM `tbl_board` WHERE user_name_1='田中' OR user_name_2='田中') 5GROUP BY board_id 6) 7ORDER BY created_at DESC; 8

投稿2017/05/26 04:01

編集2017/05/26 04:21
yambejp

総合スコア114572

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

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

退会済みユーザー

退会済みユーザー

2017/05/26 04:17

ご回答ありがとうございます。 いただいたSQLで、希望のデータを取得出来ました。 ただ、もしサブクエリを追加せずに対応出来れば。。と思っており、もし何かヒントがあれば教えていただければ幸いです。
yambejp

2017/05/26 04:19

検証結果を追記しときました
退会済みユーザー

退会済みユーザー

2017/05/26 05:48

ご回答ありがとうございます。 検証の流れを丁寧に書いていただき、順を追って理解する事が出来ました。 最終的にいただいたクエリをベースに、開発を進めてみようと思います。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

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

ただいまの回答率
85.50%

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

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

質問する

関連した質問