ออกแบบฐานข้อมูล Simple bank

ออกแบบฐานข้อมูล Simple bank

บริการที่เราจะสร้างเป็นธนาคารที่เรียบง่าย มันจะจัดเตรียม API สำหรับส่วนหน้าเพื่อทำสิ่งต่อไปนี้:

• ขั้นแรก สร้างและจัดการบัญชีธนาคาร ซึ่งประกอบด้วยชื่อเจ้าของ ยอดคงเหลือ และสกุลเงิน
• ประการที่สอง บันทึกการเปลี่ยนแปลงยอดคงเหลือทั้งหมดในแต่ละบัญชี ดังนั้นทุกครั้งที่มีการเพิ่มหรือหักเงินจากบัญชี จะมีการสร้างบันทึกรายการบัญชี
• และประการที่สาม ดำเนินการโอนเงินระหว่าง 2 บัญชี สิ่งนี้ควรเกิดขึ้นภายในธุรกรรม เพื่อให้ยอดคงเหลือของทั้งสองบัญชีได้รับการอัปเดตสำเร็จหรือไม่มีเลย

ออกแบบฐานข้อมูล

เราจะเรียนรู้เกี่ยวกับการออกแบบฐานข้อมูล:

• ออกแบบสคีมาฐานข้อมูล SQL โดยใช้ dbdiagram.io
• บันทึกสคีมาเป็นไดอะแกรม PDF หรือ PNG เพื่อแชร์กับทีมของคุณ
• และสุดท้ายสร้างโค้ด SQL เพื่อสร้างสคีมาในเอ็นจิ้นฐานข้อมูลเป้าหมายที่คุณเลือก เช่น PostgreSQL, MySQL หรือ SQL server

สคีมา (Schema)หมายถึง โครงสร้างข้อมูลหรือนิยามข้อมูล รวมถึงความสัมพันธ์ของข้อมูลในแต่ละเอ็นติตี้ ว่ามี ความสัมพันธ์กันอย่างไร

ออกแบบ DB schema

เริ่มโดยไปที่ dbdiagram.io แล้วคลิกไปที่ Go To App

นี่คือตัวอย่างสคีมา DB ทางด้านซ้าย เรากำหนดโครงสร้างตารางด้วยไวยากรณ์ง่ายๆ จากนั้นไดอะแกรมที่เกี่ยวข้องจะปรากฏขึ้นทางด้านขวา

เราสามารถใช้เครื่องมือ Export ที่ด้านบนสุดเพื่อบันทึกไดอะแกรมนี้เป็นไฟล์ PDF หรือ PNG หรือสร้างรหัส SQL สำหรับเซิร์ฟเวอร์ Postgres, MySQL หรือ SQL server

ตาราง accounts

มาแก้ไขสคีมานี้สำหรับฐานข้อมูลของเรากัน โดยจะเปลี่ยนชื่อไดอะแกรมนี้เป็น “Simple bank” อันดับแรกเราจะมีตาราง accounts เราใช้คีย์เวิร์ด Table เพื่อประกาศตาราง และใช้คีย์เวิร์ด as เพื่อตั้งชื่อนามแฝงแบบสั้นสำหรับตาราง

สมมติว่าเราต้องการให้แต่ละบัญชีมี ID ที่ไม่ซ้ำกัน ดังนั้นจึงใช้ฟิลด์ ID ที่เพิ่มอัตโนมัติสำหรับa account นั้น

Table accounts as A {
  id bigserial [pk]
}

ในสคริปต์ตัวอย่าง พวกเขาใช้ increment เพื่อจุดประสงค์นั้น แต่ใน Postgres เรายังใช้ type bigserial ได้ โดยพื้นฐานแล้วมันหมายถึงจำนวนเต็มที่เพิ่มขึ้นอัตโนมัติขนาดใหญ่ (8 ไบต์/64 บิต)

เราใช้ pk เพื่อบอกว่าฟิลด์นี้เป็น primary key ของตารางนี้

