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

編集履歴

質問編集履歴

4

ご教授ください。

2019/11/22 05:55

投稿

m3304017499
m3304017499

スコア5

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

3

あ

2019/11/22 05:55

投稿

m3304017499
m3304017499

スコア5

title CHANGED
File without changes
body CHANGED
@@ -193,4 +193,7 @@
193
193
 
194
194
  ```
195
195
 
196
- ### ご教授いただきたいです。
196
+ ### ご教授いただきたいです。
197
+
198
+
199
+ ### SQL更新いたしました。

2

a

2019/11/22 03:31

投稿

m3304017499
m3304017499

スコア5

title CHANGED
File without changes
body CHANGED
@@ -68,7 +68,7 @@
68
68
  -- Table `mydb`.`company`
69
69
  -- -----------------------------------------------------
70
70
  CREATE TABLE IF NOT EXISTS `mydb`.`company` (
71
- `id` NOT NULL,
71
+ `id` int NOT NULL,
72
72
  `name` VARCHAR(45) NULL,
73
73
  PRIMARY KEY (`id`));
74
74
 
@@ -77,14 +77,14 @@
77
77
  -- Table `mydb`.`year`
78
78
  -- -----------------------------------------------------
79
79
  CREATE TABLE IF NOT EXISTS `mydb`.`year` (
80
- `id` NOT NULL,
80
+ `id` int NOT NULL,
81
81
  `name` VARCHAR(45) NULL,
82
- `company_id` NOT NULL,
82
+ `company_id` int NOT NULL,
83
83
  PRIMARY KEY (`id`),
84
84
  INDEX `fk_year_company1_idx` (`company_id` ASC),
85
85
  CONSTRAINT `fk_year_company1`
86
- FOREIGN KEY (`company_id`)
86
+ FOREIGN KEY (`company_id`)
87
- REFERENCES `mydb`.`company` (`id`)
87
+ REFERENCES `mydb`.`company` (`id`)
88
88
  ON DELETE NO ACTION
89
89
  ON UPDATE NO ACTION);
90
90
 
@@ -93,13 +93,13 @@
93
93
  -- Table `mydb`.`department`
94
94
  -- -----------------------------------------------------
95
95
  CREATE TABLE IF NOT EXISTS `mydb`.`department` (
96
- `id` NOT NULL,
96
+ `id` int NOT NULL,
97
- `year_id` NOT NULL,
97
+ `year_id` int NOT NULL,
98
98
  PRIMARY KEY (`id`),
99
99
  INDEX `fk_department_year1_idx` (`year_id` ASC),
100
100
  CONSTRAINT `fk_department_year10`
101
- FOREIGN KEY (`year_id`)
101
+ FOREIGN KEY (`year_id`)
102
- REFERENCES `mydb`.`year` (`id`)
102
+ REFERENCES `mydb`.`year` (`id`)
103
103
  ON DELETE NO ACTION
104
104
  ON UPDATE NO ACTION);
105
105
 
@@ -108,14 +108,14 @@
108
108
  -- Table `mydb`.`departmentgroup`
109
109
  -- -----------------------------------------------------
110
110
  CREATE TABLE IF NOT EXISTS `mydb`.`departmentgroup` (
111
- `id` NOT NULL,
111
+ `id` int NOT NULL,
112
112
  `department_name` VARCHAR(45) NULL,
113
- `department_id` NOT NULL,
113
+ `department_id` int NOT NULL,
114
114
  PRIMARY KEY (`id`),
115
115
  INDEX `fk_department_department1_idx` (`department_id` ASC),
116
116
  CONSTRAINT `fk_department_department1`
117
- FOREIGN KEY (`department_id`)
117
+ FOREIGN KEY (`department_id`)
118
- REFERENCES `mydb`.`department` (`id`)
118
+ REFERENCES `mydb`.`department` (`id`)
119
119
  ON DELETE NO ACTION
120
120
  ON UPDATE NO ACTION);
121
121
 
@@ -124,16 +124,16 @@
124
124
  -- Table `mydb`.`user`
125
125
  -- -----------------------------------------------------
126
126
  CREATE TABLE IF NOT EXISTS `mydb`.`user` (
127
- `id` NOT NULL,
127
+ `id` int NOT NULL,
128
128
  `name` VARCHAR(45) NOT NULL,
129
129
  `birthday` DATE NOT NULL,
130
- `company_id` NOT NULL,
130
+ `company_id` int NOT NULL,
131
131
  `gender` INT NOT NULL,
132
132
  PRIMARY KEY (`id`),
133
133
  INDEX `fk_user_company_idx` (`company_id` ASC),
134
134
  CONSTRAINT `fk_user_company`
135
- FOREIGN KEY (`company_id`)
135
+ FOREIGN KEY (`company_id`)
136
- REFERENCES `mydb`.`company` (`id`)
136
+ REFERENCES `mydb`.`company` (`id`)
137
137
  ON DELETE NO ACTION
138
138
  ON UPDATE NO ACTION);
139
139
 
@@ -142,9 +142,9 @@
142
142
  -- Table `mydb`.`yearuser`
143
143
  -- -----------------------------------------------------
144
144
  CREATE TABLE IF NOT EXISTS `mydb`.`yearuser` (
145
- `id` NOT NULL,
145
+ `id` int NOT NULL,
146
- `user_id` NOT NULL,
146
+ `user_id` int NOT NULL,
147
- `year_id` NOT NULL,
147
+ `year_id` int NOT NULL,
148
148
  `address` VARCHAR(45) NULL,
149
149
  `email` VARCHAR(45) NULL,
150
150
  `zip` VARCHAR(45) NULL,
@@ -153,13 +153,13 @@
153
153
  INDEX `fk_yearuser_user1_idx` (`user_id` ASC),
154
154
  INDEX `fk_yearuser_year1_idx` (`year_id` ASC),
155
155
  CONSTRAINT `fk_yearuser_user1`
156
- FOREIGN KEY (`user_id`)
156
+ FOREIGN KEY (`user_id`)
157
- REFERENCES `mydb`.`user` (`id`)
157
+ REFERENCES `mydb`.`user` (`id`)
158
158
  ON DELETE NO ACTION
159
159
  ON UPDATE NO ACTION,
160
160
  CONSTRAINT `fk_yearuser_year1`
161
- FOREIGN KEY (`year_id`)
161
+ FOREIGN KEY (`year_id`)
162
- REFERENCES `mydb`.`year` (`id`)
162
+ REFERENCES `mydb`.`year` (`id`)
163
163
  ON DELETE NO ACTION
164
164
  ON UPDATE NO ACTION);
165
165
 
@@ -168,20 +168,20 @@
168
168
  -- Table `mydb`.`departmentuser`
169
169
  -- -----------------------------------------------------
170
170
  CREATE TABLE IF NOT EXISTS `mydb`.`departmentuser` (
171
- `id` NOT NULL,
171
+ `id` int NOT NULL,
172
- `yearuser_id` NOT NULL,
172
+ `yearuser_id` int NOT NULL,
173
- `department_id` NOT NULL,
173
+ `department_id` int NOT NULL,
174
174
  PRIMARY KEY (`id`),
175
175
  INDEX `fk_departmentuser_yearuser1_idx` (`yearuser_id` ASC),
176
176
  INDEX `fk_departmentuser_department1_idx` (`department_id` ASC),
177
177
  CONSTRAINT `fk_departmentuser_yearuser1`
178
- FOREIGN KEY (`yearuser_id`)
178
+ FOREIGN KEY (`yearuser_id`)
179
- REFERENCES `mydb`.`yearuser` (`id`)
179
+ REFERENCES `mydb`.`yearuser` (`id`)
180
180
  ON DELETE NO ACTION
181
181
  ON UPDATE NO ACTION,
182
182
  CONSTRAINT `fk_departmentuser_department1`
183
- FOREIGN KEY (`department_id`)
183
+ FOREIGN KEY (`department_id`)
184
- REFERENCES `mydb`.`departmentgroup` (`id`)
184
+ REFERENCES `mydb`.`departmentgroup` (`id`)
185
185
  ON DELETE NO ACTION
186
186
  ON UPDATE NO ACTION);
187
187
 
@@ -190,6 +190,7 @@
190
190
  SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
191
191
  SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
192
192
 
193
+
193
194
  ```
