SQL Server 中不同 schema 的数据库设计方案

2026-10-24支付老李

结论

这类系统适合采用“关系型流程数据 + 版本化 XML 业务数据”的混合设计：

request、申请人、review、approval、implementation、时间、状态等需要查询、统计或审计的信息，保存在普通关系表中。
各种 code type 特有的配置内容用 XML 保存。
每份 XML 都要引用一个不可变的 schema version。
版本机制应从第一版开始使用，即使 schema 很少变化，也要保存版本号。
SQL Server 2000 没有原生 xml 数据类型和 XML Schema Collection，因此应使用 ntext 保存 XML，并在应用层完成解析和 XSD 验证。

XML 只用于保存结构容易变化、基本不需要通过 SQL 查询的内容，不应承载整个系统的数据。

为什么不建议完全通用的属性表

如果把所有字段拆成“字段名称、数据类型、字段值、显示方式”，通常会形成 EAV（Entity-Attribute-Value）模型，例如：

RequestId | FieldName       | DataType | Value
----------|-----------------|----------|-------
1001      | PaymentMethod   | enum     | ACH
1001      | AllowReversal   | boolean  | true

这种模型看起来很通用，实际代价不小：

数据类型约束会转移到应用层。
外键、唯一性和跨字段约束很难表达。
查询完整 request 时，需要大量 join、pivot 或动态 SQL。
nested data、条件字段和重复集合会让模型很快变得复杂。
表单元数据、业务规则和数据值混在一起，维护起来很困难。
每种 code type 最后仍要有自己的展示或报表逻辑。

只有当字段需要由最终用户动态定义，而且系统还要支持逐字段搜索、筛选和统计时，EAV 才可能适用。当前需求并不具备这些条件。

独立表方案并没有错

为每种 code type 建立独立表，符合关系数据库的设计原则。例如：

CodeRequest
PaymentCodeRequest
ProviderCodeRequest
WriteOffCodeRequest

这种方案有几个明显的优点：

数据类型、NOT NULL、CHECK 和外键约束清楚。
SQL 查询和报表比较简单。
DBA 和开发人员容易理解。
不同 code type 可以分别优化。

代价是每增加一种 code type，都要增加相应的表、SQL、映射代码和部分界面逻辑。

旧系统失败，并不能说明“每种类型独立建表”本身有问题。问题也可能出在流程逻辑重复、缺少公共审计模型、没有版本管理，或者界面代码耦合过重。没有必要为了让设计看起来更 generic，改用一个更难维护的数据模型。

如果 code type 数量不多，而且以后需要按配置字段查询，独立表通常仍是更稳妥的选择。

SQL Server 2000 表结构示例

下面是一种简化后的设计：

CREATE TABLE CodeType
(
    CodeTypeId     int IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    CodeTypeName   varchar(100) NOT NULL,
    IsActive       bit NOT NULL DEFAULT 1
);

CREATE TABLE SchemaDefinition
(
    SchemaDefinitionId int IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    CodeTypeId         int NOT NULL,
    VersionNumber      int NOT NULL,
    XsdDocument        ntext NOT NULL,
    UiDefinition       ntext NULL,
    EffectiveFrom      datetime NOT NULL,
    CreatedBy          varchar(100) NOT NULL,
    CreatedAt          datetime NOT NULL DEFAULT GETDATE(),

    CONSTRAINT FK_SchemaDefinition_CodeType
        FOREIGN KEY (CodeTypeId)
        REFERENCES CodeType(CodeTypeId),

    CONSTRAINT UQ_SchemaDefinition_TypeVersion
        UNIQUE (CodeTypeId, VersionNumber)
);

CREATE TABLE CodeRequest
(
    RequestId           int IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    CodeTypeId          int NOT NULL,
    RequestedBy         varchar(100) NOT NULL,
    RequestedAt         datetime NOT NULL DEFAULT GETDATE(),
    CurrentStatus       varchar(30) NOT NULL,
    CurrentRevisionId   int NULL,

    CONSTRAINT FK_CodeRequest_CodeType
        FOREIGN KEY (CodeTypeId)
        REFERENCES CodeType(CodeTypeId)
);

CREATE TABLE RequestRevision
(
    RevisionId          int IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    RequestId           int NOT NULL,
    RevisionNumber      int NOT NULL,
    SchemaDefinitionId  int NOT NULL,
    PayloadXml          ntext NOT NULL,
    IsComplete          bit NOT NULL DEFAULT 0,
    CreatedBy           varchar(100) NOT NULL,
    CreatedAt           datetime NOT NULL DEFAULT GETDATE(),

    CONSTRAINT FK_RequestRevision_Request
        FOREIGN KEY (RequestId)
        REFERENCES CodeRequest(RequestId),

    CONSTRAINT FK_RequestRevision_Schema
        FOREIGN KEY (SchemaDefinitionId)
        REFERENCES SchemaDefinition(SchemaDefinitionId),

    CONSTRAINT UQ_RequestRevision_Number
        UNIQUE (RequestId, RevisionNumber)
);

CREATE TABLE WorkflowEvent
(
    WorkflowEventId int IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    RequestId       int NOT NULL,
    RevisionId      int NULL,
    EventType       varchar(30) NOT NULL,
    PerformedBy     varchar(100) NOT NULL,
    PerformedAt     datetime NOT NULL DEFAULT GETDATE(),
    Comments        varchar(2000) NULL,

    CONSTRAINT FK_WorkflowEvent_Request
        FOREIGN KEY (RequestId)
        REFERENCES CodeRequest(RequestId),

    CONSTRAINT FK_WorkflowEvent_Revision
        FOREIGN KEY (RevisionId)
        REFERENCES RequestRevision(RevisionId)
);

