sql - 关于传感器/读取/警报数据库设计的意见

Question

我最近问了一些关于数据库设计的问题，可能太多了 ;-) 但是我相信我正在慢慢地用我的设计触及问题的核心，并且正在慢慢地将其煮沸。我仍在纠结于如何将“警报”存储在数据库中的几个决定。

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

最初，我将读数与这样的警报相关联(非常减少):-

[Location]
LocationId

[Sensor]
SensorId
LocationId
UpperLimitValue
LowerLimitValue

[SensorReading]
SensorReadingId
Value
Status
Timestamp

[SensorAlert]
SensorAlertId

[SensorAlertReading]
SensorAlertId
SensorReadingId

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

这种设计的问题在于它允许来自多个传感器的读数与单个警报相关联 - 而每个警报仅针对单个传感器，并且应该只具有与其关联的该传感器的读数(我是否应该为 DB 允许这虽然？)。

我想简化事情，为什么还要费心使用 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

世界标准时间 11 月 1 日 21:50 修订

数据模型

我认为你的数据模型应该是这样的:▶Sensor Data Model◀。 (第 2 页与您关于历史的其他问题有关)。

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

业务(注释中制定的规则)

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

这些可以在关系中“读取”(与数据模型相邻读取)。业务规则和所有隐含的参照和数据完整性都可以在任何 ISO SQL 数据库中的规则、检查约束中实现，并因此得到保证。这是 IDEF1X 的演示，用于开发关系键以及实体和关系。请注意动词短语不仅仅是蓬勃发展。

除了三个 Reference 表之外，唯一的静态标识实体是 Location、NetworkSlave 和 User。传感器是系统的核心，所以我给了它自己的标题。

位置

Location包含一对多 Sensors

Location可能有一个记录器

网络从站

NetworkSlave 收集一对多网络传感器

的读数

用户

User 可以保持零对多 Locations

User 可以保持零对多 Sensors

User 可以保持零对多 NetworkSlaves

User 可以执行零对多 Downloads

一个User可以使零对多Acknowledgements，每一个Alert

一个 User 可以取零对多 Actions ，每一个 ActionType

传感器

SensorType被安装为零对多 Sensors

一个Logger(房屋和)收集Readings一个LoggerSensor

Sensor是一个NetworkSensor或一个LoggerSensor

一个NetworkSensor记录了一个Readings收集的NetworkSlave.

Logger是周期性的Downloaded一对多

一个LoggerSensor记录了一个Readings收集的Logger.

Reading可被视为Alert中的AlertType

AlertType可能发生在零对多的Readings上
.

一个Alert可能是一个Acknowledgement，由一个用户
.

一个Acknowledgement可以被一个Action、一个ActionType、一个User关闭

ActionType 可用于零对多 Actions

回复评论

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

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

已解决:Alert可能是Acknowledged； Acknowledgement可以是Actioned。

行上方的列是主键(请参阅 Notation 文档)。 SensorNo是LocationId内的连续编号；引用业务规则，在Location之外是没有意义的；两列一起形成PK。当您准备好插入传感器时(在您检查尝试是否有效等之后)，其推导如下。这不包括 LoggerSensors，它们为零:

INSERT Sensor VALUES (
    @LocationId,
    SensorNo = ( SELECT ISNULL(MAX(SensorNo), 0) + 1
        FROM Sensor
        WHERE LocationId = @LocationId
        )
    @SensorCode
    )

为了准确或改进含义，我将NetworkSlave monitors NetworkSensor改为NetworkSlave collects Readings from NetworkSensor。

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

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

需要鉴别器来识别Sensor是哪个亚型。这是SensorNo = 0的LoggerSensors； NetworkSensors的非零值。

NetworkSensors和LoggerSensors的存在分别被FK CONSTRAINTS约束为NetworkSlave和Logger；以及传感器。

在 NetworkSensor 中，包含一个 CHECK 约束以确保 SensorNo 非零

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

Acknowledgements和Actions的存在受已识别的 FK 约束(Acknowledgement不能没有Alert而存在；Action不能没有Acknowledgement而没有 67)。相反，没有Alert的Acknowledgement处于未确认状态；带有Alert但没有Acknowledgement的Action处于已确认但未采取行动的状态。
.

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

假设是，Readings的Sensors正在被NetworkSlave“现场”录制，并且每隔一分钟左右，插入一组新的Readings。后台进程定期执行(每分钟或其他时间)，这是主要的“监控”程序，它将在其循环中具有许多功能。一个这样的函数是监视Readings并产生自上次迭代(程序循环)以来发生的Alerts。

以下代码段将在循环内执行，每个 AlertType 对应一个。这是一个经典的投影:

-- Assume @LoopDateTime contains the DateTime of the last iteration
INSERT Alert
    SELECT LocationId,
           SensorNo,
           ReadingDtm,
           "L"          -- AlertType "Low"
        FROM Sensor  s,
             Reading r
        WHERE s.LocationId = r.LocationId
        AND   s.SensorNo   = r.SensorNo
        AND   r.ReadingDtm > @LoopDtm
        AND   r.Value      < s.LowerLimit
INSERT Alert
    SELECT LocationId,
           SensorNo,
           ReadingDtm,
           "H"          -- AlertType "High"
        FROM Sensor  s,
             Reading r
        WHERE s.LocationId = r.LocationId
        AND   s.SensorNo   = r.SensorNo
        AND   r.ReadingDtm > @LoopDtm
        AND   r.Value      > s.UpperLimit

所以Alert绝对是一个事实，它作为数据库中的一行存在。随后可能是Acknowledged加上User(另一行/事实)，Actioned加上ActionType加上User。

除此之外，这(投影法的创作)，即。在一般且不变的情况下，我只会将Alert称为Alert中的一行；创建后的静态对象。

关注改变Users。这已经被处理了，如下所示。在我(昨天修改的)答案的顶部，我声明主要的识别元素是静态。我已对业务规则重新排序以提高清晰度。

由于您提到的原因，User.Name对于User来说不是一个好的PK，尽管它仍然是一个备用 key (唯一)并且是用于人机交互的 key 。

User.Name不能重复，Fred不能超过一个；可以有FirstName-LastName；两个Fred Bloggs，但不是User.Name。我们的第二个 Fred 需要选择另一个User.Name。注意确定的指数。

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

或者(删除从不做任何事情的Users，从而释放User.Name供使用)允许删除，只要不存在 FK 违规(即UserId是而不是“104567”中引用的

) Download, Acknowledgement, Action)。

为了确保只有当前的Users执行Actions，在用户(DM更新)中添加一个IsObsolete bool 值，并在查询该表的任何功能(报告除外)时检查该列您可以实现一个 View UserCurrent 只返回那些 Users 。
Location和NetworkSlave也是如此。如果您需要区分当前与历史，请告诉我，我也会为它们添加IsObsolete。

我不知道:您可能会定期清除古代历史数据的数据库，删除(例如)超过 10 年的行。这必须首先从底部(表格)开始，建立关系。
随意问的问题。

请注意，IDEF1 Notation 文档已被扩展。