はじめてのB2B SaaSデータモデリング in 吉祥寺.pm#18

Transcript

1. ͸͡Ίͯͷ B2B SaaS σʔλϞσϦϯά
   A1Aגࣜձࣾ
   ࠤʑ໦ Ԇ໵ @__mnc90
   

   View Slide

2. Name ࠤʑ໦ Ԇ໵
   Company A1Aגࣜձࣾ (ΤʔϫϯΤʔ)
   Career ৯΂ϩά → Speee → A1AΛڞಉ૑ۀ
   URL twitter.com/__mnc90
   manchose.hatenablog.jp
   About ٕज़ͷ੹೚ऀͱͯ͠RFQΫϥ΢υͷσʔλ
   ϕʔεઃܭͱΠϯϑϥߏஙɺ૊৫ͮ͘Γͳ
   ͲΛ୲౰
   

   View Slide

3. B2BͷऔҾΛϫϯϥϯΫ্ʹ
   Vision
   

   View Slide

4. B2BͷऔҾΛϫϯϥϯΫ্ʹ
   Vision
   A1A
   

   View Slide

5. ΍ͬͨ΄͏͕Α͍͜ͱͳΜͯ
   ΄ͱΜͲͰ͖ͳ͍ͱ֮ޛ͢Δ
   ΍ͬͨ΄͏͕Α͍͜ͱͷ5%
   ͘Β͍͔͠Ͱ͖ͳ͍ͱߟ͑Δ
   ελʔτΞοϓͷγεςϜ։ൃ
   

   View Slide

6. ͦΕͰ΋ఘΊͨΒμϝͳ෦෼ʹͷΈूத͢Δ
   ελʔτΞοϓͷγεςϜ։ൃ
   

   View Slide

7. Horizontal SaaSΑΓɺ͞Βʹਂ͍υϝΠϯ஌͕ࣝඞཁ
   Vertical SaaS
   

   View Slide

8. Vertical SaaS ۀքಛԽܕͷSaaSɻoctͳͲʢݐઃϓϩδΣΫτ؅ཧSaaSʣ
   Horizontal SaaS ৬छɾػೳʹಛԽͨ͠SaaSɻSalesforce΍SmartHRͳͲ
   Vertical SaaSͱHorizontal SaaS
   

   View Slide

9. “DBͷण໋͸ΞϓϦέʔγϣϯΑΓ௕͍”
   @soudai1025 ͞Μ
   DBͷॏཁੑ
   

   View Slide

10. ΍ͬͨ΄͏͕ྑͦ͞͏ͳ͜ͱ
    TDD
    IaC
    GraphQL
    React
    TypeScript
    Serverless
    Kubernetes
    GitOps
    Apex
    Atomic
    Design
    DDD
    σʔλ
    ϞσϦϯά
    

    View Slide

11. TDD
    IaC
    GraphQL
    React
    TypeScript
    Serverless
    Kubernetes
    GitOps
    Apex
    Atomic
    Design
    DDD
    σʔλ
    ϞσϦϯά
    ΍ͬͨ΄͏͕ྑͦ͞͏ͳ͜ͱ
    

    View Slide

12. ͸͡ΊͯͷB2B SaaSͷσʔλϞσϦϯά
    

    View Slide

13. ੡଄ۀߪങ෦໳޲͚ݟੵࠪఆͷSaaS
    RFQΫϥ΢υ
    Product
    

    View Slide

14. View Slide

15. 1 Multi Tenancyͳςʔϒϧઃܭ
    2 جຊNullΛڐՄ͠ͳ͍
    3 Πϕϯτͷநग़ൣғΛυϝΠϯͷத৺ʹߜΔ
    4 ֎෦Ωʔͷ࣋ͪํ͔ΒσʔλͷϥΠϑαΠΫϧΛߟ͑Δ
    

    View Slide

16. 1. Multi Tenancyͳςʔϒϧઃܭ
    

    View Slide

17. “ಉ͡γεςϜ΍αʔϏεΛෳ਺ͷޓ͍ʹແؔ܎ͳར༻
    ऀ͕ؒڞಉͰར༻͢ΔํࣜͰɺͦΕͧΕͷར༻ऀ͸Ξ
    Χ΢ϯτ໊ͳͲͷࢿ֨৘ใΛݩʹ۠ผ͞ΕɺͦΕͧΕ
    γεςϜ্ʹઐ༻ͷྖҬ΍ઃఆͳͲ͕༩͑ΒΕΔɻ”
    Multi Tenancy
    ग़ల e-words.jp/w/Ϛϧνςφϯτ.html
    

    View Slide

