teratail
回答率
85.48%
質問するログイン新規登録
トップに関する質問ある要求を満たす、MySQLを用いたデータベース設計の確認

編集履歴

質問編集履歴

1

追加

2015/11/06 14:45

投稿

Kei227
Kei227

スコア44

test CHANGED
File without changes
test CHANGED
@@ -20,8 +20,6 @@
20
20
 
21
21
  - グループ名を変えられる
22
22
 
23
- - 全メッセージの中から文字queryで検索可能
24
-
25
23
  =============
26
24
 
27
25
 
@@ -106,11 +104,13 @@
106
104
 
107
105
 
108
106
 
109
- insert into groups (group_id, group_name) values (1,'school');
107
+ insert into groups (group_id, group_name,id) values (1,'school',1,);
108
+
110
-
109
+ insert into groups (group_id, group_name,id) values (2,'school',3,);
110
+
111
- insert into groups (group_id, group_name) values (2,'bestfried');
111
+ insert into groups (group_id, group_name,id) values (3,'bestfried',2,);
112
-
112
+
113
- insert into groups (group_id, group_name) values (3,'teamA');
113
+ insert into groups (group_id, group_name,id) values (4,'teamA',2,);
114
114
 
115
115
 
116
116
 
@@ -118,42 +118,40 @@
118
118
 
119
119
  school_id int primary key,
120
120
 
121
+ school_message_content text,
122
+
123
+ id int,
124
+
125
+ foreign key (id) references users(id)
126
+
127
+ );
128
+
129
+
130
+
131
+ insert into schools (school_id, school_message_content) values (1, 'aaa');
132
+
133
+ insert into schools (school_id, school_message_content) values (2, 'bbb');
134
+
135
+ insert into schools (school_id, school_message_content) values (3, 'ccc');
136
+
137
+
138
+
139
+ create table messages(
140
+
141
+ message_id int,
142
+
143
+ message_content text,
144
+
121
145
  id int,
122
146
 
123
147
  foreign key (id) references users(id),
124
148
 
149
+ friend_id int,
150
+
151
+ foreign key (friend_id) references friends(friend_id),
152
+
125
153
  group_id int,
126
154
 
127
- foreign key (group_id) references groups (group_id)
128
-
129
- );
130
-
131
-
132
-
133
- insert into schools (school_id, id) values (1, 1);
134
-
135
- insert into schools (school_id, id) values (2, 3);
136
-
137
- insert into schools (school_id, id) values (3, 4);
138
-
139
-
140
-
141
- create table messages(
142
-
143
- message_id int,
144
-
145
- message_content text,
146
-
147
- id int,
148
-
149
- foreign key (id) references users(id),
150
-
151
- friend_id int,
152
-
153
- foreign key (friend_id) references friends(friend_id),
154
-
155
- group_id int,
156
-
157
155
  foreign key (group_id) references groups(group_id)
158
156
 
159
157
  );
@@ -166,6 +164,8 @@
166
164
 
167
165
  insert into messages (message_id, message_content) values (3, 'hoge');
168
166
 
167
+ insert into messages (message_id, message_content) values (4, 'honcon');
168
+
169
169
  ```
170
170
 
171
171
 
@@ -188,28 +188,78 @@
188
188
 
189
189
  ```lang-sql
190
190
 
191
- (1)select messages.message_content from users
192
-
193
- inner join messages on users.id = messages.message_id
194
-
195
- inner join groups on users.id = groups.group_id
196
-
197
- inner join schools on schools.school_id = schools.school_id
198
-
199
- where groups.group_id = 1;
200
-
201
- (2)不明
202
-
203
- (3)不明
204
-
205
- (4)select messages.message_content from messages
191
+ (1)select messages.message_content from messages
192
+
206
-
193
+ inner join users on messages.message_id = users.id
194
+
195
+ inner join friends on messages.message_id = friends.friend_id
196
+
197
+ where users.id = 1 and users.id = 2;
198
+
199
+
200
+
201
+ (2)select users.name from users
202
+
203
+ inner join groups on users.id = groups.id
204
+
205
+ where group_name = 'school';
206
+
207
+
208
+
209
+ (3)select schools.school_message_content from schools;
210
+
211
+
212
+
207
- where messages.message_content = '%hoge%';
213
+ (4)where messages.message_content like '%hoge%';
208
-
214
+
209
- ```
215
+ ```
216
+
217
+
218
+
219
+
220
+
210
-
221
+ 自分で把握している疑問点は一つあります。
222
+
211
-
223
+ 上記のsql文で、グループラインのテーブル(shools)のメッセージ履歴を書いたのですが、
224
+
225
+
226
+
212
-
227
+ ```lang-sql
228
+
213
-
229
+ (3)select schools.school_message_content from schools;
230
+
214
-
231
+ ```
232
+
233
+
234
+
235
+ 下記のように、グループラインごとにテーブルをひとつずつ作成していたら膨大なテーブル数に成ってしまいますよね。
236
+
237
+
238
+
239
+ ```sql
240
+
241
+ create table schools (
242
+
243
+ school_id int primary key,
244
+
245
+ school_message_content text,
246
+
247
+ id int,
248
+
249
+ foreign key (id) references users(id)
250
+
251
+ );
252
+
253
+
254
+
255
+ insert into schools (school_id, school_message_content) values (1, 'aaa');
256
+
257
+ insert into schools (school_id, school_message_content) values (2, 'bbb');
258
+
259
+ insert into schools (school_id, school_message_content) values (3, 'ccc');
260
+
261
+ ```
262
+
263
+
264
+
215
- 足りないテーブルや、書き間違いがあれば訂正していただきたいです。宜しくお願いします。
265
+ グループテーブルの中に、グループラインそれぞれの履歴を格納したいのですが、どのように記入してあげればいいのかが分かりません。