ฟิลด์ถัดไปคือ owner ซึ่งเก็บชื่อเจ้าของบัญชี ดังนั้นประเภทของมันสามารถเป็น text หรือ varchar.

Table accounts as A {
  id bigserial [pk]
  owner varchar
  balance bigint
}

จากนั้น balance ช่องสำหรับเก็บจำนวนเงินที่มีอยู่ในบัญชี พูดง่ายๆ แค่ใช้ bigint พิมพ์ที่นี่ ในความเป็นจริง บางสกุลเงินไม่ใช่จำนวนเต็มเสมอไป ดังนั้น คุณควรพิจารณาใช้ประเภท decimal แทน

ตอนนี้ ให้เพิ่มอีกหนึ่งฟิลด์เพื่อเก็บชื่อของไฟล์ currency และสุดท้ายเป็น created_at ฟิลด์มาตรฐานที่ต้องทราบเมื่อสร้างบัญชี

เราควรใช้ประเภท timestamptz แทน timestamp เพราะมันรวมถึงข้อมูลเขตเวลาด้วย และเราต้องการให้ฐานข้อมูลตั้งค่าโดยอัตโนมัติ ดังนั้น ให้ตั้งค่าเริ่มต้นสำหรับฐานข้อมูลด้วย default

Postgres มีฟังก์ชัน now() ที่รับเวลาปัจจุบัน เราสามารถใส่ไว้ในคู่ backtick เพื่อใช้เป็นค่าเริ่มต้นได้

Table accounts as A {
  id bigserial [pk]
  owner varchar
  balance bigint
  currency varchar
  created_at timestamptz [default: `now()`]
}

ถึงขั้นตอนนี้ ตาราง accounts สร้างเสร็จเรียบร้อยแล้ว

ตาราง entries

ตารางถัดไปเป็น entries ตารางนี้จะบันทึกการเปลี่ยนแปลงทั้งหมดในยอดเงินในบัญชี นอกจากนี้ยังมีคอลัมน์ id ที่เพิ่มขึ้นอัตโนมัติ ซึ่งเป็น primary key

และมี account_id ประเภท bigint ที่อ้างอิงคอลัมน์ id ของตาราง account เราใช้คีย์เวิร์ด ref เพื่อประกาศการอ้างอิงนี้

ดังที่คุณอาจทราบ ค่านี้แสดงถึงความสัมพันธ์แบบ 1 ต่อกลุ่มระหว่างตาราง id และ account เนื่องจาก 1 บัญชีสามารถมีรายการหลายรายการเพื่อเปลี่ยนยอดคงเหลือ

Table entries {
  id bigserial [pk]
  account_id bigint [ref: > A.id]
  amount bigint
  created_at timestamptz [default: `now()`]
}

ฟิลด์อื่นที่เราจะต้องมีคือ amount จำนวนเงินที่เพิ่มไปยังยอดเงินในบัญชีในรายการนี้ อาจเป็นบวกหรือลบขึ้นอยู่กับว่าเงินจะเข้าหรือออกจากบัญชี

และสุดท้ายเป็นช่อง created_at เพื่อบันทึกเมื่อสร้างรายการ

ตาราง transfers

ตารางสุดท้ายคือ transfers บันทึกการโอนเงินทั้งหมดระหว่าง 2 บัญชี สมมติว่าในหลักสูตรนี้ เราสนใจเฉพาะการโอนเงินภายในธนาคารแบบง่าย

ดังนั้นตารางนี้จะมี:

• คีย์หลัก id การเพิ่มอัตโนมัติ
• คีย์ต่างประเทศ from_account_id และคีย์ต่างประเทศอื่น to_account_id ทั้งคู่อ้างอิงคอลัมน์ id ของตาราง accounts
• จากนั้น amount จำนวนเงินที่ย้ายจากบัญชีหนึ่งไปยังอีกบัญชีหนึ่ง แต่ต่างจาก amount จำนวนเงินในตาราง entries ค่า amount จำนวนนี้ต้องเป็นค่าบวก
• และสุดท้ายคือฟิลด์ created_at เหมือนกับในตารางอื่นๆ

