Q&A
プロジェクトや要件次第なので、他者がとやかく言える範囲ではないような
先生の作った授業を生徒が予約するようなサービスがあるとして、以下のようにテーブル設計しました。
teachers(id,name) lessons(id,name,teacher_id) students(id,name) lesson_reservations(id,lesson_id,student_id)
業務要件として、lesson_reservationsを取得する際には多くの場合でteachers.nameも取得したくなるとします。
その場合、上記のテーブル定義だとlesson_reservationsを取得する際にいちいちlessonsをjoinしてteachers.nameを取得しなくてはなりません。
テーブル定義としては冗長にはなりますが、lesson_reservationsにteacher_idにすれば取得しやすいです。
もちろん、lessons.teacher_idが変更になるとlesson_reservations.teacher_idも変更が必要というデメリットはあります。
しかし、業務要件にこのような変更がないと考えた場合は、あえて冗長なテーブル設計をしてもよいのかなと思っています。
「このような場合は冗長な外部キーを持って良い」みたいに明記された設計パターンやその他情報があれば教えてほしいです。
ありがとうございます！
もちろんそうですよね！
> 「このような場合は冗長なキーを持って良い」みたいに明記されたルールや情報
があれば自信を持てるなと思った次第です！
実プロジェクトの情報が外に出ることはないですし、想定のとおり動作していて不具合がないのなら文句はないのではと。
「好み」だけを語られても困りますよね？
>「好み」だけを語られても困りますよね？
そうですね。好みではなくて、こういう設計パターンがあるみたいな情報がほしいです。
わかりづらくてすみません。文章を修正しました。
ありがとうございました。
「キー」と書かれると主キーや一意制約や外部キーのことを思い浮かべますが、もしかして単なるカラムのことでしょうか？
冗長なカラムを持つこと自体は場合によってはありかもしれませんが、キーに余計なものを含めると意味が変わってしまいます。
ありがとうございます！
仰るとおり曖昧な表現でした。今回は冗長な外部キーについての質問になります。
質問を修正します。
ごめんなさい、言葉が足りなかったですね。「キーに余計なものを含める」というのは、一つの複合キーに余計なカラムを追加するという意味で書きました。
lesson_reservations テーブルに teacher_id カラムを追加して、teacher_id 単独で teachers テーブルへの外部キーにするなら、それは単なるカラムの追加と大差ありません。
が、lesson_reservations テーブルの lesson_id と teacher_id を組み合わせた外部キーで lessons テーブルの id と teacher_id の組み合わせを参照する形になると、意味が変わってしまうので…。
ありがとうございます！
その分類であれば、以下を想定しています。
>teacher_id 単独で teachers テーブルへの外部キーにするなら、それは単なるカラムの追加と大差ありません。
マスターテーブル（ディメンジョンともいう）とトランザクションテーブル（ファクトとも言う）を分けて考えるのは一つの手で、マスターからトランザクションに属性の値をコピーするのは有りです。ご質問の例ですとteachersとstudentsとlessonsはマスターで、lesson_reservationsはトランザクションテーブルです。例えばレッスンの単価がlessonsにあったとしたら、lesson_reservationsに単価を毎回コピーしないと、単価が変更になった時過去の履歴まで影響が出てしまいます。また、先生と生徒程度の人数なら問題はないでしょうけれど、オンラインショップとか膨大な数を扱うdbで全マスターと毎回JOINしないといけないならパフォーマンスは望めないでしょう。稼働開始以来の全先生と全生徒と全レッスンをマスターに永遠に残さないといけない設計もやめた方がいいです。
回答6件
0
一般論ですが、以下のことに留意してください。
設計時には将来におけるデータやコードの再利用は予測できない。(例えば2000年問題)
実装容易性、保守容易性(含仕様変更、再利用、データ追加)、性能はトレードオフの関係にある。
長期にわたって使うシステムを設計する場合には、以下のように設計していくべきです。
まず保守容易性を優先して設計しプロトタイプで性能計測する。
性能が遅すぎるなら、ハード予算を増やして対応する。
ハード予算が増やせないなら、保守容易性を少し落として性能を上げる。
実装容易性とか計測してもいない性能に惑わされて保守容易性を落とすと、後で後悔することが多いです。
投稿2021/12/06 05:24
総合スコア24670
0
正規化を崩す話なので、個々の崩し方について、それはいいとか悪いとかいう記述はまず見つからないでしょう。
「性能向上のためにあえて正規化を崩す」というような言い方であれば見つかるかと思います。
ただそれは、性能が出ないという場合の話なので、あなたの言っていることとはちょっと違いますが。
性能問題が無いという前提だと、
しかし、業務要件にこのような変更がないと考えた場合は、
今後の要件変更で、そういう変更があれば腹を切ってお詫びするくらいの覚悟（変更が無いという確信）があれば良いのでは無いでしょうか。
というのは大げさですが、性能問題にせよ違うにせよ「これこれの考えであえて正規化を崩した」という旨はドキュメントに残した方がいいでしょうね。
投稿2021/12/06 06:59
総合スコア85901
ありがとうございます！
もし正規化を崩すにしてもドキュメントを残すべきというのはそのとおりだと思いました。
また今回の理由では正規化を崩すべきではないと皆様の回答から理解できました。
0
ベストアンサー
その場合、上記のテーブル定義だとlesson_reservationsを取得する際にいちいちlessonsをjoinしてteachers.nameを取得しなくてはなりません。
テーブル定義としては冗長にはなりますが、lesson_reservationsにteacher_idにすれば取得しやすいです。
正規化する事より、取得する際のアプローチ（この場合はSQLの記述）が面倒というのが上回っているとしか思えません。
そんな理由で、正規化を緩める事は普通しません。
よくある正規化を緩めるパターンとしては、参照頻度の高い集計処理などを、集計結果として纏めて保持しておく場合です。
この場合、正規化を緩めず、都度集計の問い合わせを行う方が、全体的な記述は面倒ではありません。
投稿2021/12/07 00:26
総合スコア25327
ありがとうございます！
ご回答いただいたとおりだと思いました。
DBでは正規化されたデータで持ち、必要に応じてアプリケーションやビューや集計結果テーブルなどで、使いやすいように工夫するのが結論と理解しました。
当たり前の結論ですが、アプリケーションを中心に見てたら、もっとアプリケーション側から便利なテーブル設計ないかという考えに陥っていました。
DBとしてあるべき姿を再認識できたのでベストアンサーとさせていただきます。
0
命題の例ですと先生と授業が常に１対１であるという条件があれば
正規化の合理性はほとんどありませんが、先生が複数の授業をもっていたり
一つの授業に複数の先生がかかわったりするなら正規化はマストでしょう
一つ注意しておく必要があるのは正規化は構造上最適な保持の仕方であっても
必ずしも最速化な方法ではないということです
たとえば先生のパーソナルデータはteachersテーブルに保持すべきですが
着任10年以内の先生の授業を抽出したいとなると検索性だけを考えると
teachersとlessonsは別れていないほうが高速に設計できると思います
投稿2021/12/06 04:12
編集2021/12/06 04:16
総合スコア116724
ありがとうございます！
先生が複数の授業を持ちます。
一つの授業に複数の先生はかかわりません。
そのため正規化自体はマストだという前提とさせて下さい。
（説明がわかりづらくてすみません）
この場合に、冗長でない（lesson_idからlessons.teacher_idを取得できるのでカラムとして保持しない）
`lesson_reservations(id,lesson_id,student_id)`
とするか
あえて冗長な（lesson_idからlessons.teacher_idを取得できるのにあえてカラムとして保持する）
`lesson_reservations(id,lesson_id,student_id,teacher_id)`
とするか
が設計パターンのような手法としてあれば知りたいです。
> 一つ注意しておく必要があるのは正規化は構造上最適な保持の仕方であっても必ずしも最速化な方法ではないということです
確かにこれが結論な気もしますが、もう少し具体的な設計パターンがあると嬉しいなと思っています。
理解が間違っていたら申し訳ないですが
lesson_reservationsにteacher_idを含めるべきかということでしたら
含めるべきではありません。
たとえばなんらかの理由で途中から先生が変わる可能性もありますので
その際lessonsテーブルとlesson_reservationsの両方修正が必要になります
データ管理上のぞましくありません
ありがとうございます！
ご理解頂いている内容で齟齬ありません。
仰るとおり、先生が変わるとlessonsテーブルとlesson_reservationsの両方修正が必要になるのを理解し、それを許容した上で、あえて冗長に持たせる（lesson_reservationsにteacher_idを含める）設計パターンというのがあればいいなと思ったのですが。。
書いていて思いましたが、データは冗長にもたせずに、必要に応じてビューを作るのがいいのかなと言う気がしてきました。
0
変数名などの事柄は、
命名規則という分野になります。
下記記事が参考になるかと。
データベースオブジェクトの命名規約 - Qiita
私の知っている限り、
DBのテーブル、カラム名に関しては、記事の通りの、
小文字スネークケースで命名していることが多いように思います。
投稿2021/12/06 04:12
総合スコア9555
0
正規化をあえて崩すのも一つの正規化です。最終正規化ですね。
IDや氏名が変わることを前提とする人事システムなどでは、マスターの履歴化も考慮する必要があります。
そこまでしないが、その変更当時の氏名やIDを取得したいのであれば、冗長化する必要がありますね。逆にあくまで最新情報で閲覧したいのであれば冗長化はすべきではなく、さらにリレーションはIDではなく物理採番のキーで行う必要があります。
投稿2021/12/13 23:15
総合スコア10
あなたの回答
tips
プレビュー
