Upgrade to Pro — share decks privately, control downloads, hide ads and more …

【社内勉強会用】SQLアンチパターン / SQL Antipatterns study

VTRyo
April 12, 2020

【社内勉強会用】SQLアンチパターン / SQL Antipatterns study

2020/4/13 第5章 エンティティ・アトリビュート・バリュー

VTRyo

April 12, 2020
Tweet

More Decks by VTRyo

Other Decks in Programming

Transcript

  1. d753ZPQSFTFOUBUJPO NBTUFS WUSZPDUMEFTDSJCFQSFTFOUBUJPO
    d753ZPQSFTFOUBUJPO NBTUFS QSFTFOUBUJPOTUBSUFE
    42-Ξϯνύλʔϯ
    ୈষ
    ΤϯςΟςΟɾΞτϦϏϡʔτɾόϦϡʔ
    ʢ˞ࣾ಺ษڧձൃදࢿྉʣ

    View Slide

  2. d753ZPQSFTFOUBUJPO NBTUFS 5XJUUFS!T@IW
    ˞஫ҙ఺
    w ຊࢿྉ͸ࣾ಺ษڧձͷ՝୊Ͱ࡞੒ͨ͠΋ͷͰ͢
    w l42-ΞϯνύλʔϯzΛࢀߟʹ࡞੒͍ͯ͠·͢
    w ͸͡ΊͯຊॻΛಡΜͰ͍·͢

    View Slide

  3. d753ZPQSFTFOUBUJPO NBTUFS 5XJUUFS!T@IW
    &"7ͱ͸
    w ΤϯςΟςΟɾΞτϦϏϡʔτɾόϦϡʔ

    ʢఆٛର৅ʣɹɹɹʢଐੑʣɹɹʢ஋ʣ
    Q

    View Slide

  4. d753ZPQSFTFOUBUJPO NBTUFS 5XJUUFS!T@IW
    Ξϯνύλʔϯߏ੒
    Α͘ݟͨΒ
    ΢ϯνύλʔϯʹͳͬͯ·ͨ͠
    ൚༻తͳଐੑςʔϒϧΛ࢖༻ͪ͠Ό͏
    ଐੑΛߦʹ֨ೲͪ͠Ό͏
    Q

    View Slide

  5. d753ZPQSFTFOUBUJPO NBTUFS 5XJUUFS!T@IW
    ଐੑऔಘ͢Δʹ͸
    ΫΤϦ͕ෳࡶʹͳΔ
    Q

    View Slide

  6. d753ZPQSFTFOUBUJPO NBTUFS 5XJUUFS!T@IW
    ඞਢଐੑΛઃఆͰ͖ͳ͍
    /05/6--੍໿Λએݴ͢Δ͚ͩͰ
    ຊ౰͸Α͔ͬͨͷʹʜ
    ଐੑ͕
    ߦʹ֨ೲ͞Ε͍ͯΔ
    BUUS@OBNFྻʹEBUF@SFQPSUFE͕ೖ͍ͬͯΔߦΛ
    JTTVF@JE͝ͱʹଘࡏͤ͞Δ੍໿͕Ϝζ͍
    ೔ผʹ·ͱΊͯϨϙʔτΛ࡞Γ͍ͨ
    ˠʮEBUF@SFQPSUFEͰूܭ͠Αʯ
    Q

    View Slide

  7. d753ZPQSFTFOUBUJPO NBTUFS 5XJUUFS!T@IW
    σʔλܕΛ࢖͑ͳ͍
    %"5&ܕͰྻΛఆٛ͢Ε͹͍͍
    ͚ͩͳͷʹʜ
    σʔλܕ
    ͕จࣈྻ
    ݸผͷBUUS@WBMVFྻΛఆٛ͢Δ͜ͱ΋͋Δ͕ʜ
    ΫΤϦ͸௒ෳࡶԽ͢Δ
    ೔ผʹ·ͱΊͯϨϙʔτΛ࡞Γ͍ͨ
    ˠʮEBUF@SFQPSUFEͰूܭ͠Αʯ
    Q

    View Slide

  8. d753ZPQSFTFOUBUJPO NBTUFS 5XJUUFS!T@IW
    ࢀর੔߹ੑͷڧ੍ෆՄ
    '03&*(/,&: TUBUVT

    3&'&3&/$&4#VH4UBUVT TUBUVT

    Ͱ͍͍ͷʹʜ
    શߦʹ
    ద༻͞Εͯ͠·͏
    '03&*(/,&: BUUS@WBMVF

    3&'&3&/$&4#VH4UBUVT TUBUVT

    TUBUVTଐੑͷ஋͸#VH4UBUVTςʔϒϧ͕
    ֨ೲ͍ͯ͠Δ஋ʹ͍ͨ͠
    Q

    View Slide

  9. d753ZPQSFTFOUBUJPO NBTUFS 5XJUUFS!T@IW
    *TTVFΛҰߦͰऔಘ
    ɾ࠶ߏங͕ඞཁ
    ɾଐੑ਺૿Ճʹ݁߹ͷ૿Ճ
    Q

    View Slide

  10. d753ZPQSFTFOUBUJPO NBTUFS 5XJUUFS!T@IW
    ೏͏Ξϯνύλʔϯ
    Q

    View Slide

  11. d753ZPQSFTFOUBUJPO NBTUFS 5XJUUFS!T@IW
    ΞϯνύλʔϯͰ΋͍͍ྫ
    Q

    View Slide

  12. d753ZPQSFTFOUBUJPO NBTUFS 5XJUUFS!T@IW
    &"7Λճආ͠Α͏
    ̍ɽͻͱͭͷςʔϒϧʹ
    αϒλΠϓΛશ෦ೖΕΔ
    ̎ɽαϒλΠϓ͝ͱʹ
    ςʔϒϧΛ࡞੒͢Δ
    ̏ɽςʔϒϧΛΦϒδΣΫτࢦ޲ͷ
    ΫϥεʹݟཱͯͯܧঝΛ໛฿͢Δ
    ̐ɽ9.-΍+40/ܗࣜͰଐੑɾ஋
    Λڞʹ֨ೲ͢Δ
    ద੾ͳঢ়گͰͲΕ͔Λબ୒ͯ͜͠͏
    Q

    View Slide

  13. d753ZPQSFTFOUBUJPO NBTUFS 5XJUUFS!T@IW
    ̍ɽγϯάϧςʔϒϧܧঝ
    w શͯͷଐੑΛ*TTVFͷݸผྻͱͯ֨͠ೲ͢Δ
    w "DUJWF3FDPSEͱ૬ੑΑ͖
    w αϒλΠϓͷ਺গͳ͍ͱ͖ʹ༗ޮ
    Q

    View Slide

  14. d753ZPQSFTFOUBUJPO NBTUFS 5XJUUFS!T@IW
    ̎ɽ۩৅ςʔϒϧܧঝ
    w #VHTςʔϒϧɺ'FBUVSF3FRVFTUTςʔϒϧͳͲαϒλΠ
    ϓ͝ͱʹ࡞੒͢Δ
    w ڞ௨͢ΔଐੑͱݸผͷଐੑΛ࣋ͭ
    w ͢΂ͯͷαϒλΠϓΛ·͍ͨͩݕࡧ͕গͳ͍ͱ͖༗ޮ
    w αϒλΠϓ͕ผςʔϒϧʹ͋ΓෳࡶԽ͢ΔͨΊ
    Q

    View Slide

  15. d753ZPQSFTFOUBUJPO NBTUFS 5XJUUFS!T@IW
    ̏ɽΫϥεςʔϒϧܧঝ
    w ͢΂ͯͷαϒλΠϓʹڞ௨͢ΔଐੑΛؚΉجఈςʔϒϧ
    ʢ*TTVFʣΛ࡞Δ
    w *TTVFςʔϒϧɺ#VHTςʔϒϧɺ'FBUVSF3FRVFTUT
    ςʔϒϧʹJTTVF@JEΛઃఆ͢Δ
    w ͢΂ͯͷαϒλΠϓʹڞ௨͢ΔྻΛࢀর͢ΔΫΤϦ͕ස
    ൟʹ࣮ߦ͞ΕΔͱ͖ʹ༗ޮ
    Q

    View Slide

  16. d753ZPQSFTFOUBUJPO NBTUFS 5XJUUFS!T@IW
    ̐ɽ൒ߏ଄Խςʔϒϧ
    w සൟʹଐੑΛ௥Ճ͢Δέʔε͸-0#ܕʢ-BSHF0CKFDUʣ
    Λ௥Ճ͠+40/ͳͲͷܗࣜͰ֨ೲ͢Δ
    w ͲΜͳଐੑͰ΋֨ೲͰ͖ΔͷͰ֦ுੑ͕ߴ͍
    w ಛఆͷଐੑʹΞΫηε͢Δखஈ͕ͳ͘ɺΞϓϦέʔγϣϯ
    ίʔυͰ෼ղ͢Δඞཁ͕͋Δܽ఺
    w αϒλΠϓͷ਺੍͕ݶͰ͖ͳ͍ɺଐੑఆٛͷॊೈੑ͕ඞཁ
    ͳ৔߹ʹ༗ޮ
    Q

    View Slide

  17. d753ZPQSFTFOUBUJPO NBTUFS 5XJUUFS!T@IW
    ·ͱΊ
    w 42-͸ɺҟͳΔଐੑ͸ҟͳΔྻʹ֨ೲ͢Δํ๏ཱ͕֬͞
    Ε͍ͯΔ
    w &"7ͷ࠾༻͸ɺଐੑΛࣝผ͢ΔͨΊͷ৽ͨͳํ๏Λ42-
    ্ʹߏங͢Δ͜ͱΛҙຯ͢Δ
    w ʮϝλσʔλ͸ɺϝλσʔλͷͨΊʹ༻͍·͠ΐ͏ʯ
    Q

    View Slide

  18. d753ZPQSFTFOUBUJPO NBTUFS WUSZPDUMEFTDSJCFQSFTFOUBUJPO
    d753ZPQSFTFOUBUJPO NBTUFS QSFTFOUBUJPOTUBSUFE
    42-Ξϯνύλʔϯ
    ୈষ
    ΤϯςΟςΟɾΞτϦϏϡʔτɾόϦϡʔ
    ʢ˞ࣾ಺ษڧձൃදࢿྉʣ
    ׬

    View Slide