teratail
回答率
85.47%
質問するログイン新規登録
トップに関する質問DB設計に関するして中間テーブルを親にする設計についてご教授ください。

編集履歴

質問編集履歴

4

ご教授ください。

2019/11/22 05:55

投稿

m3304017499
m3304017499

スコア5

test CHANGED
@@ -1 +1 @@
1
- DB設計に関するして中間テーブルを親にする設計について
1
+ DB設計に関するして中間テーブルを親にする設計についてご教授ください。
test CHANGED
File without changes

3

あ

2019/11/22 05:55

投稿

m3304017499
m3304017499

スコア5

test CHANGED
File without changes
test CHANGED
@@ -389,3 +389,9 @@
389
389
 
390
390
 
391
391
  ### ご教授いただきたいです。
392
+
393
+
394
+
395
+
396
+
397
+ ### SQL更新いたしました。

2

a

2019/11/22 03:31

投稿

m3304017499
m3304017499

スコア5

test CHANGED
File without changes
test CHANGED
@@ -138,7 +138,7 @@
138
138
 
139
139
  CREATE TABLE IF NOT EXISTS `mydb`.`company` (
140
140
 
141
- `id` NOT NULL,
141
+ `id` int NOT NULL,
142
142
 
143
143
  `name` VARCHAR(45) NULL,
144
144
 
@@ -156,11 +156,11 @@
156
156
 
157
157
  CREATE TABLE IF NOT EXISTS `mydb`.`year` (
158
158
 
159
- `id` NOT NULL,
159
+ `id` int NOT NULL,
160
160
 
161
161
  `name` VARCHAR(45) NULL,
162
162
 
163
- `company_id` NOT NULL,
163
+ `company_id` int NOT NULL,
164
164
 
165
165
  PRIMARY KEY (`id`),
166
166
 
@@ -168,9 +168,9 @@
168
168
 
169
169
  CONSTRAINT `fk_year_company1`
170
170
 
171
- FOREIGN KEY (`company_id`)
171
+ FOREIGN KEY (`company_id`)
172
-
172
+
173
- REFERENCES `mydb`.`company` (`id`)
173
+ REFERENCES `mydb`.`company` (`id`)
174
174
 
175
175
  ON DELETE NO ACTION
176
176
 
@@ -188,9 +188,9 @@
188
188
 
189
189
  CREATE TABLE IF NOT EXISTS `mydb`.`department` (
190
190
 
191
- `id` NOT NULL,
191
+ `id` int NOT NULL,
192
-
192
+
193
- `year_id` NOT NULL,
193
+ `year_id` int NOT NULL,
194
194
 
195
195
  PRIMARY KEY (`id`),
196
196
 
@@ -198,9 +198,9 @@
198
198
 
199
199
  CONSTRAINT `fk_department_year10`
200
200
 
201
- FOREIGN KEY (`year_id`)
201
+ FOREIGN KEY (`year_id`)
202
-
202
+
203
- REFERENCES `mydb`.`year` (`id`)
203
+ REFERENCES `mydb`.`year` (`id`)
204
204
 
205
205
  ON DELETE NO ACTION
206
206
 
@@ -218,11 +218,11 @@
218
218
 
219
219
  CREATE TABLE IF NOT EXISTS `mydb`.`departmentgroup` (
220
220
 
221
- `id` NOT NULL,
221
+ `id` int NOT NULL,
222
222
 
223
223
  `department_name` VARCHAR(45) NULL,
224
224
 
225
- `department_id` NOT NULL,
225
+ `department_id` int NOT NULL,
226
226
 
227
227
  PRIMARY KEY (`id`),
228
228
 
@@ -230,9 +230,9 @@
230
230
 
231
231
  CONSTRAINT `fk_department_department1`
232
232
 
233
- FOREIGN KEY (`department_id`)
233
+ FOREIGN KEY (`department_id`)
234
-
234
+
235
- REFERENCES `mydb`.`department` (`id`)
235
+ REFERENCES `mydb`.`department` (`id`)
236
236
 
237
237
  ON DELETE NO ACTION
238
238
 
@@ -250,13 +250,13 @@
250
250
 
251
251
  CREATE TABLE IF NOT EXISTS `mydb`.`user` (
252
252
 
253
- `id` NOT NULL,
253
+ `id` int NOT NULL,
254
254
 
255
255
  `name` VARCHAR(45) NOT NULL,
256
256
 
257
257
  `birthday` DATE NOT NULL,
258
258
 
259
- `company_id` NOT NULL,
259
+ `company_id` int NOT NULL,
260
260
 
261
261
  `gender` INT NOT NULL,
262
262
 
@@ -266,9 +266,9 @@
266
266
 
267
267
  CONSTRAINT `fk_user_company`
268
268
 
269
- FOREIGN KEY (`company_id`)
269
+ FOREIGN KEY (`company_id`)
270
-
270
+
271
- REFERENCES `mydb`.`company` (`id`)
271
+ REFERENCES `mydb`.`company` (`id`)
272
272
 
273
273
  ON DELETE NO ACTION
274
274
 
@@ -286,11 +286,11 @@
286
286
 
287
287
  CREATE TABLE IF NOT EXISTS `mydb`.`yearuser` (
288
288
 
289
- `id` NOT NULL,
289
+ `id` int NOT NULL,
290
-
290
+
291
- `user_id` NOT NULL,
291
+ `user_id` int NOT NULL,
292
-
292
+
293
- `year_id` NOT NULL,
293
+ `year_id` int NOT NULL,
294
294
 
295
295
  `address` VARCHAR(45) NULL,
296
296
 
@@ -308,9 +308,9 @@
308
308
 
309
309
  CONSTRAINT `fk_yearuser_user1`
310
310
 
311
- FOREIGN KEY (`user_id`)
311
+ FOREIGN KEY (`user_id`)
312
-
312
+
313
- REFERENCES `mydb`.`user` (`id`)
313
+ REFERENCES `mydb`.`user` (`id`)
314
314
 
315
315
  ON DELETE NO ACTION
316
316
 
@@ -318,9 +318,9 @@
318
318
 
319
319
  CONSTRAINT `fk_yearuser_year1`
320
320
 
321
- FOREIGN KEY (`year_id`)
321
+ FOREIGN KEY (`year_id`)
322
-
322
+
323
- REFERENCES `mydb`.`year` (`id`)
323
+ REFERENCES `mydb`.`year` (`id`)
324
324
 
325
325
  ON DELETE NO ACTION
326
326
 
@@ -338,11 +338,11 @@
338
338
 
339
339
  CREATE TABLE IF NOT EXISTS `mydb`.`departmentuser` (
340
340
 
341
- `id` NOT NULL,
341
+ `id` int NOT NULL,
342
-
342
+
343
- `yearuser_id` NOT NULL,
343
+ `yearuser_id` int NOT NULL,
344
-
344
+
345
- `department_id` NOT NULL,
345
+ `department_id` int NOT NULL,
346
346
 
347
347
  PRIMARY KEY (`id`),
348
348
 
@@ -352,9 +352,9 @@
352
352
 
353
353
  CONSTRAINT `fk_departmentuser_yearuser1`
354
354
 
355
- FOREIGN KEY (`yearuser_id`)
355
+ FOREIGN KEY (`yearuser_id`)
356
-
356
+
357
- REFERENCES `mydb`.`yearuser` (`id`)
357
+ REFERENCES `mydb`.`yearuser` (`id`)
358
358
 
359
359
  ON DELETE NO ACTION
360
360
 
@@ -362,9 +362,9 @@
362
362
 
363
363
  CONSTRAINT `fk_departmentuser_department1`
364
364
 
365
- FOREIGN KEY (`department_id`)
365
+ FOREIGN KEY (`department_id`)
366
-
366
+
367
- REFERENCES `mydb`.`departmentgroup` (`id`)
367
+ REFERENCES `mydb`.`departmentgroup` (`id`)
368
368
 
369
369
  ON DELETE NO ACTION
370
370
 
@@ -382,6 +382,8 @@
382
382
 
383
383
 
384
384
 
385
+
386
+
385
387
  ```
386
388
 
387
389
 

1

あ

2019/11/22 03:24

投稿

m3304017499
m3304017499

スコア5

test CHANGED
File without changes
test CHANGED
@@ -64,6 +64,34 @@
64
64
 
65
65
  ```
66
66
 
67
+ ### テーブル設計内容
68
+
69
+ ```
70
+
71
+ ① departmentの名前は変更されても過去のyearの部署名は変更されないこと
72
+
73
+ 　　→ departmentにdepartment_nameでその当時の名前を保存するように設計
74
+
75
+
76
+
77
+ ② yearでdepartmentを引き継いだ場合departmentは同じものとして扱うこと
78
+
79
+ 　　→ departmentを親にdepartmentgroupで名前変更されても同じものに判別するよう設計
80
+
81
+
82
+
83
+ ③ userの情報はyearごとに管理できること、　（名前、生年月日はyearごとに管理しない）
84
+
85
+ 　　→ yearuserでその年のuser情報を保存するように設計
86
+
87
+
88
+
89
+ ④ yearごとに表示されるユーザーを表示すること。
90
+
91
+ 　　→ yearuserでユーザー取得できるように設計
92
+
93
+ ```
94
+
67
95
 
68
96
 
69
97
  ### 該当のソースコード
@@ -74,12 +102,28 @@
74
102
 
75
103
 
76
104
 
105
+ SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
106
+
107
+ SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
108
+
109
+ SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
110
+
111
+
112
+
77
113
  -- -----------------------------------------------------
78
114
 
79
115
  -- Schema mydb
80
116
 
81
117
  -- -----------------------------------------------------
82
118
 
119
+
120
+
121
+ -- -----------------------------------------------------
122
+
123
+ -- Schema mydb
124
+
125
+ -- -----------------------------------------------------
126
+
83
127
  CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
84
128
 
85
129
  USE `mydb` ;
@@ -148,13 +192,131 @@
148
192
 
149
193
  `year_id` NOT NULL,
150
194
 
195
+ PRIMARY KEY (`id`),
196
+
197
+ INDEX `fk_department_year1_idx` (`year_id` ASC),
198
+
199
+ CONSTRAINT `fk_department_year10`
200
+
201
+ FOREIGN KEY (`year_id`)
202
+
203
+ REFERENCES `mydb`.`year` (`id`)
204
+
205
+ ON DELETE NO ACTION
206
+
207
+ ON UPDATE NO ACTION);
208
+
209
+
210
+
211
+
212
+
213
+ -- -----------------------------------------------------
214
+
215
+ -- Table `mydb`.`departmentgroup`
216
+
217
+ -- -----------------------------------------------------
218
+
219
+ CREATE TABLE IF NOT EXISTS `mydb`.`departmentgroup` (
220
+
221
+ `id` NOT NULL,
222
+
151
223
  `department_name` VARCHAR(45) NULL,
152
224
 
225
+ `department_id` NOT NULL,
226
+
153
- PRIMARY KEY (`id`),
227
+ PRIMARY KEY (`id`),
228
+
154
-
229
+ INDEX `fk_department_department1_idx` (`department_id` ASC),
230
+
231
+ CONSTRAINT `fk_department_department1`
232
+
233
+ FOREIGN KEY (`department_id`)
234
+
235
+ REFERENCES `mydb`.`department` (`id`)
236
+
237
+ ON DELETE NO ACTION
238
+
239
+ ON UPDATE NO ACTION);
240
+
241
+
242
+
243
+
244
+
245
+ -- -----------------------------------------------------
246
+
247
+ -- Table `mydb`.`user`
248
+
249
+ -- -----------------------------------------------------
250
+
251
+ CREATE TABLE IF NOT EXISTS `mydb`.`user` (
252
+
253
+ `id` NOT NULL,
254
+
255
+ `name` VARCHAR(45) NOT NULL,
256
+
257
+ `birthday` DATE NOT NULL,
258
+
259
+ `company_id` NOT NULL,
260
+
261
+ `gender` INT NOT NULL,
262
+
263
+ PRIMARY KEY (`id`),
264
+
265
+ INDEX `fk_user_company_idx` (`company_id` ASC),
266
+
267
+ CONSTRAINT `fk_user_company`
268
+
269
+ FOREIGN KEY (`company_id`)
270
+
271
+ REFERENCES `mydb`.`company` (`id`)
272
+
273
+ ON DELETE NO ACTION
274
+
275
+ ON UPDATE NO ACTION);
276
+
277
+
278
+
279
+
280
+
281
+ -- -----------------------------------------------------
282
+
283
+ -- Table `mydb`.`yearuser`
284
+
285
+ -- -----------------------------------------------------
286
+
287
+ CREATE TABLE IF NOT EXISTS `mydb`.`yearuser` (
288
+
289
+ `id` NOT NULL,
290
+
291
+ `user_id` NOT NULL,
292
+
293
+ `year_id` NOT NULL,
294
+
295
+ `address` VARCHAR(45) NULL,
296
+
297
+ `email` VARCHAR(45) NULL,
298
+
299
+ `zip` VARCHAR(45) NULL,
300
+
301
+ `tel` VARCHAR(45) NULL,
302
+
303
+ PRIMARY KEY (`id`),
304
+
305
+ INDEX `fk_yearuser_user1_idx` (`user_id` ASC),
306
+
155
- INDEX `fk_department_year1_idx` (`year_id` ASC),
307
+ INDEX `fk_yearuser_year1_idx` (`year_id` ASC),
308
+
156
-
309
+ CONSTRAINT `fk_yearuser_user1`
310
+
311
+ FOREIGN KEY (`user_id`)
312
+
313
+ REFERENCES `mydb`.`user` (`id`)
314
+
315
+ ON DELETE NO ACTION
316
+
317
+ ON UPDATE NO ACTION,
318
+
157
- CONSTRAINT `fk_department_year1`
319
+ CONSTRAINT `fk_yearuser_year1`
158
320
 
159
321
  FOREIGN KEY (`year_id`)
160
322
 
@@ -170,127 +332,39 @@
170
332
 
171
333
  -- -----------------------------------------------------
172
334
 
173
- -- Table `mydb`.`user`
335
+ -- Table `mydb`.`departmentuser`
174
-
336
+
175
- -- -----------------------------------------------------
337
+ -- -----------------------------------------------------
176
-
338
+
177
- CREATE TABLE IF NOT EXISTS `mydb`.`user` (
339
+ CREATE TABLE IF NOT EXISTS `mydb`.`departmentuser` (
178
-
340
+
179
- `id` NOT NULL,
341
+ `id` NOT NULL,
180
-
181
- `name` VARCHAR(45) NOT NULL,
342
+
182
-
183
- `birthday` DATE NOT NULL,
343
+ `yearuser_id` NOT NULL,
184
-
344
+
185
- `company_id` NOT NULL,
345
+ `department_id` NOT NULL,
186
-
187
- `gender` INT NOT NULL,
346
+
188
-
189
- PRIMARY KEY (`id`),
347
+ PRIMARY KEY (`id`),
190
-
191
- INDEX `fk_user_company_idx` (`company_id` ASC),
348
+
192
-
193
- CONSTRAINT `fk_user_company`
194
-
195
- FOREIGN KEY (`company_id`)
196
-
197
- REFERENCES `mydb`.`company` (`id`)
198
-
199
- ON DELETE NO ACTION
200
-
201
- ON UPDATE NO ACTION);
202
-
203
-
204
-
205
-
206
-
207
- -- -----------------------------------------------------
208
-
209
- -- Table `mydb`.`yearuser`
210
-
211
- -- -----------------------------------------------------
212
-
213
- CREATE TABLE IF NOT EXISTS `mydb`.`yearuser` (
214
-
215
- `id` NOT NULL,
216
-
217
- `user_id` NOT NULL,
218
-
219
- `year_id` NOT NULL,
220
-
221
- `address` VARCHAR(45) NULL,
222
-
223
- `email` VARCHAR(45) NULL,
224
-
225
- `zip` VARCHAR(45) NULL,
226
-
227
- `tel` VARCHAR(45) NULL,
228
-
229
- PRIMARY KEY (`id`),
230
-
231
- INDEX `fk_yearuser_user1_idx` (`user_id` ASC),
349
+ INDEX `fk_departmentuser_yearuser1_idx` (`yearuser_id` ASC),
232
-
350
+
233
- INDEX `fk_yearuser_year1_idx` (`year_id` ASC),
351
+ INDEX `fk_departmentuser_department1_idx` (`department_id` ASC),
234
-
352
+
235
- CONSTRAINT `fk_yearuser_user1`
353
+ CONSTRAINT `fk_departmentuser_yearuser1`
236
-
354
+
237
- FOREIGN KEY (`user_id`)
355
+ FOREIGN KEY (`yearuser_id`)
238
-
356
+
239
- REFERENCES `mydb`.`user` (`id`)
357
+ REFERENCES `mydb`.`yearuser` (`id`)
240
358
 
241
359
  ON DELETE NO ACTION
242
360
 
243
361
  ON UPDATE NO ACTION,
244
362
 
245
- CONSTRAINT `fk_yearuser_year1`
246
-
247
- FOREIGN KEY (`year_id`)
248
-
249
- REFERENCES `mydb`.`year` (`id`)
250
-
251
- ON DELETE NO ACTION
252
-
253
- ON UPDATE NO ACTION);
254
-
255
-
256
-
257
-
258
-
259
- -- -----------------------------------------------------
260
-
261
- -- Table `mydb`.`departmentuser`
262
-
263
- -- -----------------------------------------------------
264
-
265
- CREATE TABLE IF NOT EXISTS `mydb`.`departmentuser` (
266
-
267
- `id` NOT NULL,
268
-
269
- `yearuser_id` NOT NULL,
270
-
271
- `department_id` NOT NULL,
272
-
273
- PRIMARY KEY (`id`),
274
-
275
- INDEX `fk_departmentuser_yearuser1_idx` (`yearuser_id` ASC),
276
-
277
- INDEX `fk_departmentuser_department1_idx` (`department_id` ASC),
278
-
279
- CONSTRAINT `fk_departmentuser_yearuser1`
280
-
281
- FOREIGN KEY (`yearuser_id`)
282
-
283
- REFERENCES `mydb`.`yearuser` (`id`)
284
-
285
- ON DELETE NO ACTION
286
-
287
- ON UPDATE NO ACTION,
288
-
289
363
  CONSTRAINT `fk_departmentuser_department1`
290
364
 
291
365
  FOREIGN KEY (`department_id`)
292
366
 
293
- REFERENCES `mydb`.`department` (`id`)
367
+ REFERENCES `mydb`.`departmentgroup` (`id`)
294
368
 
295
369
  ON DELETE NO ACTION
296
370