ある要求を満たす、MySQLを用いたデータベース設計の確認

Question

以下の要求を満たすテーブルを作成し、

• User登録に必要な情報は名前、メアド、パスワード
• 友だち追加は一方的にできるが、相手は追加又はブロックできる
• ブロックしたユーザー一覧が見れる（ブロックを外せる）
• 友達一覧が見れる
• 個人チャットでメッセージが遅れる
• グループ作成できる
• グループでもメッセージを送れる
• グループにはユーザーを追加、削除できる
• グループ名を変えられる

=============

create table users (
  id int primary key auto_increment,
  name varchar(50),
  mail varchar(50),
  password varchar(50)
);

insert into users (id, name) values (1, 'keita');
insert into users (id, name) values (2, 'takeru');
insert into users (id, name) values (3, 'taro');
insert into users (id, name) values (4, 'ken');

create table blockusers(
  blockuser_id int,
  blockuser_name varchar(50),
  id int,
  foreign key (id) references users (id)
);

insert into blockusers (blockuser_id, blockuser_name) values (1, 'yui');
insert into blockusers (blockuser_id, blockuser_name) values (2, 'ayu');

create table friends(
  friend_id int primary key,
  friend_name varchar(50),
  id int,
  foreign key (id) references users(id)
);

insert into friends (friend_id, friend_name) values (1, 'mim');
insert into friends (friend_id, friend_name) values (2, 'erika');

create table groups(
  group_id int primary key,
  group_name varchar(50),
  id int,
  foreign key (id) references users(id)
);

insert into groups (group_id, group_name,id) values (1,'school',1,);
insert into groups (group_id, group_name,id) values (2,'school',3,);
insert into groups (group_id, group_name,id) values (3,'bestfried',2,);
insert into groups (group_id, group_name,id) values (4,'teamA',2,);

create table schools (
  school_id int primary key,
  school_message_content text,
  id int,
  foreign key (id) references users(id)
);

insert into schools (school_id, school_message_content) values (1, 'aaa');
insert into schools (school_id, school_message_content) values (2, 'bbb');
insert into schools (school_id, school_message_content) values (3, 'ccc');

create table messages(
  message_id int,
  message_content text,
  id int,
  foreign key (id) references users(id),
  friend_id int,
  foreign key (friend_id) references friends(friend_id),
  group_id int,
  foreign key (group_id) references groups(group_id)
);

insert into messages (message_id, message_content) values (1, 'hoge?');
insert into messages (message_id, message_content) values (2, 'hoge!!!');
insert into messages (message_id, message_content) values (3, 'hoge');
insert into messages (message_id, message_content) values (4, 'honcon');

以下の命令を実行するMysql文を出力しました。

(1)ユーザ(id=1)とユーザ(id=2)の会話の履歴
(2)グループ(id=1)に所属しているメンバー一覧
(3)グループ(id=1)の会話履歴
(4)hogeという文字を含むメッセージ一覧

(1)select messages.message_content from messages
inner join users on messages.message_id = users.id
inner join friends on messages.message_id = friends.friend_id
where users.id = 1 and users.id = 2;

(2)select users.name from users
inner join groups on users.id = groups.id
where group_name = 'school';

(3)select schools.school_message_content from schools;

(4)where messages.message_content like '%hoge%';

自分で把握している疑問点は一つあります。
上記のsql文で、グループラインのテーブル(shools)のメッセージ履歴を書いたのですが、

(3)select schools.school_message_content from schools;

下記のように、グループラインごとにテーブルをひとつずつ作成していたら膨大なテーブル数に成ってしまいますよね。

create table schools (
  school_id int primary key,
  school_message_content text,
  id int,
  foreign key (id) references users(id)
);

insert into schools (school_id, school_message_content) values (1, 'aaa');
insert into schools (school_id, school_message_content) values (2, 'bbb');
insert into schools (school_id, school_message_content) values (3, 'ccc');

グループテーブルの中に、グループラインそれぞれの履歴を格納したいのですが、どのように記入してあげればいいのかが分かりません。

Comments

[KiyoshiMotoki]

分からない点・問題点をもっと細かい粒度に落とし込んでから質問された方が、回答がつきやすくなると思います。
答えるべき内容が多すぎると回答者は負担に感じますし、なによりこのご質問からは、ちょっと「丸投げ感」を感じますよw

[Kei227]

確かに丸投げのようなもので、回答者様にご負担かける書き方でした、、。

訂正いたしました。ご教授ありがとうございます！

Answer 1

情報の追記、ありがとうございます。

"グループライン"とは、グループ内のメッセージのことでしょうか？
とりあえず、↑であると仮定して回答させていただきます。

グループテーブルの中に、グループラインそれぞれの履歴を格納したいのですが、

これはまずい設計です。

groupsテーブルはあくまで1つのレコードが1つのグループを表現するテーブルであるべきで、
そこに、グループラインのような1グループに対して複数生成されるデータを混ぜ込むべきではありません。

以下のように、グループラインを格納するためのテーブルを別途、定義してやる方が良いです。

CREATE TABLE group_lines (
    group_line_id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
    id INTEGER NOT NULL,
    group_id INTEGER NOT NULL,
    message_content TEXT NOT NULL,
    FOREIGN KEY (id) REFERENCES users (id),
    FOREIGN KEY (group_id) REFERENCES groups (group_id)
);

group_idカラムをgroups.group_idの外部キーとして定義してやれば、
グループごとにテーブルを用意する必要はなくなります。

"スクール"のグループラインを取得する

SELECT group_lines.* 
FROM group_lines 
WHERE group_id = 1;

groupsテーブルはあくまで1つのレコードが1つのグループを表現するテーブルであるべき

に関連して付記させていただくと、idカラムもgroupsテーブルに定義するべきではありません。

1人のユーザーは1つのグループのみに所属するのであれば、usersテーブルにgroup_idカラムを持たせる。
1人のユーザーが複数のグループに所属できるのであれば、user_group_relationsテーブルを別途、定義してやる。
この方がよいです。

user_group_relationsテーブルをどのように設計すればよいかは、
「リレーションシップ 多対多」
とか
「relationship many to many」
とかで検索して、ご自分で調べてみて下さい。

Comments

[Orlofsky]

KiyoshiMotokiさんに賛成。
>グループテーブルの中に、グループラインそれぞれの履歴を格納したいのですが、
>>これはまずい設計です。
ひとつのテーブルには同じ性質のデータを置きます。昔、マスターと履歴を一つのテーブルにまとめる主義の人が設計していた人がいたけど、マスター情報と履歴情報は必ず別テーブルに分けます。マスターテーブルはprimary keyでunique scanでアクセスできるように設計するべきです。頻繁にselectするデータがrange scanしているとデータが増えるとどんどんパフォーマンスが落ちますし、SQLが複雑になります。