设计

设计过程主要是:

Web API 设计
数据库设计

数据库设计

dbdiagram 用 DBML 描述, 语法详见文档.

除了使用 dbdiagram 在网页进行可视化设计外, 还可以直接在本地生成, 详见官方文档.

总体流程:

DB First: 利用 dbdiagram 进行数据库设计.
利用设计自动生成 SQL.
在 Golang 代码中使用.

主要的三张表:

Account 表: Account 表用于存放用户账户信息.
Entry 表: 用于记录一个账户的所有 balance 变更历史.
Transfer 表: 用于记录转账历史.

三张表对应的 DBML 描述如下(其中对有查询/排序的字段添加索引):

// 用户账户
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 [not null, ref: > A.id]
  amount bigint [not null, note: '余额变更可正也可负']
  created_at timestamptz [not null, default: `now()`]
  
  Indexes {
    account_id
  }
}

// 账户间的转账历史
Table transfers {
  id bigserial [pk]
  from_account_id bigint [not null, ref: > A.id]
  to_account_id bigint [not null, ref: > A.id]
  amount bigint [not null, note: '转账记录数值必须为正数']
  created_at timestamptz [not null, default: `now()`]
  
  Indexes {
    from_account_id
    to_account_id
    // 同时查询的时候这样创建索引也可以提速
    (from_account_id, to_account_id)
  }
}

生成 SQL 如下:

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())
);

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 '余额变更可正也可负';

COMMENT ON COLUMN "transfers"."amount" IS '转账记录数值必须为正数';

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");

数据库设计​

数据库设计