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

はじめてのB2B SaaSデータモデリング in Builderscon 2019

はじめてのB2B SaaSデータモデリング in Builderscon 2019

Avatar for sasaki nobuya

sasaki nobuya

August 30, 2019
Tweet

More Decks by sasaki nobuya

Other Decks in Programming

Transcript

  1. Name ࠤʑ໦ Ԇ໵ Company A1Aגࣜձࣾ (ΤΠϫϯΤΠ) Career ৯΂ϩά → Speee

    → A1AΛڞಉ૑ۀ URL twitter.com/__mnc90 manchose.hatenablog.jp About CTOͱͯ͠RFQΫϥ΢υͷσʔλϕʔεઃ ܭͱΠϯϑϥߏஙɺ૊৫ͮ͘ΓͳͲΛ୲౰ !2
  2. ΍ͬͨ΄͏͕ྑͦ͞͏ͳ͜ͱ TDD E2E GraphQL React TypeScript Serverless Kubernetes GitOps Design

    System Atomic Design DDD σʔλ ϞσϦϯά ஫ҙ) σʔλϞσϦϯάҎ֎΍͍ͬͯͳ͍Θ͚Ͱ͸ͳ͍Ͱ͢ !15
  3. Single Tenancy !31 Web DB Web DB ςφϯτA ςφϯτB ֤ࣾʢAɾBʣͦΕͧΕಠཱͨ͠ઐ༻ͷΠϯϑϥΛߏங


    B޲͚ͷγεςϜʹ͸͜ͷܗଶ͕ࠓ·Ͱଟ͔ͬͨ ஫ҙ) ෼͔Γ΍͢͞Λॏࢹ͠ɺਖ਼֬ੑ͸͍͍ܽͯ·͢
  4. (΄΅)શςʔϒϧʹtenant_idΧϥϜΛ༻ҙ͢ΔMulti Tenancyͷ࣮ݱํ๏ !35 Tenants Quotations Aגࣜձࣾ ஫ҙ) ෼͔Γ΍͢͞Λॏࢹ͠ɺਖ਼֬ੑ͸͍͍ܽͯ·͢ https://a-inc.a1a.com Users

    ᶃ subdomainΛجʹTenantsςʔ ϒϧͷIDΛಛఆ ᶄ ಉҰϦΫΤετத͸શςʔϒϧ΁ ͷΫΤϦͷWHERE۟ʹऔಘͨ͠ tenant_idͰͷߜΓࠐΈ͕ઃఆ͞ΕΔ ᶃ ᶄ ᶄ
  5. 1. όΠϠʔ୯ҐͰςφϯτΛ࡞੒͢Δ tenant_id: 1 tenant_id: 2 tenant_id: 3 όΠϠʔA όΠϠʔB

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

    tenant_id: 3 tenant_id: 4 tenant_id: 5 tenant_id: 6 αϓϥΠϠD αϓϥΠϠE !41
  7. ಋೖͨ͠όΠϠʔ͕࿈Ε͖ͯͯ͘ΕΔαϓϥΠϠʔͱ͍͏ࢿ࢈ !46 όΠϠʔ αϓϥΠϠʔ αϓϥΠϠʔ αϓϥΠϠʔ αϓϥΠϠʔ αϓϥΠϠʔ όΠϠʔ͸ݟੵͷ΍ΓͱΓΛ͢ΔαϓϥΠϠʔͱ RFQΫϥ΢υΛ࢖༻͢ΔɻࣗಈతʹαϓϥΠϠʔ

    ͷΞΧ΢ϯτʢແྉʣ͕ൃߦ͞ΕΔ͜ͱʹͳΔɻ ࠓޙ͸͜ͷαϓϥΠϠʔʹ΋Ձ஋Λఏڙ͍ͯ͘͠ ͜ͱͰϏδωε֦େͷՄೳੑ͕͋Δɻ ଟ͍ͱ͖͸਺ઍࣾ࿈Ε͖ͯͯ͘ΕΔ
  8. accounts ID name tenant_id role 1 ࠤʑ໦ 1 όΠϠʔ 2

    দຊ 1 αϓϥΠϠʔ 3 ਅน 2 όΠϠʔ 4 ๺໺ 2 αϓϥΠϠʔ 1. όΠϠʔςφϯτʹαϓϥΠϠʔΞΧ΢ϯτ͕ॴଐ͢Δઃܭͷ৔߹ !52 ID name 1 ձࣾA 2 ձࣾB tenants ஫ҙ) ෼͔Γ΍͢͞Λॏࢹ͠ɺਖ਼֬ੑ͸͍͍ܽͯ·͢
  9. request_for_quotations(ݟੵґཔ) ID produce_number tenant_id sender_account_id receiver_account_id 1 300 1 1

    2 2 60 2 3 4 3 1200 1 1 2 ID unit_price request_for_quotation_id tenant_id reply_account_id 1 100 1 1 2 2 90 2 2 4 quotations(ݟੵճ౴) 1. όΠϠʔςφϯτʹαϓϥΠϠʔΞΧ΢ϯτ͕ॴଐ͢Δઃܭͷ৔߹ !53 ஫ҙ) ෼͔Γ΍͢͞Λॏࢹ͠ɺਖ਼֬ੑ͸͍͍ܽͯ·͢
  10. accounts ID name tenant_id 1 ࠤʑ໦ 1 2 দຊ 2

    3 ਅน 3 2. όΠϠʔͱαϓϥΠϠʔʹͦΕͧΕಠཱͨ͠ςφϯτΛ࡞੒͢Δ৔߹ !54 ID name role 1 ձࣾA όΠϠʔ 2 ձࣾB αϓϥΠϠʔ 3 ձࣾC όΠϠʔ tenants ஫ҙ) ෼͔Γ΍͢͞Λॏࢹ͠ɺਖ਼֬ੑ͸͍͍ܽͯ·͢
  11. request_for_quotations(ݟੵґཔ) ID produce_number tenant_id 1 300 1 2 60 3

    ID unit_price received_request_for_quotation_id tenant_id 1 100 1 2 2 90 2 2 quotations(ݟੵճ౴) 2. όΠϠʔͱαϓϥΠϠʔʹͦΕͧΕಠཱͨ͠ςφϯτΛ࡞੒͢Δ৔߹ ID produce_number tenant_id 1 300 2 2 60 2 received_request_for_quotations(डྖݟੵґཔ) ID unit_price request_for_quotation_id tenant_id 1 100 1 1 2 90 2 3 received_quotations(डྖݟੵճ౴) !55 ஫ҙ) ෼͔Γ΍͢͞Λॏࢹ͠ɺਖ਼֬ੑ͸͍͍ܽͯ·͢
  12. materials ID name code 1 ϒϥέοτ 391001 2 εϥΠυϘοΫε NULL

    3 ݻఆۚ۩ 369221 !60 codeΧϥϜʹ͸NULL͕ೖΓ͏Δɻ
  13. materials ID name 1 ϒϥέοτ 2 εϥΠυϘοΫε 3 ݻఆۚ۩ ID

    material_id code 1 1 391001 2 3 369221 material_codes ͱΓ͋͑ͣNULL͸ແ͘ͳͬͨʂ Ͱ΋ɺ͜ΕͰ͍͍ͷ͔ʁ !61
  14. 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 !65
  15. suppliers ID name rating updated_at 1 औҾઌA 80 2019-05-16 09:10:11

    2 औҾઌB 35 2019-05-17 12:51:02 suppliersςʔϒϧʹ͸αϓϥΠϠʔͷQCDධՁ͕ೖ͓ͬͯΓɺαϓϥΠ Ϡʔબఆʹ͓͍ͯॏཁͳ໾ׂΛ୲͏ɻ ͦͷͨΊɺ୭͕͍ͭͲͷΑ͏ʹมߋ͔ͨ͠ΛϢʔβʔ͕ӾཡͰ͖ΔΑ͏ʹ ͓͖͍ͯͨ͠ɻ !77 αϓϥΠϠʔͷ৔߹
  16. suppliers ID name rating 1 औҾઌA 80 2 औҾઌB 35

    !78 ID supplier_id name rating reason updated_at 1 1 औҾઌA 75 ೥࣍ධՁͷͨΊ 2019-03-03 19:42:01 2 1 औҾઌA 80 ࠓظ͔Βઃඋ౤ࢿ͕େ෯ʹ૿ ͑ڙڅ͕҆ఆ͖ͯͨͨ͠Ί 2019-05-16 09:10:11 supplier_updates αϓϥΠϠʔͷ৔߹
  17. material_tags ID name 1 Ξϧϛ 2 εςϯϨε !80 λάͷ৔߹ λάͷઃఆ͸RFQΫϥ΢υͱ͍͏ϓϩμΫτ͕ఏڙ͢ΔՁ஋ͷத৺ʹ͸ͳ͍ɻ

    ͋͘·Ͱศརʹ࢖͏ͨΊͷػೳͰ͋Γɺ؂ࠪతͳূ੻΋ෆཁͳͨΊɺλάߋ৽ΛΠ ϕϯτͱͯ͠நग़͢Δඞཁ͸ͳ͍ɻ ΧελϚʔαϙʔτͰτϨʔε͢Δඞཁ͕͋Δ৔߹͸ผ్ϩάج൫Λ࡞ͬͯ౷ҰԽ ͢Δͱྑ͍ɻ
  18. 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 !84
  19. 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ͷ࡞ ੒ޙʹొ࿥͢Δʁ !85
  20. 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ͷ σʔλొ࿥͸ॱෆಉʹͳͬͨ !88
  21. 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 !94
  22. 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 !95
  23. 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 !96