budget/scripts/create_table.sql

49 lines
1.1 KiB
MySQL
Raw Permalink Normal View History

-- 账户表
2023-05-21 15:00:12 +00:00
CREATE TABLE account (
2023-05-28 04:07:16 +00:00
a_id SERIAL PRIMARY KEY, -- 账户号
2023-06-05 12:47:58 +00:00
balance MONEY, -- 余额
meta JSONB -- 元数据
2023-05-21 15:00:12 +00:00
);
2023-06-05 12:47:58 +00:00
-- {"name":"name",
-- “budget": [
-- {"time":"2022-02","value":20.32},
-- {"time":"2022-01","value":30.32}
-- ]
-- "discription":"something"}
2023-05-21 15:00:12 +00:00
-- 类别表
2023-05-21 15:00:12 +00:00
CREATE TABLE category (
2023-06-05 12:47:58 +00:00
c_id SERIAL PRIMARY KEY, -- 类别号
meta JSONB -- 元数据
2023-05-21 15:00:12 +00:00
);
-- {"name":"name",
-- "type":"in"/"out",
-- “budget": [
-- {"time":"2022-02","value":20.32},
-- {"time":"2022-01","value":30.32}
-- ]
-- "discription":"something"}
2023-05-21 15:00:12 +00:00
-- 流水表
2023-05-21 15:00:12 +00:00
CREATE TABLE transaction (
2023-06-05 12:47:58 +00:00
t_id SERIAL PRIMARY KEY, -- 流水号
2023-06-03 06:09:06 +00:00
a_id INTEGER REFERENCES account(a_id) NOT NULL, -- 关联账户号
2023-06-05 12:47:58 +00:00
c_id INTEGER REFERENCES category(c_id), -- 关联类别号
s_id INTEGER REFERENCES account(a_id), -- 源账户号
2023-06-03 06:09:06 +00:00
time TIMESTAMP,
2023-05-28 04:07:16 +00:00
amount MONEY,
meta JSONB -- 元数据
2023-05-21 15:00:12 +00:00
);
2023-06-05 12:47:58 +00:00
-- {"discription":"something",
-- "type":"in"/"out"/"transfer"/"init",
-- "reimburse":{
-- "finish": true/false,
-- "ref":t_id
-- }
-- "":"something"}
2023-05-21 15:00:12 +00:00
2023-05-21 15:00:12 +00:00