「DBをきちんと設計した③」（第３正規化まで）

はじめに
実装
最後に

はじめに

前回の記事で、第１正規化のDDLを書いて、スクリプトも用意しました。

今回の記事は第３正規化まで行います。

実装

実装したDDLは以下のようになりました。（第３正規化は途中からで、各処理についてはコメントアウトにて記載しています）

CREATE TABLE `sakura_table` (
       id INT NOT NULL,
       `spot_name` varchar(100),
       `common_season` varchar(100),
       `open_close` varchar(100),
       `sakura_genre` varchar(100),
       `light_up` varchar(100),
       `osusume_view_point` varchar(200),
       `other_osusume_poiint` varchar(200),
       `fee` varchar(100),
       `address` varchar(100), 
       `access` varchar(100),
       `parking` varchar(100),
       `url` varchar(100), 
       `lat` float(20,10),
       `lng` float(20,10),
       `season_list` varchar(100),
       PRIMARY KEY (id)
);

LOAD DATA LOCAL INFILE '/tmp/dml/final_sakura_info_20241002.csv' INTO TABLE `sakura_table` FIELDS TERMINATED BY ',' ENCLOSED BY '"' IGNORE 1 LINES;

CREATE TABLE `sakura_season_table` (
    id INT,
    season INT,
    PRIMARY KEY (id, season),
    FOREIGN KEY (id) REFERENCES sakura_table(id)
);

-- -------------- ここまでが前回の記事で書いたこと --------------

-- 第三正規化で追加したテーブル↓
CREATE TABLE `sakura_spot_info_table` (
    `spot_name` varchar(100) NOT NULL,
    `common_season` varchar(100),
    `open_close` varchar(100),
    `sakura_genre` varchar(100),
    `light_up` varchar(100),
    `osusume_view_point` varchar(200),
    `other_osusume_poiint` varchar(200),
    `fee` varchar(100),
    `address` varchar(100), 
    `access` varchar(100),
    `parking` varchar(100),
    `url` varchar(100), 
    `lat` float(20,10),
    `lng` float(20,10),
    PRIMARY KEY (`spot_name`)
);

-- 第三正規化で作ったテーブルにデータの注入
INSERT INTO `sakura_spot_info_table` 
(`spot_name`, `common_season`, `open_close`, `sakura_genre`, `light_up`, 
 `osusume_view_point`, `other_osusume_poiint`, `fee`, `address`, `access`, 
 `parking`, `url`, `lat`, `lng`)
SELECT DISTINCT `spot_name`, `common_season`, `open_close`, `sakura_genre`, 
       `light_up`, `osusume_view_point`, `other_osusume_poiint`, `fee`, 
       `address`, `access`, `parking`, `url`, `lat`, `lng`
FROM `sakura_table`;

-- 元のテーブルの不要なカラムを削除
SET foreign_key_checks = 0;
ALTER TABLE `sakura_table`
DROP COLUMN `common_season`,
DROP COLUMN `open_close`,
DROP COLUMN `sakura_genre`,
DROP COLUMN `light_up`,
DROP COLUMN `osusume_view_point`,
DROP COLUMN `other_osusume_poiint`,
DROP COLUMN `fee`,
DROP COLUMN `address`,
DROP COLUMN `access`,
DROP COLUMN `parking`,
DROP COLUMN `url`,
DROP COLUMN `lat`,
DROP COLUMN `lng`,
DROP COLUMN `season_list`,
ADD CONSTRAINT fk_spot_info
FOREIGN KEY (`spot_name`)
REFERENCES `sakura_spot_info_table`(`spot_name`);
SET foreign_key_checks = 1;

設定できているかの確認。

mysql> SHOW COLUMNS FROM sakura_table;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| id          | int          | NO   | PRI | NULL    |       |
| spot_name   | varchar(100) | YES  | MUL | NULL    |       |
| season_list | varchar(100) | YES  |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> SELECT COUNT(*) FROM sakura_table;
+----------+
| COUNT(*) |
+----------+
|      100 |
+----------+
1 row in set (0.02 sec)

mysql> SHOW COLUMNS FROM sakura_spot_info_table;
+----------------------+--------------+------+-----+---------+-------+
| Field                | Type         | Null | Key | Default | Extra |
+----------------------+--------------+------+-----+---------+-------+
| spot_name            | varchar(100) | NO   | PRI | NULL    |       |
| common_season        | varchar(100) | YES  |     | NULL    |       |
| open_close           | varchar(100) | YES  |     | NULL    |       |
| sakura_genre         | varchar(100) | YES  |     | NULL    |       |
| light_up             | varchar(100) | YES  |     | NULL    |       |
| osusume_view_point   | varchar(200) | YES  |     | NULL    |       |
| other_osusume_poiint | varchar(200) | YES  |     | NULL    |       |
| fee                  | varchar(100) | YES  |     | NULL    |       |
| address              | varchar(100) | YES  |     | NULL    |       |
| access               | varchar(100) | YES  |     | NULL    |       |
| parking              | varchar(100) | YES  |     | NULL    |       |
| url                  | varchar(100) | YES  |     | NULL    |       |
| lat                  | float(20,10) | YES  |     | NULL    |       |
| lng                  | float(20,10) | YES  |     | NULL    |       |
+----------------------+--------------+------+-----+---------+-------+
14 rows in set (0.00 sec)

mysql> SELECT COUNT(*) FROM sakura_spot_info_table;
+----------+
| COUNT(*) |
+----------+
|      100 |
+----------+
1 row in set (0.01 sec)