18. Ϣʔβʔاۀ͔Β͢Δͱࣗ෼͕γεςϜΛಠ઎ར༻͍ͯ͠ΔΑ͏ʹݟ͑Δ
    ࣮ମ͸Single Source
    ෳ਺ͷςφϯτ͕1ͭͷγεςϜʹ૬৐Γ
    

    View Slide

19. Multi Tenancyͷํ๏
    1 ςφϯτຖʹDatabase or SchemaΛ෼཭
    2 શςʔϒϧʹtenant_idΧϥϜΛ༻ҙ
    

    View Slide

20. Multi Tenancyͷํ๏
    1 ςφϯτຖʹDatabase or SchemaΛ෼཭
    2 શςʔϒϧʹtenant_idΧϥϜΛ༻ҙ
    migration࣌ؒͷര૿ͳͲεέʔϥϏϦςΟʹ໰୊͋Γ
    

    View Slide

21. اۀؒऔҾܕSaaSͷMulti Tenancy
    ςφϯτΛͲͷ୯ҐͰ࡞੒͢Δ͔
    

    View Slide

22. αϓϥΠϠʔ͸όΠϠʔͷςφϯτʹॴଐ͢Δ
    όΠϠʔA όΠϠʔB όΠϠʔC
    αϓϥΠϠD αϓϥΠϠE αϓϥΠϠD αϓϥΠϠG αϓϥΠϠF
    αϓϥΠϠF αϓϥΠϠH
    tenant_id: 1 tenant_id: 2 tenant_id: 3
    

    View Slide

23. αϓϥΠϠʔ΋όΠϠʔ΋ผςφϯτʹ͢Δ
    όΠϠʔA όΠϠʔB όΠϠʔC
    αϓϥΠϠF
    tenant_id: 1 tenant_id: 2 tenant_id: 3
    tenant_id: 4 tenant_id: 5 tenant_id: 6
    αϓϥΠϠD αϓϥΠϠE
    

    View Slide

24. σʔλͷॴ༗ऀ͔Βςφϯτͷ୯ҐΛߟ͑Δ
    

    View Slide

25. Α͋͘Δཁ๬
    ୀձޙͷσʔλͷ෺ཧ࡟আ
    

    View Slide

26. αϓϥΠϠʔ͕ड৴ͨ͠ݟੵ৘ใ͸όΠϠʔͷୀձޙʹ࡟আ͞ΕΔ΂͖ʁ
    ϝʔϧΛϝλϑΝʔͱͯ͠ߟ͑Δ
    

    View Slide

27. ୀձޙ΋αϓϥΠϠʔ͕ड৴ͨ͠σʔλ͸࢒Δ΂͖
    

    View Slide

28. ςφϯτͷ୯Ґ
    1 αϓϥΠϠʔ͸όΠϠʔͷςφϯτʹॴଐ͢Δ
    2 αϓϥΠϠʔ΋όΠϠʔ΋ผςφϯτʹ͢Δ
    

    View Slide

29. ςφϯτͷ୯Ґ
    1 αϓϥΠϠʔ͸όΠϠʔͷςφϯτʹॴଐ͢Δ
    2 αϓϥΠϠʔ΋όΠϠʔ΋ผςφϯτʹ͢Δ
    

    View Slide

30. 1 όΠϠʔͷୀձޙͷσʔλ࡟আ͕όΠϠʔςφϯτ୯ҐͰ
    ࡟আ͢Δ͚ͩͰରԠ͕׬ྃ͢Δ
    2 اۀຖʹݸผͷΞΧ΢ϯτ؅ཧϙϦγʔΛద༻Ͱ͖Δ
    ※اۀຖʹϙϦγʔ͕ҟͳΔ͜ͱ͕ଟ͍
    3 αϓϥΠϠʔςφϯτ͕ॏෳ͠ͳ͍ͨΊαϓϥΠϠʔʹͱ
    ͬͯ΋σʔλͷҰݩ؅ཧ͕Ͱ͖Δͱ͍͏ϝϦοτΛఏڙͰ
    ͖Δ
    ϝϦοτ
    

    View Slide

31. Example
    ݟੵґཔ(request_for_quotations)ʹରͯ͠
    ݟੵճ౴(quotations)Λߦ͏৔߹
    

    View Slide

32. request_for_quotations
    ID produce_number
    1 300
    2 60
    3 1200
    ID unit_price request_for_quotation_id
    1 100 1
    2 90 2
    quotations
    ૉ๿ͳઃܭ
    

    View Slide

