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)
