📐

RDB で `択一` を表現する

に公開
Database
ER 図
tech

要件

  • システムはユーザーを持つ
  • ユーザーは複数の住所を持つ
  • ユーザーが持つ複数の住所のうち、高々一つの住所は請求先住所とする

この要件に従って ERD の設計をしたい

愚直に定義してみる

  1. 請求先住所を変更したい場合、User がもつ Address を総なめして、元々 true のものは false に変える必要がある
  2. is_billing が true のものが 高々１つ であることが表現できていない。つまり、請求先住所が2つ以上存在することが ERD の定義上可能になっている

is_billing を独立させてみる

これにより

  1. 請求先住所を変更したい場合、User がもつ Address を総なめして、元々 true のものは false に変える必要がある
  2. is_billing が true のものが 高々１つ であることが表現できていない。つまり、請求先住所が2つ以上存在することが ERD の定義上可能になっている

の 1 が解消されました。
請求先住所を変えたい場合は、BillingAddress を1行変更すれば済みます。

ただし 2 は解消されていません。
BillingAddress は、一つの Address が複数の BillingAddress として登録されることを防ぐ制約は定義できていますが、

BillingAddress に user.id を持たせてみる

これにより

  1. 請求先住所を変更したい場合、User がもつ Address を総なめして、元々 true のものは false に変える必要がある
  2. is_billing が true のものが 高々１つ であることが表現できていない。つまり、請求先住所が2つ以上存在することが ERD の定義上可能になっている

の 2 が解消されました。BillingAddress がもつ user.id は UNIQUE なので、請求先はユーザーごとに高々1つしか持てません。
1　は元から解消されています。

これで万事解決ですね

・・・本当にそうでしょうか？

第三正規形に違反してしまっている

第三正規形とは

非キー属性が主キーに対して推移的に従属していない（推移的従属性がない）こと。

推移的従属性 とは・・・
主キーではない属性 \mathbf{A} が、同じテーブル内の主キーではない別の属性 \mathbf{B} に従属し、さらにその属性 \mathbf{B} が非キー属性 \mathbf{C} を決定する関係（\mathbf{A} \rightarrow \mathbf{B} かつ \mathbf{B} \rightarrow \mathbf{C} のとき、 \mathbf{A} \rightarrow \mathbf{C} となる）を指します。

BillingAddress は user_id と address_id の2つの FK を持ちます。ここで BillingAddress が user_id を持っていることが第三正規形に違反しています。

  1. ある BillingAddress を特定するということは
  2. その BillingAddress は必ず1つの Address に紐づいているので、Address が特定できます
  3. Address は必ず1つの User に紐づいているので、User が特定できます

総じて

  • BillingAddress -> Address -> User
  • BillingAddress の id がわかれば、所有者の User の id がわかる

ということになります。
つまり、BillingAddress は Address の id を持つだけで推論的に User の id がわかるにも関わらず、User の id を持っています。

これが 第三正規形に違反してしまっている 所以です。

第三正規形に違反しているということは、データ不整合を許す設計になっていることを意味します。

具体的には、BillingAddress のテーブルだけに着目したとき、A さんの請求先住所として B さんの請求先住所を指定することが可能になってしまいます。

「現実世界のモノとモノの関係性の大抵は ERD で表現できるはず」

というのが私の信条なので、このままでは納得できません。

・・・ではどうすればいいのでしょうか？

HDR-DTL パターンを用いてみてはどうか

これにより第三正規系を保った上で要件を表現することができました。
イメージ的な考え方としては

  • 住所台帳(address)のある１ページはあるユーザーが所有する住所が載っているとして
  • そのページの一番上(addressHeader)に、どの住所がユーザーの請求先かの id が記載されている

ということです。
AddressHeader.billing_address_id は null を許容するので、「高々一つ の住所は請求先住所とする」という点も把握しやすくなりましたね。

ただし、この設計でも場合によっては欠点が生まれます。

全ての設計はトレードオフです

今回は 住所 - 請求先住所 という関係性を例に出したので、「請求先住所はユーザーがもつ住所の中から択一」というのはプロジェクトが成長してもまず不変でしょう。

ただ、仮に「請求先住所はユーザーがもつ住所の中から複数選択される」ようになった場合、 ##is_billing を独立させてみる での設計の方が却ってよくなるでしょう。

コンポジションは必要なのか

ふと、こう思ったひともいるかもしれません。

これでもよくね？と。

結論、この主張に対する完全な反論は私は持ち合わせていません。
確かにその通りだと思います。
「プロジェクトによってはそれでもいい」
と答えます。

ただ、将来的なシステムの拡張性を考えた時、

  • User テーブルには User と関係が密接なデータを持つべき

です。「住所」は確かに User と関係が強いデータではあるのですが、name や birthday 等と比較すると、関係が弱いのではないでしょうか。

もしそのことに同意されるのであれば、User と AddressHeader を分けることについても同意がいただけるのではないかと思います。

まとめ

DB の設計って難しいのよ

Discussion