PostgreSQL如何实现动态增删费用类型 兼容新旧账单存储需求

放弃原有「费用项作为账单表固定列」的宽表设计，采用元数据驱动的结构化表结构，可完全覆盖动态增删费用项、历史数据留痕、周期级费用可见性控制三个核心需求，且数据一致性、查询性能、可维护性远高于随意搭建的EAV模型或者JSONB存动态字段的方案。
#

核心表结构设计 #

所有核心业务数据均不做物理删除，仅通过状态标记实现停用、隐藏逻辑，从结构上保证历史数据可追溯。

1. 费用类型元数据表 expense_types #

存储所有系统预置、用户自定义的费用项，是费用项的唯一数据源。

CREATE TYPE expense_status AS ENUM ('active', 'disabled');

CREATE TABLE expense_types (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE, -- 费用项名称，如维护费、剪草支出
    is_preset BOOLEAN NOT NULL DEFAULT false, -- 标记是否为系统预置项，预置项仅可停用不可删除
    status expense_status NOT NULL DEFAULT 'active', -- 费用项状态
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    remark VARCHAR(200)
);
-- 预置初始费用项
INSERT INTO expense_types (name, is_preset) VALUES 
('维护费', true),
('供暖费', true),
('垃圾清运费', true),
('排污费', true),
('电费', true);

2. 账单周期表 billing_cycles #

独立管理所有账单周期，支撑周期级的费用权限控制。

CREATE TABLE billing_cycles (
    id BIGSERIAL PRIMARY KEY,
    cycle_name VARCHAR(50) NOT NULL, -- 如2024年10月、2024年Q3
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    is_settled BOOLEAN NOT NULL DEFAULT false, -- 标记是否封账，封账后不可修改配置和明细
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE(start_date, end_date)
);

3. 周期-费用项关联表 cycle_allowed_expense_types #

这是实现周期级费用可见性、历史数据留痕的核心表：仅在该表内关联的费用项，才会在对应周期的账单录入页展示；周期封账后该表对应记录永久冻结，不修改不删除。

CREATE TABLE cycle_allowed_expense_types (
    id BIGSERIAL PRIMARY KEY,
    cycle_id BIGINT NOT NULL REFERENCES billing_cycles(id) ON DELETE RESTRICT,
    expense_type_id BIGINT NOT NULL REFERENCES expense_types(id) ON DELETE RESTRICT,
    is_cycle_specific BOOLEAN NOT NULL DEFAULT false, -- 标记是否为当前周期专属费用项，专属项不会同步到其他周期
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE(cycle_id, expense_type_id) -- 避免同一周期重复添加同一费用项
);

4. 账单主表 bills #

存储账单公共属性，不存具体费用明细。

CREATE TABLE bills (
    id BIGSERIAL PRIMARY KEY,
    cycle_id BIGINT NOT NULL REFERENCES billing_cycles(id) ON DELETE RESTRICT,
    owner_name VARCHAR(50) NOT NULL, -- 账单归属方，如业主姓名、房号
    total_amount NUMERIC(10,2) NOT NULL DEFAULT 0, -- 总金额，可通过触发器自动汇总明细金额
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

5. 账单费用明细表 bill_expense_details #

存储每个账单下的具体费用项金额，明细记录永久保留不删除。

CREATE TABLE bill_expense_details (
    id BIGSERIAL PRIMARY KEY,
    bill_id BIGINT NOT NULL REFERENCES bills(id) ON DELETE RESTRICT,
    expense_type_id BIGINT NOT NULL REFERENCES expense_types(id) ON DELETE RESTRICT,
    amount NUMERIC(10,2) NOT NULL CHECK (amount >= 0), -- 费用金额，用NUMERIC避免浮点精度问题
    remark VARCHAR(200),
    UNIQUE(bill_id, expense_type_id) -- 同一账单同一费用项仅可录入一条
);

业务需求对应实现逻辑 #

• 动态新增费用类型：全局通用费用项直接插入expense_types表，后续新开账周期时自动将所有status=active的费用项写入cycle_allowed_expense_types关联表；如果是单个周期的偶发/专属费用，仅写入对应周期的关联表，其他周期不可见。
• 停用费用类型：仅需将expense_types表中对应记录的status更新为disabled，后续新开周期不会自动关联该费用项，新账单录入页不再展示；历史周期的关联关系、已生成的费用明细完全不受影响，查询历史账单时关联expense_types表即可正常展示费用名称。
• 历史数据留存：所有费用类型元数据、周期关联关系、费用明细仅做逻辑状态变更，不做物理删除；周期封账后自动冻结对应关联表和明细数据，不会出现历史账单费用项缺失、名称无法匹配的问题。
• 周期专属费用控制：账单录入页加载可选费用项时，仅查询当前账单对应周期在cycle_allowed_expense_types中关联的费用项列表，不在列表内的费用项不可录入、不可见，完全实现专属费用仅对应周期可用。

常用查询示例 #

查询单张账单的全部费用明细：

SELECT
  et.name AS expense_item,
  bed.amount,
  bed.remark
FROM bill_expense_details bed
JOIN expense_types et ON bed.expense_type_id = et.id
WHERE bed.bill_id = $1; -- $1为传入的账单ID

查询指定周期新账单可选择的费用项列表：

SELECT
  et.id,
  et.name
FROM cycle_allowed_expense_types caet
JOIN expense_types et ON caet.expense_type_id = et.id
WHERE caet.cycle_id = $1; -- $1为当前操作的账期ID

避坑提示：不要图省事用JSONB类型列在账单表存储动态费用字段，后续做费用统计、对账校验、数据筛选时复杂度会指数级上升，且一旦删除费用类型元数据，JSON中存储的字段名将无法对应业务含义，历史数据追溯能力极差。

内容的提问来源于stack exchange，提问作者Alyona