数据库如何设计 system default 与用户自定义项

2026-10-10支付小周

结论

通常应选择 Option 1 的单表设计。系统默认项和用户自定义项本质上都是 PaymentTerm，适合存放在同一张表中，并通过可空的 OwnerUserId 区分作用域：

OwnerUserId IS NULL：系统默认项；
OwnerUserId = 当前用户 ID：该用户创建的自定义项。

INVOICE 只需保留一个 PaymentTermId 外键。Option 2 将同一种业务实体拆成两张表，会引入双外键、状态标志和分支查询，也更难保证数据完整性，因此不建议采用。

如果数据库必须严格保证“发票只能引用系统项或发票所属用户的自定义项”，可以在 Option 1 的基础上增加触发器。如果希望完全依靠声明式外键约束，可以再增加一张用户可用项关联表。

查询用户可选择的 Payment Terms

用户可以看到系统默认项和自己创建的自定义项：

SELECT
    id,
    name,
    owner_user_id,
    CASE
        WHEN owner_user_id IS NULL THEN 'SYSTEM'
        ELSE 'CUSTOM'
    END AS source
FROM payment_term
WHERE is_active = TRUE
  AND (
      owner_user_id IS NULL
      OR owner_user_id = :current_user_id
  )
ORDER BY
    CASE WHEN owner_user_id IS NULL THEN 0 ELSE 1 END,
    name;

查询某张发票使用的 Payment Term 时，也不用根据标志选择不同的表：

SELECT
    i.id AS invoice_id,
    pt.id AS payment_term_id,
    pt.name AS payment_term_name,
    CASE
        WHEN pt.owner_user_id IS NULL THEN FALSE
        ELSE TRUE
    END AS is_custom
FROM invoice AS i
JOIN payment_term AS pt
  ON pt.id = i.payment_term_id
WHERE i.id = :invoice_id;

普通外键无法完成所有校验

上述外键只能保证 PaymentTermId 对应的记录确实存在，无法确认它是否属于正确的用户。例如，用户 1001 的发票在技术上仍有可能引用用户 1002 创建的 Payment Term。

因此，写入发票时必须检查：

payment_term.owner_user_id IS NULL
OR payment_term.owner_user_id = invoice.user_id

这条规则可以由应用服务检查。如果系统存在多个写入入口，最好同时在数据库中添加约束。

PostgreSQL 可以使用触发器：

CREATE OR REPLACE FUNCTION validate_invoice_payment_term()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM payment_term AS pt
        WHERE pt.id = NEW.payment_term_id
          AND pt.is_active = TRUE
          AND (
              pt.owner_user_id IS NULL
              OR pt.owner_user_id = NEW.user_id
          )
    ) THEN
        RAISE EXCEPTION
            'Payment term % is not available to user %',
            NEW.payment_term_id,
            NEW.user_id;
    END IF;

    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_validate_invoice_payment_term
BEFORE INSERT OR UPDATE OF user_id, payment_term_id
ON invoice
FOR EACH ROW
EXECUTE FUNCTION validate_invoice_payment_term();

具体语法取决于所用的数据库。MySQL、SQL Server 和 Oracle 需要改写成各自支持的触发器语法。

需要纯外键约束时的 Option 3

如果不想依赖触发器，可以增加一张“用户可用 Payment Term”关联表，让发票引用用户已经获准使用的选项：

CREATE TABLE user_payment_term (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id BIGINT NOT NULL,
    payment_term_id BIGINT NOT NULL,

    CONSTRAINT fk_user_payment_term_user
        FOREIGN KEY (user_id)
        REFERENCES app_user(id),

    CONSTRAINT fk_user_payment_term_term
        FOREIGN KEY (payment_term_id)
        REFERENCES payment_term(id),

    CONSTRAINT uq_user_payment_term
        UNIQUE (user_id, payment_term_id),

    CONSTRAINT uq_user_payment_term_owner
        UNIQUE (user_id, id)
);

发票同时保存用户和关联记录，再用复合外键确保两者匹配：

CREATE TABLE invoice (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id BIGINT NOT NULL,
    user_payment_term_id BIGINT NOT NULL,

    CONSTRAINT fk_invoice_user_payment_term
        FOREIGN KEY (user_id, user_payment_term_id)
        REFERENCES user_payment_term(user_id, id)
);

创建用户时，需要为其写入系统默认项的关联记录：

