我应该设计一个 SQL Server 数据库来依赖 UNION 还是避免它？

Question

采用以下 SQL 查询：

SELECT     Account, Amount AS Deposit, 0.00 AS Withdrawal, Date
FROM       Deposits
WHERE      Account = @Account
UNION
SELECT     Account, 0 AS Expr1, Amount, Date
FROM       Withdrawals
WHERE      Account = @Account
ORDER BY   Date Desc

与此相反：

SELECT    Account, TransactionType, Amount, Date
FROM      Transactions
WHERE     Account = @Account
ORDER BY  Date Desc

在第一个查询中，存款和取款存储在不同的表中，并且每当需要一起查看它们时（例如显示银行对账单时），它们就会 UNION 在一起。第二个查询将所有交易（存款和取款）存储在一张表中，并且通过 TransactionType 列区分交易类型。产生的查询结果并不完全相同，但假设客户对最终输出感到满意。

使用第一个模式设置数据库比第二个模式更好或更差是否有任何性能原因？

编辑：为了清楚起见，我想指出我问这个问题的原因是为了找出保持表较小并在必要时使用 UNION 是否有性能优势与使用一张大表使用 WHERE 子句来查找不同类型的数据相比。我用上面的例子来更好地表达我的问题。欢迎就其他原因提出哪种模式更好的建议，但也请尝试回答这个问题。

Answer 1

Transactions 似乎更合适，因为它们就是这样。虽然不太明显，但通过表格区分存款和取款并不比通过表格区分现金交易和支票交易更明智。

Answer 2

老实说，我认为性能不应该是您最关心的问题 - 在您给出的示例中，性能不太可能有显着差异。

但是，假设您想要计算任意时间点的帐户余额。如果你有一个“交易”表，你可以在单个查询中实现这一点 -

select sum(amount) from transactions

如果你分成两个表，你需要执行两个查询；一般来说，我预计这会比查询单个表花费两倍的时间，即使该表的记录数与其他两个表的记录数相同。

我认为您应该专注于最能代表业务领域的内容 - 如果您查看老式分类账，我认为您会发现贷方和借方都输入在同一列中。您的业​​务利益相关者更有可能从“交易”（积极或消极）的角度来思考，而不是根据存款和取款的单独概念来思考。

Answer 3

我会选择 Transaction 表。这使得在应用程序中实现 Transaction 基类变得更加容易。

如果您后来意识到需要自定义“提款”和“存款”列，那么您可以创建包含这些列的子类表。

Answer 4

从会计部门的角度来看，存款和取款都是无签名金额的交易。区别在于现金流的方向。这倾向于单一交易表，而不是单独的存款和取款表。

从 ER/数据建模的角度来看，如果我们从上述开始，那么问题是：存款和取款是否具有相同的属性集？如果他们这样做，那么你就有了一个强有力的迹象表明他们实际上是同一个实体。如果它们具有不同的属性，那么您可能会查看不同的子类型：也许父事务表保存公共属性，而子表则用于每种不同类型的事务：

create table dbo.TransactionBase
(
  id     int   not null primary key ,
  type   int   not null , -- 1:Deposit, 2:Withdrawal, 3:???, ...
  amount money not null ,
  -- ... other common attributes ,
)
create table dbo.TransactionDeposit
(
  transaction_id int not null
    primary key
    foreign key references dbo.TransactionBase(id) ,
  -- ... deposit-specific attributes
)
create table dbo.TransactionWithdrawal
(
  transaction_id int not null
    primary key
    foreign key references dbo.TransactionBase(id) ,
  -- ... withdrawal-specific attributes
)

在此模型中，基本事务表具有基数为 1 的关系每个子类型表之间的 - 到零或一。每个事务都存在于基表中，并且仅存在于一个子类型表中。

现在您可以两全其美。您只需进行一次选择即可处理整个交易（例如，计算帐户余额）。

如果您看到交易类型数量不断增加，这一点就更重要了。

Answer 5

我将创建一个组合了您需要的数据的视图。这样您就可以拥有有意义的表格以及查询信息的单一来源。您不想在同一个表中存储两个不同的内容。

通常，如果您担心性能，您可以创建索引视图，但不幸的是，由于您使用的是联合，所以您不能。如果您担心性能，您可以考虑创建一个使用联合数据加载的表。

Answer 6

我可能会将这两个放在一个表中，因为它们基本上是非常相似的“事务”实体，并且您可能希望将它们放在一起并一起索引以进行很多操作。您可以创建执行数据水平部分的视图，并且仅显示其中之一。如果存款或取款有不同的支持信息，这些信息也可能会出现在同一个表（其中一个或另一个具有 NULL）或辅助表中。

另请注意，您可能希望使用 UNION ALL 来避免 UNION 执行的重复数据删除（希望是不必要的）。

Answer 7

我会把它们放在同一张表中，我见过的每个商业会计系统都有一张表用于所有交易。这是有充分理由的。就用户的期望而言，它是有意义的，就新开发人员的期望而言，它是有意义的，并且最适合报告必须对两个表进行计算才能获得正确答案的位置。如果您希望两者之间存在不同的字段，则将它们放在单独的表中，但保留主要数据（尤其是在主表中报告所需的金额和其他项目）。

我使用一个系统（由其他人设计），其中不同类型的费用位于不同的表中，并且这种选择多年来导致了很多错误，因为新开发人员不知道他们应该在多个表中查找数据。我认为，这里单独的表也将是一个维护问题，人们在如何获取数据（包括使用 UNION，其中出于性能原因指示 UNION ALL）时做出奇怪的选择，而数据应该很简单。

Answer 8

如果您需要将结果分组在一起，我个人会使用这两个表并使用 UNION ALL。

通常不会提前考虑性能，并且这只是可能的许多查询之一，我会设计数据库以适应更频繁使用的查询，例如仅用于提款或存款的查找。将所有数据放在一起更简单，但您必须清除重复数据，这会导致查找时间更长。

换句话说，如果这个联合查询每天执行 100 次，但针对各个表的查找和更新每天发生 1000 次，那么请将它们分开。