对传感器/读取/警报数据库设计的意见

Question

我最近问了一些关于数据库设计的问题，可能太多了;-)但是我相信我正在慢慢地通过我的设计找到问题的核心，并慢慢地将其简化。我仍在努力思考如何将“警报”存储在数据库中。

在这个系统中，警报是一个必须被确认、采取行动等的实体。

最初，我将读数与警报相关联，如下所示（非常精简）： -

[Location]
LocationId

[Sensor]
SensorId
LocationId
UpperLimitValue
LowerLimitValue

[SensorReading]
SensorReadingId
Value
Status
Timestamp

[SensorAlert]
SensorAlertId

[SensorAlertReading]
SensorAlertId
SensorReadingId

最后一个表将读数与警报相关联，因为它是读数指示传感器是否处于警戒状态。

这种设计的问题在于，它允许来自多个传感器的读数与单个警报相关联 - 而每个警报仅针对单个传感器，并且应该仅具有与其关联的该传感器的读数（我应该担心数据库允许不过这个？）。

我想简化事情，为什么还要费心使用 SensorAlertReading 表呢？相反，我可以这样做：

[Location]
LocationId

[Sensor]
SensorId
LocationId

[SensorReading]
SensorReadingId
SensorId
Value
Status
Timestamp

[SensorAlert]
SensorAlertId
SensorId
Timestamp

[SensorAlertEnd]
SensorAlertId
Timestamp

基本上我现在不会将读数与警报关联起来 - 相反，我只知道警报在特定传感器的开始时间和结束时间之间处于活动状态，并且如果我想查找该警报的读数我能做到。

显然，缺点是我不再有任何限制阻止我删除警报期间发生的读数，但我不确定该限制是否必要。

现在从外部来看，作为一名开发人员/DBA，这会让您感到恶心还是看起来合理？

也许还有我可能缺少的另一种方法吗？

谢谢。

编辑： 这是另一个想法——它以不同的方式运作。它将每个传感器状态变化（从正常到警报）存储在表中，然后将读数简单地与特定状态相关联。这似乎解决了所有问题 - 你觉得怎么样？ （我唯一不确定的是将该表称为“SensorState”，我不禁想到有一个更好的名称（也许是 SensorReadingGroup？）： -

[Location]
LocationId

[Sensor]
SensorId
LocationId

[SensorState]
SensorStateId
SensorId
Timestamp
Status
IsInAlert

[SensorReading]
SensorReadingId
SensorStateId
Value
Timestamp

必须有一个优雅的解决方案！

Answer 1

修订于 1 月 11 日 21:50 UTC

数据模型

我认为您的数据模型应该如下所示：▶传感器数据模型◀。 （第 2 页与您有关历史的其他问题相关）。

不熟悉关系建模标准的读者可能会发现▶ IDEF1X 表示法◀很有用。

业务（在评论中制定的规则）

我确实发现了一些早期的业务规则，这些规则现在已经过时了，所以我已经删除了它们

这些可以在关系中“阅读”（请阅读数据模型旁边的内容）。业务规则和所有隐含的引用和数据完整性都可以在任何 ISO SQL 数据库中的 RULES、CHECK 约束中实现，并由此得到保证。这是 IDEF1X 在关系键以及实体和关系的开发中的演示。请注意，动词短语不仅仅是华丽的。

除了三个参考表之外，唯一的静态识别实体是位置、网络从站和用户。传感器是系统的核心，所以我给了它自己的标题。

位置

• 一个位置包含一对多传感器
• 一个位置可能有一个记录器

NetworkSlave

• 收集一对多网络传感器的读数

用户

• 一个用户可以维护零到多个位置
• 一个用户 可以维护零到多个传感器
• 一个用户 可以维护零到多个NetworkSlaves
• 一个用户 code> 可以执行零到多的下载
• 一个用户可以进行零到多的确认，每个警报< /code>
• 一个用户可以采取零到多个Actions，每一个ActionType

Sensor

• SensorType 安装为零到多 传感器

• 一个记录器（容纳并）收集一个LoggerSensor的读数

• A Sensor 是其中一个一个NetworkSensor 或 一个 LoggerSensor

  • NetworkSensor 记录一个 NetworkSlave 收集的读数
    。
• Logger 定期一对多下载
  • LoggerSensor 记录一个 Logger 收集的读数
    。
• 一个Reading可以被认为是一个AlertType的Alert
  • AlertType 可能会发生在零到多读数上
    。
• 一个警报可能是一个用户的一个确认
  。
• 确认可以由一个Action、一个ActionType、一个User关闭
  • 一个 ActionType 可以用于零到多个 Actions

对评论的响应

1. 粘贴 Id 列上所有移动的东西都会干扰标识符的确定，标识符是赋予数据库关系“能力”的自然关系键。它们是代理键，这意味着附加键和索引，它阻碍了关系的力量；这会导致比其他必要的连接更多的连接。因此，只有当关系键变得太麻烦而无法迁移到子表（并接受强加的额外联接）时，我才使用它们。