194
195
 
195
196
  ### ご教授いただきたいです。

1

あ

2019/11/22 03:24

投稿

m3304017499
m3304017499

スコア5

title CHANGED
File without changes
body CHANGED
@@ -31,14 +31,36 @@
31
31
  DB設計上いかがなものなのでしょうか？
32
32
 
33
33
  ```
34
+ ### テーブル設計内容
35
+ ```
36
+ ① departmentの名前は変更されても過去のyearの部署名は変更されないこと
37
+ 　　→ departmentにdepartment_nameでその当時の名前を保存するように設計
34
38
 
39
+ ② yearでdepartmentを引き継いだ場合departmentは同じものとして扱うこと
40
+ 　　→ departmentを親にdepartmentgroupで名前変更されても同じものに判別するよう設計
41
+
42
+ ③ userの情報はyearごとに管理できること、　（名前、生年月日はyearごとに管理しない）
43
+ 　　→ yearuserでその年のuser情報を保存するように設計
44
+
45
+ ④ yearごとに表示されるユーザーを表示すること。
46
+ 　　→ yearuserでユーザー取得できるように設計
47
+ ```
48
+
35
49
  ### 該当のソースコード
36
50
 
37
51
  ```sql
38
52
 
53
+ SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
54
+ SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
55
+ SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
56
+
39
57
  -- -----------------------------------------------------