Table transfers {
  id bigserial [pk]
  from_account_id bigint [ref: > A.id]
  to_account_id bigint [ref: > A.id]
  amount bigint
  created_at timestamptz [default: `now()`]
}

เพิ่มข้อจำกัด

ตกลง DB schema ของเราใกล้เสร็จแล้ว ทีนี้มาดูตัวอย่างที่เหลือเพื่อดูว่ามีอะไรที่เราเพิ่มได้อีกไหม

นี่เป็นอีกวิธีหนึ่งในการประกาศการอ้างอิงคีย์ต่างประเทศ เรายังสามารถเพิ่มข้อจำกัดที่ไม่เป็นค่าว่าง not null หรือ unique ค่าเฉพาะให้กับบางฟิลด์ในตารางของเราได้อีกด้วย

เรามีฟิลด์ใดที่ควรมีเอกลักษณ์ ยกเว้นคีย์หลักที่มีอยู่แล้ว ดังนั้นฉันจะเพิ่ม not null ให้กับ:

• owner เจ้าของ , balance ยอดคงเหลือ , currency สกุลเงิน และคอลัมน์ created_at ของตาราง accounts
• amount และคอลัมน์ created_at ของตาราง entries
• และคล้ายกันสำหรับตารางการโอน transfers

เพิ่มบันทึก

นอกจากนี้เรายังสามารถเพิ่มบันทึกย่อลงในคอลัมน์ได้อีกด้วย มาเพิ่มหมายเหตุลงในคอลัมน์ amount ของตาราง transfers กัน โดยระบุว่าเป็นค่าลบหรือค่าบวก และหมายเหตุอื่นในคอลัมน์จำนวนเงินของตารางการโอนว่าต้องเป็นค่าบวก

Table entries {
  ...
  amount bigint [not null, note: 'can be negative or positive']
}

Table transfers {
  ...
  amount bigint [not null, note: 'must be positive']
}

และนี่คือ โค้ดทั้งหมดของขั้นตอนนี้

Table accounts as A {
  id bigserial [pk]
  owner varchar [not null]
  balance bigint [not null]
  currency varchar [not null]
  created_at timestamptz [not null, default: `now()`]
}

Table entries {
  id bigserial [pk]
  account_id bigint [ref: > A.id, not null]
  amount bigint [not null, note: 'can be negative or positive']
  created_at timestamptz [not null, default: `now()`]
}

Table transfers {
  id bigserial [pk]
  from_account_id bigint [ref: > A.id, not null]
  to_account_id bigint [ref: > A.id, not null]
  amount bigint [not null, note: 'must be positive']
  created_at timestamptz [not null, default: `now()`]
}

กำหนดประเภท enum

เราสามารถกำหนดประเภท enum แบบกำหนดเองสำหรับคอลัมน์ของเราได้ ตัวอย่างเช่น เราสามารถกำหนด Currency enum ด้วยค่าที่เป็นไปได้ 2 ค่า: USD และ EUR และใช้เป็นประเภทของคอลัมน์ accounts.currency ดังนี้:

enum Currency {
    USD
    EUR
}

Table accounts as A {
  ...
  currency Currency [not null]
}

อย่างไรก็ตาม เราจะใช้ชนิดข้อมูล varchar ที่นี่ และให้โค้ดแอปพลิเคชันจัดการการตรวจสอบค่า

เพิ่ม indexes

ตกลง สิ่งสุดท้ายที่เราต้องทำคือเพิ่ม Indexes ลงในตารางของเรา เพื่อที่เราจะใช้คำหลัก Indexes

ในตาราง accounts เราอาจต้องการค้นหาบัญชีตามชื่อเจ้าของ ดังนั้นมาเพิ่ม owner ในรายการ indexes

ในตาราง entries เราอาจต้องการแสดงรายการทั้งหมดของบัญชีใดบัญชีหนึ่ง ดังนั้น มาเพิ่ม account_id ให้กับ index

ตารางการโอนเงิน transfers จะซับซ้อนที่สุด:

