失敗から学ぶRDBの正しい歩き方 その1 / learn-from-failure-1

88f4e84b94fe07cddbd9e6479d689192?s=47 soudai sone
February 25, 2019

失敗から学ぶRDBの正しい歩き方 その1 / learn-from-failure-1

88f4e84b94fe07cddbd9e6479d689192?s=128

soudai sone

February 25, 2019
Tweet

Transcript

  1. 12.

    自己紹介 曽根 壮大(34歳) 株式会社オミカレ 副社長/CTO • 日本PostgreSQLユーザ会 勉強会分科会 座長 •

    3人の子供がいます • 技術的にはWeb/LL言語/RDBが好きです そ ね た け と も
  2. 13.

    自己紹介 曽根 壮大(34歳) 株式会社オミカレ 副社長/CTO • 日本PostgreSQLユーザ会 勉強会分科会 座長 •

    3人の子供がいます • 技術的にはWeb/LL言語/RDBが好きです そ ね た け と も
  3. 16.
  4. 19.

    売上id 売上金額 売上日 配送状態 1 29,522 2014-03-31 23:59:59 配送済み 2

    6,480 2014-04-01 00:00:00 発注中 ︙ ︙ ︙ ︙ 名前 値 消費税率 0.05 売上id 商品id 個数 購入者 1 1 3 sone 1 2 3 sone 1 3 3 sone 2 4 3 sone ︙ ︙ ︙ ︙ 商品id 商品名 価格 1 SQL実践入門 2,580 2 リーダブルコード 2,592 3 プログラマのためのSQL 4,200 4 データベースリファクタリング 3,000 日付が変わるタイミングで消費税率 を5%から8%に変えることで対応 消費税率*商品.価格*カート.個数=売上金額 カート 売上 設定マスタ 商品
  5. 22.

    売上id 売上金額 売上日 配送状態 1 27,579 2014-03-31 23:59:59 配送済み 2

    6,480 2014-04-01 00:00:00 発注中 ︙ ︙ ︙ ︙ 名前 値 消費税率 0.08 売上id 商品id 個数 購入者 1 1 2 sone 1 2 3 sone 1 3 3 sone 2 4 3 sone ︙ ︙ ︙ ︙ 商品id 商品名 価格 1 SQL実践入門 2,580 2 リーダブルコード 2,592 3 プログラマのためのSQL 4,200 4 データベースリファクタリング 3,000 商品id=1の個数を3から2へ カート 売上 設定マスタ 商品 再計算
  6. 24.

    売上id 売上金額 売上日 配送状態 1 27,579 2014-03-31 23:59:59 配送済み 2

    6,480 2014-04-01 00:00:00 発注中 ︙ ︙ ︙ ︙ 名前 値 消費税率 0.08 売上id 商品id 個数 購入者 1 1 2 sone 1 2 3 sone 1 3 3 sone 2 4 3 sone ︙ ︙ ︙ ︙ 商品id 商品名 価格 1 SQL実践入門 2,580 2 リーダブルコード 2,592 3 プログラマのためのSQL 4,200 4 データベースリファクタリング 3,000 カート 売上 設定マスタ 商品 売上id=1の返品処理をする場合、 カートの値のみを変更して再計算 すると、本来有るべき値から誤差 が生まれる
  7. 25.

    失われた事実 • 最初の計算 (2,580 × 3 + 2,592 × 3

    + 4,200 × 3) × 1.05 = 29,522円 • 返品で個数が、日付で消費税率が変わる (2,580 × 2 + 2,592 × 3 + 4,200 × 3) × 1.08 = 27,579円 • 本来あるべき計算 (2,580 × 2 + 2,592 × 3 + 4,200 × 3) × 1.05 = 26,813円
  8. 27.

    商品id 商品名 価格 1 SQL実践入門 2,580 2 リーダブルコード 2,592 3

    プログラマのためのSQL 4,200 過去の事実と不整合が生まれてしまう 商品id 商品名 価格 1 SQL実践入門 第二版 2,480 2 リーダブルコード 2,592 3 プログラマのためのSQL 4,200 過去の事実(値)が失われる
  9. 31.

    アンチパターンを防ぐには? 消費税率 有効日 失効日 0.05 1997-04-01 2014-03-31 0.08 2014-04-01 null

    消費税率に履歴を持たせる ・消費税率テーブルを新規に作る ・有効期限をもたせることで、 売上日から消費税率を遡ることができる ・自動切り替えにも対応出来る
  10. 32.

    アンチパターンを防ぐには? 購入時の消費税率の履歴を持たせる 売上id 売上金額 消費税率 売上日 配送状態 1 29,522 0.05

    2014-03-31 23:59:59 配送済み 2 6,480 0.08 2014-04-01 00:00:00 発注中 ︙ ︙ ︙ ︙ ︙ ・売上テーブルに購入時の消費税率を持たせる ・消費税率テーブルがない場合、 この列が無いと返品処理が出来ない
  11. 41.
  12. 48.

    非正規化の例 postgres=# SELECT * FROM reserve; id | party_di |

    申込者 | 同伴者1 | 同伴者2 | 同伴者3 | 予約日 ----+----------+--------+---------+---------+---------+---------------------------- 1 | 1 | hoge | fuga | | | 2019-01-14 13:54:14.517217 2 | 2 | foo | bar | hoge1 | hoge2 | 2019-01-14 13:54:14.517217 3 | 1 | hoge2 | fuga | | | 2019-01-14 13:54:14.517217 4 | 1 | test | | test | | 2019-01-14 13:54:14.517217 (4 rows) -- 同伴者fugaを検索するクエリ postgres=# SELECT * FROM reserve WHERE ("同伴者1"='fuga' OR "同伴者2"='fuga' OR "同伴者3"='fuga’); id | party_di | 申込者 | 同伴者1 | 同伴者2 | 同伴者3 | 予約日 ----+----------+--------+---------+---------+---------+---------------------------- 1 | 1 | hoge | fuga | | | 2019-01-14 13:54:14.517217 3 | 1 | hoge2 | fuga | | | 2019-01-14 13:54:14.517217 (2 rows)
  13. 49.

    非正規化の例 postgres=# SELECT * FROM reserve; id | party_di |

    申込者 | 同伴者1 | 同伴者2 | 同伴者3 | 予約日 ----+----------+--------+---------+---------+---------+---------------------------- 1 | 1 | hoge | fuga | | | 2019-01-14 13:54:14.517217 2 | 2 | foo | bar | hoge1 | hoge2 | 2019-01-14 13:54:14.517217 3 | 1 | hoge2 | fuga | | | 2019-01-14 13:54:14.517217 4 | 1 | test | | test | | 2019-01-14 13:54:14.517217 (4 rows) -- 同伴者fugaを検索するクエリ postgres=# SELECT * FROM reserve WHERE ("同伴者1"='fuga' OR "同伴者2"='fuga' OR "同伴者3"='fuga’); id | party_di | 申込者 | 同伴者1 | 同伴者2 | 同伴者3 | 予約日 ----+----------+--------+---------+---------+---------+---------------------------- 1 | 1 | hoge | fuga | | | 2019-01-14 13:54:14.517217 3 | 1 | hoge2 | fuga | | | 2019-01-14 13:54:14.517217 (2 rows) 更新の場合など、バグなどでデータが壊 れることが容易に想像できる
  14. 60.

    非正規化の例 postgres=# SELECT * FROM アンケート; id | 回答者 |

    好きなデータベース | その他 ----+----------+--------------------+-------- 1 | soudai | PostgreSQL | 2 | sone | MySQL | 3 | taketomo | OracleDB | SQLite ←不整合 4 | test | SQL Server | 5 | hoge | その他 | Db2 id=3の「その他」列に不正な値 データが壊れるのは正規化出来ていない証拠
  15. 61.

    正規化の例 postgres=# SELECT * FROM アンケート; id | 回答者 |

    好きなデータベース ----+----------+------------------- 1 | soudai | PostgreSQL 2 | sone | MySQL 3 | taketomo | OracleDB 4 | test | SQL Server 5 | hoge | Db2 postgres=# SELECT * FROM データベースの種類; id | DBの種類 | 回答 ----+------------+------------------- 1 | PostgreSQL | PostgreSQL 2 | MySQL | MySQL 3 | OracleDB | OracleDB 4 | SQL Server | SQL Server 5 | DB2 | その他 6 | SQLite | その他 正規化の例 アンケートテーブルの好きなデータベース列は、 データベースの種類テーブルを親とした 外部キー制約を作る
  16. 63.

    CHECK制約の活用例 -- 想定したデータの登録 postgres=# INSERT INTO enquete ("id", "回答者", "好きなデータベース",

    "その他") VALUES (1, 'soudai', 'PostgreSQL', ''),(2, 'sone', 'MySQL', ''); INSERT 0 2 -- 正しくその他を登録する postgres=# INSERT INTO enquete ("id", "回答者", "好きなデータベース", "その他") VALUES (3, 'taketomo', 'その他', 'Db2'); INSERT 0 1 postgres=# SELECT * FROM enquete; id | 回答者 | 好きなデータベース | その他 ----+----------+--------------------+-------- 1 | soudai | PostgreSQL | 2 | sone | MySQL | 3 | taketomo | その他 | Db2 (3 rows) 非正規化されたテーブル
  17. 64.

    CHECK制約の活用例 postgres=# CREATE TABLE enquete postgres-# ( postgres(# id serial

    NOT NULL , postgres(# "回答者" text NOT NULL, postgres(# "好きなデータベース" text NOT NULL, postgres(# "その他" text NOT NULL CHECK postgres(# (CASE postgres(# WHEN "好きなデータベース"!='その他' AND "その他"='' THEN TRUE postgres(# WHEN "好きなデータベース"='その他' AND "その他"!='' THEN TRUE postgres(# ELSE FALSE postgres(# END) postgres(# ); CREATE TABLE 「好きなデータベースの列」に”その他”が設定された時のみ、 「その他の列」にデータを投入できる
  18. 65.

    CHECK制約の活用例 -- 好きなデータベースがその他の時以外はエラーになる postgres=# INSERT INTO enquete ("id", "回答者", "好きなデータベース",

    "その他") VALUES (3, 'taketomo', 'OracleDB', 'SQLite’); ERROR: new row for relation "enquete" violates check constraint "enquete_check" DETAIL: Failing row contains (3, taketomo, OracleDB, SQLite). -- 好きなデータベースがその他の時に空白でもエラーになる postgres=# INSERT INTO enquete ("id", "回答者", "好きなデータベース", "その他") VALUES (3, 'taketomo', 'その他', ''); ERROR: new row for relation "enquete" violates check constraint "enquete_check" DETAIL: Failing row contains (3, taketomo, その他, ). CHECK制約で守られる
  19. 71.
  20. 83.
  21. 86.

    user_idを見てユーザを判断 if($this->is_admin($user->user_id)) { // 管理者用の処理へ } // user_id の先頭が 9

    だった場合は管理者 function is_admin($user_id) { $role_id = mb_substr($user_id, 0, 1); return ($role_id == 9) ; } idに状態が隠れている
  22. 87.

    閲覧のみユーザ用の処理を追加 $role_id = $this->get_role_id($user->user_id); if ($role_id == 9) { //

    管理者用の処理へ } elseif ($role_id == 8) { // 閲覧のみユーザの処理へ } function get_role_id($user_id) { return $role_id = mb_substr($user_id, 0, 1); } 仕様追加の度に複雑に
  23. 101.

    会員id 名前 900001 曽根 壮大 900002 曽根 徠楽 100003 曽根

    煌楽 100004 曽根 朔楽 idから権限の責務をカラムに分割する 会員id 名前 権限 1 曽根 壮大 管理者 2 曽根 徠楽 管理者 3 曽根 煌楽 一般ユーザ 4 曽根 朔楽 一般ユーザ 意味を含んだIDの改善例
  24. 107.
  25. 108.