2019-08-18
DB正規化の注意点
DB 設計について、「値 A って値 B から導出可能っぽいな！冗長だから DB には値 A だけもたせよう！」の危険性について考えた。
(前提)正規化のメリット
正規化＝冗長性排除により、以下のメリットが得られる。
- 保守性の向上=更新時に 1 箇所だけ書き換えれば OK
- 必要なことをやるコストが小さくなる
- 必要なことをしそびれてデータが矛盾が生じるリスクを抑える
- (データ量の縮減)
これを踏まえた上で、ある箇所で出現する値 A と、別の箇所で出現する値 B について、 「冗長である」＝「一方だけ保存しておけばよい」か否かをどう判断するか、考えていきたい。
サンプル
以下、小売店をイメージした具体例で考える。
- お客さんを users テーブルで表現する
- 商品を items テーブルで表現する
- 各商品は「通常ポイント加算商品(1%)」or「特別ポイント加算商品(5%)」のどちらか
- ポイントの区分は point_categories テーブルで表現する
- お客さんが商品を購入したことを purchases テーブルで表現する
- 簡略化のため、1 つの商品を購入するごとに purchase レコードを 1 つ生成する
- お客さんには、購入した商品の金額*その商品のポイント区分のレートを掛けて切り捨てた値がポイントとして付与される
- たとえば 360 円の通常ポイント加算商品(1%)であれば、3pt が付与される。
テーブル構造は以下のとおり。
mysql> desc users;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
mysql> desc items;
+-------------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| price | int(11) | YES | | NULL | |
| point_category_id | int(11) | YES | MUL | NULL | |
+-------------------+-------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
mysql> desc point_categories;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| rate | decimal(3,2) | YES | | NULL | |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> desc purchases;
+--------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| purchased_at | date | YES | | NULL | |
| user_id | int(11) | YES | MUL | NULL | |
| item_id | int(11) | YES | MUL | NULL | |
+--------------+---------+------+-----+---------+----------------+
4 rows in set (0.01 sec)ここに以下のようなサンプルデータをもたせた。
mysql> select * from users;
+----+------+
| id | name |
+----+------+
| 1 | taro |
| 2 | jiro |
+----+------+
2 rows in set (0.00 sec)
mysql> select * from items;
+----+----------+-------+-------------------+
| id | name | price | point_category_id |
+----+----------+-------+-------------------+
| 1 | rice | 1980 | 2 |
| 2 | bread | 320 | 1 |
| 3 | sabamiso | 398 | 1 |
| 4 | stew | 680 | 1 |
| 5 | sake | 950 | 1 |
| 6 | wine | 1180 | 2 |
+----+----------+-------+-------------------+
6 rows in set (0.00 sec)
mysql> select * from point_categories;
+----+---------+------+
| id | name | rate |
+----+---------+------+
| 1 | normal | 0.01 |
| 2 | special | 0.05 |
+----+---------+------+
2 rows in set (0.00 sec)
mysql> select * from purchases;
+----+--------------+---------+---------+
| id | purchased_at | user_id | item_id |
+----+--------------+---------+---------+
| 1 | 2019-07-15 | 1 | 1 |
| 2 | 2019-07-20 | 1 | 3 |
| 3 | 2019-07-25 | 1 | 5 |
| 4 | 2019-07-30 | 2 | 2 |
| 5 | 2019-08-05 | 2 | 4 |
| 6 | 2019-08-10 | 2 | 6 |
+----+--------------+---------+---------+
6 rows in set (0.00 sec)本題
さて、いま 2 つの値を考える。
A.「ポイント区分ごとに定められたレート(1%/5%)」
B.「ある購入で付与されたポイント(10pt とか 20pt とか)」
B は A から導出可能だろうか？ シンプルに考えると、購入された商品の金額とポイント区分さえわかれば、付与されたポイントは導出可能に思われる。
上記の例では、「『ある購入で付与されたポイント』は冗長であり DB にもたせる必要がない」と考えた場合の DB 設計としている。 それゆえ、purchases テーブルには付与されたポイントを表現するカラムを用意していない。
各人に付与されたポイントの合計については、計算した値をすぐ取り出せるようビューを作成しておく。
mysql> create view purchase_reports as
-> select p.purchased_at, u.name as user_name, i.name as item_name, i.price, pc.rate, floor(i.price * pc.rate) as point
-> from purchases p
-> join users u on p.user_id = u.id
-> join items i on p.item_id = i.id
-> join point_categories pc on i.point_category_id = pc.id
-> order by p.purchased_at asc;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from purchase_reports;
+--------------+-----------+-----------+-------+------+-------+
| purchased_at | user_name | item_name | price | rate | point |
+--------------+-----------+-----------+-------+------+-------+
| 2019-07-15 | taro | rice | 1980 | 0.05 | 99 |
| 2019-07-20 | taro | sabamiso | 398 | 0.01 | 3 |
| 2019-07-25 | taro | sake | 950 | 0.01 | 9 |
| 2019-07-30 | jiro | bread | 320 | 0.01 | 3 |
| 2019-08-05 | jiro | stew | 680 | 0.01 | 6 |
| 2019-08-10 | jiro | wine | 1180 | 0.05 | 59 |
+--------------+-----------+-----------+-------+------+-------+
6 rows in set (0.01 sec)しかし私の考えでは、「ある購入で付与されたポイント」は「(ビジネスロジックによっては)冗長でなく、 それゆえ DB にもたせておくべき値」だと思われる。
冗長性の有無=DB に値をもたせることの是非を検討するための 3 つの観点を述べるとともに、 「DB に値をもたせなかった場合に問題が生じるのか」をみていきたい。
観点 1: 値 A を含め、DB 内に存在する情報だけから値 B を導出できるか？
この例では、以下のように言い換えられる。
「『ポイント区分ごとに定められたレート(1%/5%)』を含め、DB 内に存在する情報だけから『ある購入で付与されたポイント』を導出できるか？」
これが成立しないのは、例えば「レジでくじを引いて当たりなら 100pt 付与」というキャンペーンを始めた場合だ。
サンプルのテーブル設計の場合、くじで当たりを引いたかどうかを記録しておく箇所がないので、当然ながら実際に付与されたポイントを知ることはできなくなってしまう。
mysql> select * from purchase_reports;
+--------------+-----------+-----------+-------+------+-------+
| purchased_at | user_name | item_name | price | rate | point | 実際に付与されたポイントは...
+--------------+-----------+-----------+-------+------+-------+
| 2019-07-15 | taro | rice | 1980 | 0.05 | 99 | floor(1980*0.05) => 99
| 2019-07-20 | taro | sabamiso | 398 | 0.01 | 3 | floor( 398*0.01) => 3
| 2019-07-25 | taro | sake | 950 | 0.01 | 9 | floor( 950*0.01) => 9
| 2019-07-30 | jiro | bread | 320 | 0.01 | 3 | floor( 320*0.01) で3, くじが当たって100 => 103
| 2019-08-05 | jiro | stew | 680 | 0.01 | 6 | floor( 680*0.01) => 6
| 2019-08-10 | jiro | wine | 1180 | 0.05 | 59 | floor(1180*0.05) => 59
+--------------+-----------+-----------+-------+------+-------+
6 rows in set (0.01 sec)観点 2: 値 A から値 B を導出するロジックが変化する可能性はないか？
この例では、以下のように言い換えられる。
「『ポイント区分ごとに定められたレート(1%/5%)』から『ある購入で付与されたポイント』を導出するロジックが変化する可能性はないか？」
このロジックが変化する場合というのは、例えばポイント導出のルールが「金額にレートを掛けて切り捨て」が「切り上げ」に変わる場合だ。
このルール変更が 8/1 から適用されたとすると、当然ながら既存のビューのままでは「ある購入で付与されたポイント」を正しく求めることはできない。むりやりただしい値を出そうとするなら、ビューを以下のように作り直さねばならない。
mysql> create view purchase_reports as
-> select p.purchased_at, u.name as user_name, i.name as item_name, i.price, pc.rate,
-> case
-> when p.purchased_at < '2019-08-01' then floor(i.price * pc.rate)
-> when p.purchased_at >= '2019-08-01' then ceil(i.price * pc.rate)
-> end as point
-> from purchases p
-> join users u on p.user_id = u.id
-> join items i on p.item_id = i.id
-> join point_categories pc on i.point_category_id = pc.id
-> order by p.purchased_at asc;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from purchase_reports;
+--------------+-----------+-----------+-------+------+-------+
| purchased_at | user_name | item_name | price | rate | point | 実際に付与されたポイントは...
+--------------+-----------+-----------+-------+------+-------+
| 2019-07-15 | taro | rice | 1980 | 0.05 | 99 | floor(1980*0.05) => 99
| 2019-07-20 | taro | sabamiso | 398 | 0.01 | 3 | floor( 398*0.01) => 3
| 2019-07-25 | taro | sake | 950 | 0.01 | 9 | floor( 950*0.01) => 9
| 2019-07-30 | jiro | bread | 320 | 0.01 | 3 | floor( 320*0.01) => 3
| 2019-08-05 | jiro | stew | 680 | 0.01 | 7 | ceil( 680*0.01) => 7
| 2019-08-10 | jiro | wine | 1180 | 0.05 | 59 | ceil(1180*0.05) => 59
+--------------+-----------+-----------+-------+------+-------+
6 rows in set (0.00 sec)「この時点ではどういうルールでポイントを算出していたんだっけ？」と遡ってビューやアプリケーションコードで頑張って復元することは、(少なくともこの例では)なんとかできた。
しかしこれは妥当な処理とは言えない。
本来、値 A から値 B を算出するロジックが可変的なものであるのならば、「とにかくその時点で採用されているロジックで算出した『その購入で付与されたポイント』を、その購入を表現するレコードにもたせておく」べきである。
「各ケースごとにどのロジックを採用していたのかを判断できれば値 A から値 B を導出可能」であるときは、「値 A から値 B を導出できるので値 B は冗長=DB にもたせなくてよい」とは考えてはならない。
観点 3: 値 A が更新されたとき値 B も連動して更新されるが、それでよいか？
この例では、以下のように言い換えられる。
「『ポイント区分ごとに定められたレート(1%/5%)』を更新すると『ある購入で付与されたポイント』も連動して更新されるが、それでよいか？」
サンプルのデータベースにおいて、purchases テーブルは購入の履歴を表現している。
特別ポイント加算商品のレートが 5%から 10%に上昇したとき、実際に 5%が適用された過去の購入を表現する purchase レコードに対応する DB 上のレートは、10%になってしまう。
これは端的に履歴として誤っている。
mysql> update point_categories set rate = 0.1 where name = 'special';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from purchase_reports;
+--------------+-----------+-----------+-------+------+-------+
| purchased_at | user_name | item_name | price | rate | point | 実際に付与されたポイントは...
+--------------+-----------+-----------+-------+------+-------+
| 2019-07-15 | taro | rice | 1980 | 0.10 | 198 | floor(1980*0.05) => 99
| 2019-07-20 | taro | sabamiso | 398 | 0.01 | 3 | floor( 398*0.01) => 3
| 2019-07-25 | taro | sake | 950 | 0.01 | 9 | floor( 950*0.01) => 9
| 2019-07-30 | jiro | bread | 320 | 0.01 | 3 | floor( 320*0.01) => 3
| 2019-08-05 | jiro | stew | 680 | 0.01 | 6 | floor( 680*0.01) => 6
| 2019-08-10 | jiro | wine | 1180 | 0.10 | 118 | floor(1180*0.05) => 59
+--------------+-----------+-----------+-------+------+-------+
6 rows in set (0.01 sec)