• เราอาจต้องการค้นหาการโอนเงินทั้งหมดที่ออกจากบัญชี ดังนั้น from_account_id ควรเป็น 1 index
• ในทำนองเดียวกัน เราอาจต้องการค้นหาการโอนเงินทั้งหมดที่เข้าสู่บัญชี ดังนั้น to_account_id ควรเป็น index อื่น
• และสุดท้าย หากเราต้องการค้นหาการโอนเงินทั้งหมดระหว่าง 2 บัญชี เราจำเป็นต้องมีดัชนีผสมของทั้ง from_account_id และ to_account_id

Table accounts as A {
  id bigserial [pk]
  owner varchar [not null]
  balance bigint [not null]
  currency varchar [not null]
  created_at timestamptz [not null, default: `now()`]

  Indexes {
    owner
  }
}

Table entries {
  id bigserial [pk]
  account_id bigint [ref: > A.id, not null]
  amount bigint [not null, note: 'can be negative or positive']
  created_at timestamptz [not null, default: `now()`]

  Indexes {
    account_id
  }
}

Table transfers {
  id bigserial [pk]
  from_account_id bigint [ref: > A.id, not null]
  to_account_id bigint [ref: > A.id, not null]
  amount bigint [not null, note: 'must be positive']
  created_at timestamptz [not null, default: `now()`]

  Indexes {
    from_account_id
    to_account_id
    (from_account_id, to_account_id)
  }
}

และตอนนี้ คำจำกัดความสคีมาของเราเสร็จสมบูรณ์

ดูไดอะแกรมฐานข้อมูล

ทางด้านขวา เราสามารถจัดเรียงเพื่อจัดระเบียบไดอะแกรม DB ที่สร้างขึ้นสำหรับเรา

อย่างที่คุณเห็น มีลิงก์ 1-to-many จากตาราง accounts ไปยัง entries และมีลิงก์ 1-to-many 2 ลิงก์จาก accounts ไปยังตาราง transfers

สร้างโค้ด SQL

ตอนนี้ มาลองสร้างโค้ด PostgreSQL โดยใช้เครื่องมือ export ที่ด้านบน -> Export to PostgreSQL

ไฟล์ถูกดาวน์โหลดมา

เมื่อเปิด ไฟล์ดู จะพบโค้ดพร้อมที่จะรันใน PostgreSQL เพื่อสร้างสคีมาฐานข้อมูล

CREATE TABLE "accounts" (
  "id" bigserial PRIMARY KEY,
  "owner" varchar NOT NULL,
  "balance" bigint NOT NULL,
  "currency" varchar NOT NULL,
  "created_at" timestamptz NOT NULL DEFAULT (now())
);

CREATE TABLE "entries" (
  "id" bigserial PRIMARY KEY,
  "account_id" bigint NOT NULL,
  "amount" bigint NOT NULL,
  "created_at" timestamptz NOT NULL DEFAULT (now())
);

CREATE TABLE "transfers" (
  "id" bigserial PRIMARY KEY,
  "from_account_id" bigint NOT NULL,
  "to_account_id" bigint NOT NULL,
  "amount" bigint NOT NULL,
  "created_at" timestamptz NOT NULL DEFAULT (now())
);

ALTER TABLE "entries" ADD FOREIGN KEY ("account_id") REFERENCES "accounts" ("id");

ALTER TABLE "transfers" ADD FOREIGN KEY ("from_account_id") REFERENCES "accounts" ("id");

ALTER TABLE "transfers" ADD FOREIGN KEY ("to_account_id") REFERENCES "accounts" ("id");

CREATE INDEX ON "accounts" ("owner");

CREATE INDEX ON "entries" ("account_id");

CREATE INDEX ON "transfers" ("from_account_id");

CREATE INDEX ON "transfers" ("to_account_id");

CREATE INDEX ON "transfers" ("from_account_id", "to_account_id");

COMMENT ON COLUMN "entries"."amount" IS 'can be negative or positive';

COMMENT ON COLUMN "transfers"."amount" IS 'must be positive';

credit : https://dev.to/techschoolguru/