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

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

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