学生信息汇总的数据模型

Question

我在一个学区工作，管理人员想知道的很多事情之一就是一个学生在给定的时间范围内有多少个格子。我当然可以从考勤表中检索计数，但我想知道如何总结数据，这样做是否更快/更好。所以，我设计了一张桌子，它看起来像：

create table stu_summary (
id int identity primary key nonclustered not null,
stu_id char(10) not null primary key references student (stu_id),
group_name varchar(50) not null,
item_name varchar(50),
value varchar(100))

这样我就可以获得如下数据：

1, 'abc112233', 'tardy-count', '2011-F-21-99383', 2

这意味着，这一记录表明，学生abc112233在2011学年的进步期为21和id 99383学年有2件格子。

有更好的方法来存储这种信息吗？我希望保持表的灵活性，而不是将其绑定到特定的数据列。我是不是往坏的方向走了？您做了什么来存储这种日期跨度特定的摘要？

编辑

关于数据的数量和有关表格的数量，有一些猜测，所以让我来说明一下：

• 每天有70000排的出勤记录，每月120万
• 每个学生的迟到计数将显示在一个网格出勤窗口中。这一点很重要，因为学生通常有6节课，因此他们的出勤率每天都很高。
• 考勤表中有一个code列，确定它是哪种考勤。延迟代码就是这些值之一。

Answer 1

立即计算出这些计数。

对于大多数这种性质的数据集，我可以想象计算延迟事件的数量会相对便宜。在长期运行中，您希望在students或attendance表中有多少行？可能最多只有几万人。这与一家拥有数十亿笔交易的大型银行从其交易历史记录中计算帐户余额形成鲜明对比。在这种情况下，我会考虑将这些聚合的摘要保存在某个地方。

如果您想预先计算这些数据，我会按照最上面的答案是中的建议，在索引视图中总结每个学生每年迟到事件的数量。

例如：

CREATE VIEW dbo.tardiness_summary
WITH SCHEMABINDING
AS
SELECT
     student_id
   , year
   , COUNT_BIG(*) AS tardy_count
FROM dbo.attendance
GROUP BY 
     student_id
   , year
;

CREATE UNIQUE CLUSTERED INDEX IX_tardiness_summary
ON dbo.tardiness_summary (
     student_id
   , year
;

但是，这不是一种灵活的方法，因为这个视图现在是模式绑定到基表，因此对视图或表的任何修改都需要重新构建视图。索引视图也有关于如何创建或查询它们的许多限制。它们的优点是保证汇总表与其源保持同步，因为数据库引擎现在正在为您完成此工作。

Answer 2

您应该避免对任何有可能被修改的数据进行这种去规范化/总结。如果您有大量的事务性数据必须被多次、多次地查询，并且事务是在您正在总结的时间框架内只读的，那么构建一个像您正在考虑的那样的汇总表并不是太糟糕。

但是，您必须准备好忍受汇总表与原始事务不同步的风险。这意味着要么生活在索引视图的反面，要么手工构建(并维护)重新同步逻辑，要么准备承受数据不一致的痛苦--或者这些数据的组合。

在大型事务性系统中，有时需要咬紧牙关，构建用于报告的汇总表。我建议您仔细考虑您的特定应用程序示例是否需要这样做，或者是否需要实时报告(甚至是索引视图)。