40
58
  -- Schema mydb
41
59
  -- -----------------------------------------------------
60
+
61
+ -- -----------------------------------------------------
62
+ -- Schema mydb
63
+ -- -----------------------------------------------------
42
64
  CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
43
65
  USE `mydb` ;
44
66
 
@@ -73,10 +95,9 @@
73
95
  CREATE TABLE IF NOT EXISTS `mydb`.`department` (
74
96
  `id` NOT NULL,
75
97
  `year_id` NOT NULL,
76
- `department_name` VARCHAR(45) NULL,
77
98
  PRIMARY KEY (`id`),
78
99
  INDEX `fk_department_year1_idx` (`year_id` ASC),
79
- CONSTRAINT `fk_department_year1`
100
+ CONSTRAINT `fk_department_year10`
80
101
  FOREIGN KEY (`year_id`)
81
102
  REFERENCES `mydb`.`year` (`id`)
82
103
  ON DELETE NO ACTION
@@ -84,6 +105,22 @@
84
105
 
85
106
 
86
107
  -- -----------------------------------------------------
108
+ -- Table `mydb`.`departmentgroup`
109
+ -- -----------------------------------------------------
110
+ CREATE TABLE IF NOT EXISTS `mydb`.`departmentgroup` (
111
+ `id` NOT NULL,
112
+ `department_name` VARCHAR(45) NULL,
113
+ `department_id` NOT NULL,
114
+ PRIMARY KEY (`id`),
115
+ INDEX `fk_department_department1_idx` (`department_id` ASC),
116
+ CONSTRAINT `fk_department_department1`
117
+ FOREIGN KEY (`department_id`)
118
+ REFERENCES `mydb`.`department` (`id`)
119
+ ON DELETE NO ACTION
120
+ ON UPDATE NO ACTION);
121
+
122
+
123
+ -- -----------------------------------------------------
87
124
  -- Table `mydb`.`user`
88
125
  -- -----------------------------------------------------
89
126
  CREATE TABLE IF NOT EXISTS `mydb`.`user` (
@@ -144,7 +181,7 @@
144
181
  ON UPDATE NO ACTION,
145
182
  CONSTRAINT `fk_departmentuser_department1`
146
183
  FOREIGN KEY (`department_id`)
147
- REFERENCES `mydb`.`department` (`id`)
184
+ REFERENCES `mydb`.`departmentgroup` (`id`)
148
185
  ON DELETE NO ACTION
149
186
  ON UPDATE NO ACTION);
150
187