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

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

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

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