INSERT INTO user_payment_term (user_id, payment_term_id)
SELECT :user_id, pt.id
FROM payment_term AS pt
WHERE pt.owner_user_id IS NULL
  AND pt.is_active = TRUE;

创建自定义项后，也要为创建者增加一条关联：

WITH new_term AS (
    INSERT INTO payment_term (name, owner_user_id)
    VALUES (:name, :user_id)
    RETURNING id
)
INSERT INTO user_payment_term (user_id, payment_term_id)
SELECT :user_id, id
FROM new_term;

这种设计可以通过外键严格保证访问关系，也便于以后支持以下需求：

管理员只向部分用户开放某个系统项；
团队共享自定义项；
用户隐藏某些系统默认项；
为不同租户配置不同的可用集合。

相应的代价是，系统默认项不再天然对所有用户可见，必须维护关联记录。用户数量和默认项数量较大时，这张表可能积累大量数据。如果需求只有“系统项全局可见，自定义项仅创建者可见”，使用单表并增加校验通常更简单。

不推荐 Option 2 的原因

PaymentTerm 和 PaymentTermCustom 表示的是同一种实体，区别只有所有者，没有必要拆成两张表。

拆表后，发票中会出现三个相互依赖的字段：

PaymentTermId
PaymentTermCustomId
IsPaymentTermCustom

数据库还要额外保证以下组合规则：

IsPaymentTermCustom = FALSE
    => PaymentTermId 非空且 PaymentTermCustomId 为空

IsPaymentTermCustom = TRUE
    => PaymentTermCustomId 非空且 PaymentTermId 为空

如果标志和两个外键不一致，就会产生无法判断真实 Payment Term 的脏数据。

查询也会变得复杂。统计、排序、搜索和关联查询通常需要使用 UNION、两个 LEFT JOIN 或大量 CASE 表达式。以后为 Payment Term 增加描述、到期天数或状态等字段时，两张表也必须同步修改。

这相当于手工实现多态关联，却失去了普通外键模型原有的简洁性。

唯一性约束与索引

如果系统项名称不能重复，可以建立部分唯一索引：

CREATE UNIQUE INDEX uq_payment_term_system_name
ON payment_term (name)
WHERE owner_user_id IS NULL;

同一用户下的自定义名称也可以限制为唯一：

CREATE UNIQUE INDEX uq_payment_term_user_name
ON payment_term (owner_user_id, name)
WHERE owner_user_id IS NOT NULL;

还可以为常用的作用域查询增加索引：

CREATE INDEX ix_payment_term_owner_active
ON payment_term (owner_user_id, is_active);

是否允许用户创建与系统项同名的自定义项，应由业务规则决定。例如，用户能否再创建一个 NET30，需要在产品层面明确，不能单靠表结构判断。

历史发票的处理

Payment Term 通常属于历史业务数据。直接修改或删除 NET30，可能改变已开具发票的语义。因此，已经被使用的 Payment Term 通常不应物理删除，而应通过状态将其停用：

UPDATE payment_term
SET is_active = FALSE
WHERE id = :payment_term_id
  AND owner_user_id = :current_user_id;

停用后，该选项不会再出现在新发票中，但旧发票仍可通过外键读取它。

如果发票必须完整保留开具时的显示内容，还可以在发票中保存快照字段：

ALTER TABLE invoice
ADD COLUMN payment_term_name_snapshot VARCHAR(100);

真正参与计算的条款更适合保存为结构化数据，例如：

ALTER TABLE payment_term
ADD COLUMN due_days INTEGER;

不要通过解析 NET30 这样的名称计算日期。名称用于展示，due_days 才是业务数据。DueOnReceipt 可以对应 due_days = 0。

最终选择

如果需求简单，而且系统默认项始终向所有用户开放，使用以下结构即可：

PAYMENT_TERM
- Id
- Name
- OwnerUserId NULL 表示系统项
- IsActive

INVOICE
- Id
- UserId
- PaymentTermId

应用校验或数据库触发器负责保证发票只能引用系统项，或者当前用户自己的自定义项。

只有在需要共享、授权、隐藏、租户级配置，或者必须用声明式外键严格表达可用范围时，才需要增加 USER_PAYMENT_TERM 关联表。无论采用哪种方案，都不建议将系统项和自定义项拆成两张实体表，也不应在发票中保存可由关联数据推导出的 IsPaymentTermCustom 标志。