database-design - 优化模式以捕获出勤数据的最佳方法是什么

Question

我们有一个体育训练营，城市中的各个团队定期参加。我们每天有一个时间跨度为 2 小时(上午 9 点到 11 点)的 session ，时间段可能因不同的团队而异。我们想捕捉每天参加训练营的人。

我们得出以下模型来捕获出席情况。(id, user_id, date, present)。假设用户每天都参加夏令营(比如一个月有 30 天)，您会在数据库中看到那么多记录。

假设我们只对找出用户参加训练营的天数感兴趣，是否有更好的方法来标记特定用户的存在或不存在(也许只有一个月的一行并标记所有个别日子，例如 (P,P,P,A, ...,A,P)。P = Present，A = Absent

Answer 1

您在问题标题中使用了“优化”一词，但没有解释您要优化的内容。

如果您谈论的是查询性能，那么您没有问题。您可以拥有的记录数取决于您每天的 session 数(因为只有一个团队可以参加任何给定 session )。如果您每天运行 10 个 session ，那就是每月 300 条记录。如果您每天运行一百个 session ，那么一个月就有三千条记录。这些不是大量的数据。因此，您通过扭曲数据库设计来避免不存在的性能问题是一个错误的决定。

您在其中一条评论中提到了电子表格。这不是一个糟糕的设计。最上面一行是 session ，下面是团队，单元格显示团队是否出席 session 。这些映射到三个数据库表:SESSIONS、TEAMS 和交集表 TEAM_SESSIONS。当团队参加 session 时，您只需要在 TEAM_SESSIONS 中记录。

作为概念证明，我在 Oracle 中创建了三个表。

SQL> desc teams
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER
 NAME                                               VARCHAR2(20 CHAR)

SQL> desc sessions
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER
 SSN_DAY                                            DATE
 SSN_START                                          NUMBER(4,2)
 SSN_END                                            NUMBER(4,2)

SQL> desc team_sessions
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TEAM_ID                                   NOT NULL NUMBER
 SESSION_ID                                NOT NULL NUMBER

SQL>

Oracle 11g 中引入的 PIVOT 函数使得构建矩阵变得轻而易举(不同风格的 DBMS 将有不同的方法来处理这个问题)。如您所见，今天有三支球队预订了类(class)，没有人愿意在午餐时间训练，而 Bec United 非常热衷(或需要训练)!

SQL> select * from (
  2      select t.name as team_name
  3             , trim(to_char(s.ssn_start))||'-'||trim(to_char(s.ssn_end)) as ssn
  4             , case when ts.team_id is not null then 1 else 0 end as present
  5      from   sessions s
  6             cross join teams t
  7             left outer join team_sessions ts
  8                  on (ts.team_id = t.id
  9                      and ts.session_id = s.id )
 10      where s.ssn_day = trunc(sysdate)
 11      )
 12  pivot
 13      ( sum (present)
 14        for ssn in ( '9-11', '11-13', '13-15', '15-17', '17-19')
 15      )
 16  order by team_name
 17  /

TEAM_NAME                '9-11'    '11-13'    '13-15'    '15-17'    '17-19'
-------------------- ---------- ---------- ---------- ---------- ----------
Balham Blazers                0          1          0          0          0
Bec United                    1          0          0          0          1
Dinamo Tooting                0          0          0          0          0
Melchester Rovers             0          0          0          1          0

SQL>

不管怎么说，这种数据模型的优点就是灵活。我们可以统计一个团队参加的频率、他们参加的时间、他们参加的星期几、哪些 session 总是被预订、哪些 session 很少被预订等等。此外，管理数据也很容易。特别是，三 table 解决方案相对于两 table 的优势在于更容易防止重复预订和非标准或重叠时段。

你看，规范化不仅仅是我们用来欺骗无辜者的一些月球语言，它提供了真正的实际好处。在极少数情况下，降低到至少 BCNF 不是最好的主意。