CurrentRevisionId 的外键可以在创建 RequestRevision 后通过 ALTER TABLE 添加，也可以只由应用层维护。无论选择哪种方式，创建 revision、写入 workflow event 和更新 request 状态都要在同一个事务中完成。

在生产设计中，用户最好通过内部 UserId 关联用户表，不要直接使用可能变化的姓名或登录名。为了保留历史显示，也可以同时保存操作发生时的用户名称快照。

Schema 版本如何处理

不要假设 schema 永远不会变化。成本最低的办法，是从一开始就遵守以下三条规则：

SchemaDefinition 发布后不得修改。
新结构必须创建新的 VersionNumber。
每个 RequestRevision 永久引用创建时使用的 SchemaDefinitionId。

例如，payment code 从版本 1 升级到版本 2 时：

PaymentCode / Version 1 / XSD A
PaymentCode / Version 2 / XSD B

历史 XML 仍按版本 1 验证和显示，新 request 默认使用版本 2，不需要立即迁移历史数据。

只有当业务明确要求“旧 request 必须使用新版表单继续编辑”时，才需要 migration。迁移过程应创建新的 revision，而不是覆盖原 XML：

revision 3：schema version 1，原始数据
revision 4：schema version 2，由迁移程序生成

还要记录迁移时间、执行者和来源 revision，才能保留完整的审计链。

SQL Server 2000 中的 XML 验证

SQL Server 2000 可以保存 XML，但不能像 SQL Server 2005 那样声明原生 xml 列并绑定 XML Schema Collection。因此，应用层需要完成以下操作：

根据 SchemaDefinitionId 读取 XSD。
解析 XML，确认格式正确。
使用对应的 XSD 验证。
验证成功后，在数据库事务中保存 revision。
状态进入 submitted、approved 或 implemented 前，再执行相应的完整性检查。

伪代码如下：

XmlSchemaSet schemas = new XmlSchemaSet();
schemas.Add(null, schemaReader);

XmlReaderSettings settings = new XmlReaderSettings();
settings.Schemas = schemas;
settings.ValidationType = ValidationType.Schema;
settings.ValidationEventHandler += OnValidationError;

using (XmlReader reader = XmlReader.Create(payloadReader, settings))
{
    while (reader.Read())
    {
        // Reading triggers XSD validation.
    }
}

具体使用哪个 API，要看项目采用的 .NET Framework 版本。较早的版本可能需要使用 XmlValidatingReader，不能仅根据这段示例，就假定 SQL Server 2000 项目一定支持较新的 XmlSchemaSet。

ntext 的使用方式与普通 nvarchar 不完全相同。应用程序应通过参数化的大对象接口写入，避免拼接 SQL，同时注意 SQL Server 2000 客户端驱动对 Unicode 和大字段参数的支持情况。

部分完成的 request

草稿通常是不完整的，不能要求每次保存都通过最终 XSD 的完整验证。可以按流程阶段设置不同的验证要求：

draft：只要求 XML 格式正确，元素和值类型合法，可以缺少必填项。
submitted：申请阶段的必填字段必须完整。
approved：执行审批阶段的业务规则。
implemented：满足最终配置结构和全部约束。

实现时有两种选择：

为草稿和最终提交分别准备不同的 XSD。
使用相对宽松的 XSD 验证基本结构，再由应用层执行各阶段的业务规则。

后者往往更实用。很多条件规则，比如“A 为 true 时 B 必填”，在旧版 XSD 中并不好表达。

不要指望只靠 XSD 自动生成好用的表单

XSD 适合描述 XML 的结构和基本数据类型，但通常无法定义完整的 UI。下面这些信息更适合放在独立且同样经过版本化的 UI metadata 中：

字段标签和帮助文字
控件类型
字段顺序和分组
dropdown 的显示文本
可见性和只读条件
跨字段验证规则
不同流程阶段的必填规则

例如：

<field path="/paymentCode/method"
       label="Payment method"
       widget="dropdown"
       required-at="submitted"
       option-set="payment-methods" />

XSD 和 UI metadata 应作为同一个版本化 definition package 发布。不要把展示要求硬塞进 XSD，也不要根据已有 record 反推表单结构。表单由定义生成，record 只负责填充已有字段，因此也能正常显示 partially-completed record。

XML 方案的边界

使用 XML 的前提，是这些配置不需要频繁通过 SQL 查询。如果以后出现下面这些需求，SQL Server 2000 上的 XML 方案会变得难以使用：

按 XML 内部字段筛选 request。
为配置值建立索引。
跨 request 汇总某个字段。
在 SQL 中校验引用完整性。
直接生成复杂的业务报表。

SQL Server 2005 提供的原生 xml 类型和 XQuery 可以改善查询能力，但无法让任意 XML 查询自动变得高效。如果某些字段后来成为常用查询条件，可以把它们提升为普通关系列，形成“XML payload + 可索引摘要列”的结构，不需要推翻整个设计。

最终建议

在当前需求下，XML 是合理的选择，但只应用于 code-specific configuration。流程、身份、状态、时间、版本和审计记录仍应保存在关系表中。

从第一天开始保存 SchemaDefinitionId 和不可变 revision，成本不高，却能避免以后无法解释历史数据。XSD、表单定义和业务验证也应分开处理：XSD 负责结构，UI metadata 负责显示，应用层规则负责流程阶段和复杂条件。这样既能适应 SQL Server 2000，也能在迁移到 SQL Server 2005 后逐步使用原生 XML 能力，不必重新设计整个系统。