33. request_for_quotations
    ID produce_number
    1 300
    2 60
    3 1200
    ID unit_price received_request_for_quotation_id
    1 100 1
    2 90 2
    quotations
    σʔλͷॴ༗ऀΛߟ͑ͨઃܭ
    ID produce_number
    1 300
    2 60
    3 1200
    received_request_for_quotations
    ID unit_price request_for_quotation_id
    1 100 1
    2 90 2
    received_quotations
    

    View Slide

34. 2. جຊNULLΛڐՄ͠ͳ͍
    

    View Slide

35. NullableͳΧϥϜ͕͋Δ৔߹
    ϥΠϑαΠΫϧͷҟͳΔσʔλ͕
    ಉ͡ςʔϒϧʹೖ͍ͬͯΔ͜ͱ͕ଟ͍
    NULLΛڐՄ͠ͳ͍ཧ༝
    

    View Slide

36. Example
    

    View Slide

37. materials
    ID name code
    1 ϒϥέοτ 391001
    2 εϥΠυϘοΫε ϒϥέοτ NULL
    3 ݻఆۚ۩ 369221
    

    View Slide

38. materials
    ID name
    1 ϒϥέοτ
    2 εϥΠυϘοΫε ϒϥέοτ
    3 ݻఆۚ۩
    ID material_id code
    1 1 391001
    2 3 369221
    material_codes
    ͱΓ͋͑ͣNULL͸ແ͘ͳͬͨʂ
    Ͱ΋ɺ͜ΕͰ͍͍ͷ͔ʁ
    

    View Slide

39. ͳͥNULLؚ͕·ΕΔͷͩΖ͏
    

    View Slide

40. 1 material_codeΛͦ΋ͦ΋؅ཧ͍ͯ͠ͳ͍اۀ͕͋Δ
    2 ࢼ࡞඼ͷஈ֊Ͱ͸material_codeΛൃ൪ͤͣʹݟੵґཔ
    Λߦ͍ɺίετͷ֓ࢉΛ֬ೝ͔ͯ͠ΒcodeΛൃ൪͢Δͱ
    ͍͏ۀ຿ϑϩʔ͕͋Δ
    

    View Slide

41. 1 material_codeΛͦ΋ͦ΋؅ཧ͍ͯ͠ͳ͍اۀ͕͋Δ
    2 ࢼ࡞඼ͷஈ֊Ͱ͸material_codeΛൃ൪ͤͣʹݟੵґཔ
    Λߦ͍ɺίετͷ֓ࢉΛ֬ೝ͔ͯ͠ΒcodeΛൃ൪͢Δͱ
    ͍͏ۀ຿ϑϩʔ͕͋Δ
    ࢼ࡞඼ͷݟੵґཔۀ຿ͱ͍͏Ϣʔεέʔε͕ͬͦ͝Γൈ͚͍ͯΔ
    

    View Slide

42. materials
    ID name
    1 ϒϥέοτ
    2 εϥΠυϘοΫε ϒϥέοτ
    3 ݻఆۚ۩
    ID material_id code
    1 1 391001
    2 3 369221
    production_materials
    prototype_materials
    ID material_id
    1 2
    

    View Slide

43. ϦιʔεͱΠϕϯτΛ۠ผ͢Δɻ
    Πϕϯτ͸ۀ຿ͷه࿥Ͱ͋Γɺ
    B2B SaaSͷத৺తଘࡏɻ
    3. Πϕϯτͷநग़ൣғΛυϝΠϯͷத৺ʹߜΔ
    

    View Slide

44. ͔͠͠ɺ͋ΒΏΔϦιʔεʹର͢Δߋ৽ΛΠϕ
    ϯτͱͯ͠ϞσϦϯάͯ͠͠·͏ͱ࣮૷ίετ
    ͕ߴ͘ͳͬͯ͠·͏ɻ
    ͦͷͨΊɺղܾ͍ͨ͠υϝΠϯͷத৺ʹҐஔ͢
    Δۀ຿ͷΈΠϕϯτͱͯ͠ϞσϦϯά͢Δɻ
    3. Πϕϯτͷநग़ൣғΛυϝΠϯͷத৺ʹߜΔ
    ※ @kawasima͞ΜͷൃදͰֶͼ·ͨ͠
    

    View Slide

45. Example
    ඼໨(materials)ͷߋ৽ʹ͍ͭͯߟ͑Δ
    

    View Slide

46. materials
    ID name updated_at
    1 ϒϥέοτA 2019-05-16 09:10:11
    2 ໶۩ 2019-05-17 12:51:02
    materialsςʔϒϧΛ௚઀
    ߋ৽͢Δ৔߹
    

    View Slide