2. 可空键是非规范化数据库的典型症状。数据库中的空值对于性能来说是个坏消息；但是 FK 中的 Null 意味着每个表做了太多的事情，有太多的含义，并且结果是非常糟糕的代码。适合喜欢“重构”数据库的人；对于关系数据库来说完全没有必要。

3. 已解决：警报可能已已确认； 确认可能会已采取行动。

4. 该行上方的列是主键（请参阅符号文档）。 SensorNo 是 LocationId 内的连续编号；参考业务规则，在 Location 之外没有任何意义；两列共同构成PK。当您准备插入传感器时（在检查尝试是否有效等之后），它将按如下方式导出。这不包括 LoggerSensors，其为零：

   INSERT Sensor VALUES (
       @LocationId，
       传感器编号 = ( SELECT ISNULL(MAX(传感器编号), 0) + 1
           来自传感器
           WHERE 位置 ID = @LocationId
           ）
       @SensorCode
       )

5. 为了准确性或改进的含义，我已将 NetworkSlave 监视 NetworkSensor 更改为 NetworkSlave 从 NetworkSensor 收集读数。

6. 检查约束。 NetworkSensor 和 LoggerSensor 是 Sensor 的专有子类型，它们的完整性可以通过 CHECK 约束设置。 Alerts、Acknowledgements 和 Actions 不是子类型，但它们的完整性是通过相同的方法设置的，所以我将它们一起列出。

   • 数据模型中的每个关系都作为子类型（或子类型）中的约束实现为 FOREIGN KEY (child_FK_columns) REFERENCES Parent (PK_columns)

   • 需要鉴别器来识别传感器属于哪种子类型。这是 LoggerSensors 的 SensorNo = 0；对于 NetworkSensors 来说非零。

   • NetworkSensors 和 LoggerSensors 的存在分别受到 NetworkSlave 和 Logger 的 FK CONSTRAINTS 约束；以及传感器。
   • 在 NetworkSensor 中，包含 CHECK 约束以确保 SensorNo 非零

   • 在 LoggerSensor 中，包含 CHECK 约束以确保 SensorNo 为零

   • Acknowledgements 和 Actions 的存在受到已识别的 FK CONSTRAINTS 的约束（如果没有 Acknowledgement 就不可能存在）警报；没有确认就不可能存在操作）。相反，没有 Acknowledgement 的 Alert 处于未确认状态；带有确认但没有操作的警报处于已确认但未操作状态。
     。

7. 警报。这种（实时监控和警报）应用程序的设计概念是许多独立运行的小程序；所有这些都使用数据库作为事实的单一版本。有些程序插入行（读数、警报）；其他程序轮询数据库是否存在此类行（并发送 SMS 消息等；或者手持设备仅拾取与该设备相关的警报）。从这个意义上说，数据库可以被描述为一个消息框（一个程序将行放入其中，另一个程序读取并执行操作）。

   假设是，传感器的读数由NetworkSlave“实时”记录，并且每分钟左右记录一组新数据已插入 Readings。后台进程定期执行（每分钟或其他），这是主要的“监视”程序，它将在其循环中具有许多功能。其中一个功能是监视读数并生成自上次迭代（程序循环）以来发生的警报。

   以下代码段将在循环内执行，每个警报类型对应一个。这是一个经典的投影：

   --假设@LoopDateTime包含最后一次迭代的DateTime
   插入警报
       选择位置 ID，
              传感器没有，
              读Dtm，
              “L”——警报类型“低”
           来自传感器，
                阅读 r
           WHERE s.LocationId = r.LocationId
           AND s.SensorNo = r.SensorNo
           AND r.ReadingDtm > @LoopDtm
           AND r.Value < s.下限
   插入警报
       选择位置 ID，
              传感器没有，
              读Dtm，
              “H”——警报类型“高”
           来自传感器，
                阅读 r
           WHERE s.LocationId = r.LocationId
           AND s.SensorNo = r.SensorNo
           AND r.ReadingDtm > @LoopDtm
           AND r.值> s.UpperLimit

   因此，Alert 绝对是一个事实，它作为数据库中的一行存在。随后，可能会被用户（另一行/事实）确认，并由ActionType通过ActionType进行操作。 用户。

   除此之外（通过投影行为进行创作），即。在一般且不变的情况下，我只会将 Alert 引用为 Alert 中的一行；创建后的静态对象。

8. 关于更改用户的担忧。这已经得到解决，如下所示。在我（昨天修改的）答案的顶部，我声明主要的识别元素是静态。我对业务规则进行了重新排序以提高清晰度。

   • 由于您提到的原因，User.Name 对于 User 来说并不是一个好的 PK，尽管它仍然是一个备用密钥（唯一）并且用于人际交互。

   • User.Name不能重复，不能有多个Fred；可以用名字-姓氏表示；两个Fred Bloggs，但不是User.Name。我们的第二个 Fred 需要选择另一个 User.Name。记下已确定的指数。

   • UserId是永久记录，已经是PK了。千万不要删除User，它具有历史意义。事实上，FK 约束会阻止你（永远不要在真正的数据库中使用 CASCADE，那纯粹是疯狂的）。不需要代码或触发器等。

   • 或者（删除从未执行过任何操作的Users，从而释放User.Name以供使用）只要不存在 FK 违规（即UserId在下载、确认、操作中未被引用。

   要确保只有当前的用户执行操作，请在 User (DM Updated) 中添加 IsObsolete 布尔值，并检查该列当该表被询问任何函数（报告除外）时，您可以实现一个 View UserCurrent ，它仅返回那些 Users。

   Location 和 NetworkSlave 也是如此。如果您需要区分当前与历史，请告诉我，我也会向它们添加 IsObsolete。

   我不知道：您可以定期清除数据库中的古代历史数据，删除（例如）超过 10 年的行。这必须首先从底部（表格）开始，建立关系。

随意提问。

请注意，IDEF1 表示法文档已被扩展。

Answer 2

这是我对这个问题的两点看法。

AlertType 表包含所有可能的警报类型。 AlertName 可能是高温、低压、低水位等。

AlertSetup 表允许为特定警报类型设置来自传感器的警报阈值。
例如，TresholdLevel = 100 和 TresholdType = 'HI' 应在读数超过 100 时触发警报。

读数表保存流式传输的传感器读数进入服务器（应用程序）。

警报表保存所有警报。它保留触发警报的第一个读数和完成警报的最后一个读数的链接（FirstReadingId、LastReadingId）。如果 (SensorId, AlertTypeId) 组合存在活动警报，则 IsActive 为 true。仅当读取低于警报阈值时，IsActive 才能设置为 false。 IsAcknowledged 表示操作员已确认警报。

1. 应用层将新的读数插入到Reading表中，捕获ReadingId。

2. 然后，应用程序根据每个（SensorId、AlertTypeId）组合的警报设置检查读数。此时，将创建对象集合 {SensorId, AlertTypeId, ReadingId, IsAlert}，并为每个对象设置 IsAlert 标志。

3. 然后检查Alert表中集合中每个对象{SensorId, AlertTypeId, ReadingId, IsAlert}的活动警报。

   • 如果 IsAlert 为 TRUE，并且 (SensorId, AlertTypeId) 组合没有活动警报，则创建一个新行添加到 Alert 表中，其中 FirstReadingID 指向当前 ReadingId。 IsActive 设置为 TRUE，IsAcknowledged 设置为 FALSE。

   • 如果 IsAlert 为 TRUE，并且 (SensorId, AlertTypeId) 组合存在活动警报，则该行为通过设置指向当前 ReadingId 的 LastReadingID 进行更新。

   • 如果 IsAlert 为 FALSE，并且 (SensorId, AlertTypeId) 组合存在活动警报，则该行为通过设置 IsActive FALSE 进行更新。

   • 如果 IsAlert 为 FALSE，并且 (SensorId, AlertTypeId) 组合没有活动警报，则 警报表未修改。

Answer 3

这里您必须处理的主要“三角”是传感器、[传感器]读取和警报。假设您必须跟踪正在发生的活动（而不是“一次加载全部”设计），您的第三个解决方案与我们最近所做的类似。经过一些调整，它看起来像：

[Location] 
LocationId 

[Sensor] 
SensorId 
LocationId 
CurrentSensorState  --  Denormalized data!

[SensorReading] 
SensorReadingId 
SensorState
Value 
Timestamp 

[SensorStateLog] 
SensorId 
Timestamp 
SensorState
Status   --  Does what?
IsInAlert 
(Primary key is {SensorId, Timestamp})

“SensorState”可以是 SensorStateId，并有一个关联的查找表列出（并限制）所有可能的状态。

这个想法是，您的传感器每个传感器包含一行并显示其当前状态。 SensorReading 随传感器读数不断更新。如果给定传感器的当前状态发生变化（即新读数的状态与传感器的当前状态不同），您可以更改当前状态并向 SensorStateLog 添加一行以显示状态的变化。 （或者，您可以使用“状态结束”时间戳更新该传感器的“先前”条目，但这是要编写的繁琐代码。）

传感器表中的 CurrentSensorState 是非规范化数据，但如果维护得当（并且如果您有数百万个传感器）行）它将大大提高查询当前状态的效率，因此可能值得付出努力。

所有这一切的明显缺点是警报不再是一个实体，并且它们变得更难以跟踪和识别。如果这些必须易于立即识别和使用，那么您的第三个方案将无法满足您的需要。