47. ID name created_at material_id
    1 ϒϥέοτA 2019-05-16 09:10:11 1
    2 ϒϥέοτB 2019-05-26 11:42:11 1
    materials
    ID name
    1 ϒϥέοτ
    2 ໶۩
    material_updates
    ߋ৽Πϕϯτͷςʔϒϧ
    material_updatesΛ༻ҙ
    

    View Slide

48. ID name created_at material_id
    1 ϒϥέοτA 2019-05-16 09:10:11 1
    2 ϒϥέοτB 2019-05-26 11:42:11 1
    materials
    ID name
    1 ϒϥέοτ
    2 ໶۩
    material_updates
    Ռͨͯ͠ɺmaterialsͷߋ৽ۀ຿
    ͸RFQΫϥ΢υͷղܾ͢ΔυϝΠ
    ϯͷத৺ͳͷ͔ʁ
    

    View Slide

49. ID name created_at material_id
    1 ϒϥέοτA 2019-05-16 09:10:11 1
    2 ϒϥέοτB 2019-05-26 11:42:11 1
    materials
    ID name
    1 ϒϥέοτ
    2 ໶۩
    material_updates
    ҧ͏͸ͣɻ
    ͋͘·Ͱmaterialsͷߋ৽ཤྺͷ؅ཧ͸جװγε
    ςϜ(ERP)Ͱߦ͍ͬͯΔɻ
    ͳΒɺmaterialsΛ௚઀ߋ৽ͯ͠͠·ͬͯྑ͍ɻ
    ΋͠CS༻ʹཤྺ͕΄͔ͬͨ͠৔߹͸ผͷσʔλ
    ετΞʹೖΕΔͳΓ͢Ε͹Α͍ɻ
    

    View Slide

50. ֎෦ΩʔΛ࣋ͭͱ͍͏͜ͱ͸ࢀরઌ
    ͷσʔλ͕ొ࿥͞Εͳ͍ݶΓɺࢀরݩ
    ͷσʔλΛొ࿥Ͱ͖ͳ͍ͱ͍͏͜ͱɻ
    4. ֎෦Ωʔͷ࣋ͪํ͔ΒσʔλͷϥΠϑαΠΫϧΛߟ͑Δ
    

    View Slide

51. ֎෦Ωʔͷ࣋ͪํΛؒҧ͑ΔͱΞϓϦέʔγϣ
    ϯ͔Βͷσʔλొ࿥ॲཧ͕ෳࡶʹͳͬͨΓɺ
    Ϣʔβʔͷۀ຿ʹଇ͞ͳ͍ػೳʹͳΔ͜ͱͰ
    ϢʔβϏϦςΟͷ௿ԼΛট͘͜ͱʹͳΔɻ
    ֎෦Ωʔͷ࣋ͪํͷॏཁੑ
    

    View Slide

52. Example
    ݟੵґཔ(request_for_quotations)ʹਤ໘
    (diagram_images)Λఴ෇ͯ͠ૹ৴͢Δɻ
    

    View Slide

53. diagram_images
    ID name path request_for_quotation_id
    1 ਤ໘_ver1 /images/xxxx/sample.jpeg 1
    2 ਤ໘_ver2 /images/xxxx/sample2.jpeg 1
    3 ਤ໘_ver3 /images/xxxx/sample3.jpeg 2
    request_for_quotations
    ID produce_number
    1 300
    2 60
    

    View Slide

54. diagram_images
    ID name path request_for_quotation_id
    1 ਤ໘_ver1 /images/xxxx/sample.jpeg 1
    2 ਤ໘_ver2 /images/xxxx/sample2.jpeg 1
    3 ਤ໘_ver3 /images/xxxx/sample3.jpeg 2
    request_for_quotations
    ID produce_number
    1 300
    2 60
    diagram_iamges͸ඞͣ
    request_for_quotationsͷ࡞
    ੒ޙʹొ࿥͢Δʁ
    

    View Slide

55. ۀ຿Λߟ͑Δ
    1 ݟੵґཔ࡞੒લʹઃܭ෦໳͔Βਤ໘ϑΝΠϧ͚ͩڞ༗͞Ε
    Δͱ͍͏Ϣʔεέʔε
    2 ਤ໘ϑΝΠϧΛ౎౓ΞοϓϩʔυͤͣʹϚελʔͱͯ͠ొ
    ࿥͓͖ͯ͠ɺݟੵґཔ࣌ʹ͸ͦΕΛࢀরར༻͢Δͱ͍͏Ϣ
    ʔεέʔε
    

    View Slide

56. ۀ຿Λߟ͑Δ
    1 ݟੵґཔ࡞੒લʹઃܭ෦໳͔Βਤ໘ϑΝΠϧ͚ͩڞ༗͞Ε
    Δͱ͍͏Ϣʔεέʔε
    2 ਤ໘ϑΝΠϧΛ౎౓ΞοϓϩʔυͤͣʹϚελʔͱͯ͠ొ
    ࿥͓͖ͯ͠ɺݟੵґཔ࣌ʹ͸ͦΕΛࢀরར༻͢Δͱ͍͏Ϣ
    ʔεέʔε
    ඞͣ͠΋ݟੵґཔ࡞੒ޙʹਤ໘Λొ࿥͢Δͱ͍͏Θ͚Ͱ͸ͳ͍
    

    View Slide

57. diagram_images
    ID name path
    1 ਤ໘_ver1 /images/xxxx/sample.jpeg
    2 ਤ໘_ver2 /images/xxxx/sample2.jpeg
    3 ਤ໘_ver3 /images/xxxx/sample3.jpeg
    request_for_quotations
    ID produce_number
    1 300
    2 60
    ID diagram_image_id request_for_quotation_id
    1 1 1
    2 2 1
    3 3 2
    attached_diagram_images
    diagram_imagesͱ
    request_for_quotationsͷ
    σʔλొ࿥͸ॱෆಉʹͳͬͨ
    

    View Slide

58. υϝΠϯΤΩεύʔτ͔Βͷ৘ใͷҾ͖ग़͠ํ
    ൪֎ฤ
    

    View Slide

59. 1 ୟ͖୆ͱͳΔERDΛ࡞Δ
    2 ERDͷؔ࿈͝ͱʹυϝΠϯʹదͨ͠ྫΛ༻͍ͳ͕Β࣮ମΛ
    ൓өͰ͖͍ͯΔ͔υϝΠϯΤΩεύʔτʹ֬ೝ͢Δ
    

    View Slide

60. Example
    ֎෦Ωʔͷ࣋ͪํɺNULLͷՄೳੑɺςʔϒϧ
    ͷؔ࿈(1:n/1:1/n:n)ʹ͍ͭͯ1ͭͣͭྫΛग़
    ͠ͳ͕Βʮ○○͕ෳ਺ʹͳΔ͜ͱͬͯ͋Γ·͢
    ͔ʁʯͱ࣭໰͍ͯ͘͠ɻ
    

    View Slide

61. Example
    diagram_images
    ID path request_for_quotation_id
    1 /images/xxxx/sample.jpeg 1
    2 /images/xxxx/sample2.jpeg 1
    3 /images/xxxx/sample3.jpeg 2
    request_for_quotations
    ID produce_number
    1 300
    2 60
    

    View Slide

62. Example
    diagram_images
    ID path request_for_quotation_id
    1 /images/xxxx/sample.jpeg 1
    2 /images/xxxx/sample2.jpeg 1
    3 /images/xxxx/sample3.jpeg 2
    request_for_quotations
    ID produce_number
    1 300
    2 60
    ਤ໘ϑΝΠϧͬͯݟੵґཔʹରͯ͠ෳ਺
    ఴ෇͢Δ͜ͱ͸͋Γ·͔͢ʁ
    me
    

    View Slide

63. Example
    diagram_images
    ID path request_for_quotation_id
    1 /images/xxxx/sample.jpeg 1
    2 /images/xxxx/sample2.jpeg 1
    3 /images/xxxx/sample3.jpeg 2
    request_for_quotations
    ID produce_number
    1 300
    2 60
    ݟੵґཔΛ࡞੒͢Δલʹਤ໘ϑΝΠϧ͚ͩ
    ଞ෦໳͔Βڞ༗͞ΕΔ͜ͱ͸͋Γ·͔͢ʁ
    me
    

    View Slide

64. υϝΠϯΤΩεύʔτ΁ͷґཔͷ࢓ํ
    υϝΠϯΤΩεύʔτ͔Β͢ΔͱɺʮͳΜͰͦ
    ΜͳʹΤοδέʔε·Ͱؾʹ͢ΔΜʁʯͱ͍͏
    ؾ࣋ͪʹͳΓ΍͍͢ɻ
    ͔ͩΒɺʮؾ෇͍ͯͳ͍Τοδέʔε·Ͱؾͮ
    ͚Δ͜ͱ͕ࠓޙͷγεςϜ։ൃͷεϐʔυΛ଎
    ͘͢Δʯͱ͍͏͜ͱΛ఻͑ͯɺ͍ΖΜͳέʔε
    ͷ૝૾Λ๲Β·ͤͯ΋Β͏Α͏ʹ͢Δɻ
    

    View Slide

65. We are hiring!
    

    View Slide