关于传感器/读取/警报数据库设计的意见 [英] Opinions on sensor / reading / alert database design

问题描述

我最近问了几个关于数据库设计的问题，可能太多了；-) 但是我相信我正在慢慢地用我的设计触及问题的核心，并且正在慢慢地将其归结.关于警报"如何存储在数据库中，我仍在努力做出一些决定.

I've asked a few questions lately regarding database design, probably too many ;-) However I beleive I'm slowly getting to the heart of the matter with my design and am slowly boiling it down. I'm still wrestling with a couple of decisions regarding how "alerts" are stored in the database.

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

In this system, an alert is an entity that must be acknowledged, acted upon, etc.

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

Initially I related readings to alerts like this (very cut down) : -

[Location]
LocationId

[Sensor]
SensorId
LocationId
UpperLimitValue
LowerLimitValue

[SensorReading]
SensorReadingId
Value
Status
Timestamp

[SensorAlert]
SensorAlertId

[SensorAlertReading]
SensorAlertId
SensorReadingId

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

The last table is associating readings with the alert, because it is the reading that dictate that the sensor is in alert or not.

这种设计的问题在于它允许来自多个传感器的读数与单个警报相关联 - 而每个警报仅针对单个传感器，并且应该只有与该传感器相关联的读数(我是否应该对此感到困扰)数据库允许这样做吗?)

The problem with this design is that it allows readings from many sensors to be associated with a single alert - whereas each alert is for a single sensor only and should only have readings for that sensor associated with it (should I be bothered that the DB allows this though?).

我想简化事情，为什么还要麻烦 SensorAlertReading 表?相反，我可以这样做:

I thought to simplify things, why even bother with the SensorAlertReading table? Instead I could do this:

[Location]
LocationId

[Sensor]
SensorId
LocationId

[SensorReading]
SensorReadingId
SensorId
Value
Status
Timestamp

[SensorAlert]
SensorAlertId
SensorId
Timestamp

[SensorAlertEnd]
SensorAlertId
Timestamp

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

Basically I'm not associating readings with the alert now - instead I just know that an alert was active between a start and end time for a particular sensor, and if I want to look up the readings for that alert I can do.

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

Obviously the downside is I no longer have any constraint stopping me deleting readings that occurred during the alert, but I'm not sure that the constraint is neccessary.

现在作为一名开发人员/DBA，从外面看，这会让你想生病还是看起来合理?

Now looking in from the outside as a developer / DBA, would that make you want to be sick or does it seem reasonable?

是否还有其他我可能缺少的方法?

Is there perhaps another way of doing this that I may be missing?

谢谢.

这是另一个想法 - 它以不同的方式工作.它将每个传感器状态变化存储在一个表中，从正常到警报，然后读数与特定状态简单相关联.这似乎解决了所有问题——你怎么想?(我唯一不确定的是将表称为SensorState"，我不禁想到有一个更好的名字(也许是 SensorReadingGroup?):-

Here's another idea - it works in a different way. It stores each sensor state change, going from normal to alert in a table, and then readings are simply associated with a particular state. This seems to solve all the problems - what d'ya think? (the only thing I'm not sure about is calling the table "SensorState", I can't help think there's a better name (maybe SensorReadingGroup?) : -

[Location]
LocationId

[Sensor]
SensorId
LocationId

[SensorState]
SensorStateId
SensorId
Timestamp
Status
IsInAlert

[SensorReading]
SensorReadingId
SensorStateId
Value
Timestamp

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

There must be an elegant solution to this!

推荐答案

Revised 01 Jan 11 21:50 UTC

数据模型

我认为您的数据模型应该如下所示:▶传感器数据模型◀.(第 2 页与您关于历史的其他问题有关).

I think your Data Model should look like this:▶Sensor Data Model◀. (Page 2 relates to your other question re History).

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

Readers who are unfamiliar with the Relational Modelling Standard may find ▶IDEF1X Notation◀ useful.

业务(评论中制定的规则)

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

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

These can be "read" in the Relations (read adjacent to the Data Model). The Business Rules and all implied Referential and Data Integrity can be implemented in, and thus guaranteed by, RULES, CHECK Constraints, in any ISO SQL database. This is a demonstration of IDEF1X, in the development of both the Relational keys, and the Entities and Relations. Note the Verb Phrases are more than mere flourish.

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

Apart from three Reference tables, the only static, Identifying entities are Location, NetworkSlave, and User. Sensor is central to the system, so I ahve given it its own heading.

位置

• 一个Location包含一对多的Sensors
• 一个位置可能有一个记录器

• A Location contains one-to-many Sensors
• A Location may have one Logger

NetworkSlave

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

用户

• 一个 User 可以维护零到多个 Locations
• 一个User可以维护零到多个Sensors
• 一个User可以维护零对多的NetworkSlaves
• 一个用户可以执行零到多个下载
• 一个用户可以进行零对多的Acknowledgements，每个Alert
• 一个User可以采取零到多个Actions，每个ActionType

• An User may maintain zero-to-many Locations
• An User may maintain zero-to-many Sensors
• An User may maintain zero-to-many NetworkSlaves
• An User may perform zero-to-many Downloads
• An User may make zero-to-many Acknowledgements, each on one Alert
• An User may take zero-to-many Actions, each of one ActionType

传感器

• SensorType 安装为零对多 Sensors

一个 Logger(房子和)为一个 LoggerSensor

A Logger (houses and) collects Readings for one LoggerSensor

Sensor 是要么一个NetworkSensor 或一个LoggerSensor

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

• A NetworkSensor records Readings collected by one NetworkSlave
  .

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

• A LoggerSensor records Readings collected by one Logger
  .

• AlertType 可能发生在零对多的读数
  .

• 一个 ActionType 可以用于零对多的 Actions

• An ActionType may be taken on zero-to-many Actions

对评论的回复

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

1. Sticking Id columns on everything that moves, interferes with the determination of Identifiers, the natural Relational keys that give your database relational "power". They are Surrogate Keys, which means an additional Key and Index, and it hinders that relational power; which results in more joins than otherwise necessary. Therefore I use them only when the Relational key becomes too cumbersome to migrate to the child tables (and accept the imposed extra join).

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

Nullable keys are a classic symptom of an Unnormalised database. Nulls in the database is bad news for performance; but Nulls in FKs means each table is doing too many things, has too many meanings, and results is very poor code. Good for people who like to "refactor" their databases; completely unnecessary for a Relational database.

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

Resolved: An Alert may be Acknowledged; An Acknowledgement may be Actioned.

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

The columns above the line are the Primary Key (refer Notation document). SensorNo is a sequential number within LocationId; refer Business Rules, it is meaningless outside a Location; the two columns together form the PK. When you are ready to INSERT a Sensor (after you have checked that the attempt is valid, etc), it is derived as follows. This excludes LoggerSensors, which are zero:

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

为了准确或改进意义，我已将NetworkSlave 监控NetworkSensor 更改为NetworkSlave 从NetworkSensor 收集读数.

For accuracy or improved meaning, I have changed NetworkSlave monitors NetworkSensor to NetworkSlave collects Readings from NetworkSensor.

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

Check Constraints. The NetworkSensor and LoggerSensor are exclusive subtypes of Sensor, and their integrity can be set by CHECK constraints. Alerts, Acknowledgements and Actions are not subtypes, but their integrity is set by the same method, so I will list them together.

• 数据模型中的每个 Relation 在子(或子类型)中作为 CONSTRAINT 实现为 FOREIGN KEY (child_FK_columns) REFERENCES Parent (PK_columns)

• Every Relation in the Data Model is implemented as a CONSTRAINT in the child (or subtype) as FOREIGN KEY (child_FK_columns) REFERENCES Parent (PK_columns)

需要一个鉴别器来识别 Sensor 是哪个子类型.对于 LoggerSensors，这是 SensorNo = 0；NetworkSensors 的非零值.

A Discriminator is required to identify which subtype a Sensor is. This is SensorNo = 0 for LoggerSensors; and non-zero for NetworkSensors.

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

In LoggerSensor, include a CHECK constraint to ensure SensorNo is zero

Acknowledgements 和 Actions 的存在受到识别的 FK CONSTRAINTS 的约束(没有 就不能存在 AcknowledgementAlert；没有 Acknowledgement，Action 就不能存在.相反，没有 Acknowledgement 的 Alert 处于未确认状态；带有 Acknowledgement 但没有 Action 的 Alert 处于已确认但未操作的状态..

The existence of Acknowledgements and Actions are constrained by the identified FK CONSTRAINTS (An Acknowledgement cannot exist without an Alert; an Action cannot exist without an Acknowledgement). Conversely, an Alert with no Acknowledgement is in an unacknowledged state; an Alert with and Acknowledgementbut no Action is in an acknowledged but un-actioned state. .

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

Alerts. The concept in a design for this kind of (live monitoring and alert) application is many small programs, running independently; all using the database as the single version of the truth. Some programs insert rows (Readings, Alerts); other programs poll the db for existence of such rows (and send SMS messages, etc; or hand-held units pick up Alerts relevant to the unit only). In that sense, the db is a may be described as an message box (one program puts rows in, which another program reads and actions).

假设是，Sensors 的Readings 正在被NetworkSlave实时"记录，并且每分钟左右，一个新的集合Readings 已插入.后台进程定期执行(每分钟或其他时间)，这是主要的监控"程序，它将在其循环中具有许多功能.其中一个功能是监视Readings 并生成自上次迭代(程序循环)以来发生的Alerts.

The assumption is, Readings for Sensors are being recorded "live" by the NetworkSlave, and every minute or so, a new set of Readings is inserted. A background process executes periodically (every minute or whatever), this is the main "monitor" program, it will have many functions within its loop. One such function will be to monitor Readings and produce Alerts that have occurred since the last iteration (of the program loop).

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

The following code segment will be executed within the loop, one for each AlertType. It is a classic Projection:

-- 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 绝对是一个事实，它在数据库中作为一行存在.随后，可以由 User(另一行/事实)Acknowledged，并通过 ActionType 以 ActionType用户.

So an Alert is definitely a fact, that exists as a row in the database. Subsequently that may be Acknowledged by an User (another row/fact), and Actioned with an ActionType by an User.

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

Other that this (the creation by Projection act), ie. the general and unvarying case, I would refer to Alert only as a row in Alert; a static object after creation.

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

Concerns re Changing Users. That is taken care of already, as follows. At the top of my (revised yesterday) Answer, I state that the major Identifying elements are static. I have re-sequenced the Business Rules to improve clarity.

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

• For the reasons you mention, User.Name is not a good PK for User, although it remains an Alternate Key (Unique) and the one that is used for human interaction.

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

User.Name cannot be duplicated, there cannot be more than one Fred; there can be in terms of FirstName-LastName; two Fred Bloggs, but not in terms of User.Name. Our second Fred needs to choose another User.Name. Note the identified Indices.

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

UserId is the permanent record, and it is already the PK. Never delete User, it has historical significance. In fact the FK constraints will stop you (never use CASCADE in a real database, that is pure insanity). No need for code or triggers, etc.

或者(删除从不做任何事情的Users，从而释放User.Name供使用)允许删除，只要没有FK违规(即.UserId 在下载、确认、操作中未引用.

Alternately (to delete Users who never did anything, and thus release User.Name for use) allow Delete as long as there are no FK violations (ie. UserId is not referenced in Download, Acknowledgement, Action).

为了确保只有当前的 Users 执行 Actions，在 User (DM Updated) 中添加一个 IsObsolete 布尔值，并检查该列当针对任何函数(报告除外)查询该表时，您可以实现一个视图 UserCurrent，它只返回那些 Users.

To ensure that only Users who are Current perform Actions, add an IsObsolete boolean in User (DM Updated), and check that column when that table is interrogated for any function (except reports) You can implement a View UserCurrent which returns only those Users.

同样适用于 Location 和 NetworkSlave.如果您需要区分当前与历史，请告诉我，我也会向它们添加 IsObsolete.

Same goes for Location and NetworkSlave. If you need to differentiate current vs historical, let me know, I will add IsObsolete to them as well.

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

I don't know: you may purge the database of ancient Historical data periodically, delete rows that are (eg) over 10 years old. That has to be done from the bottom (tables) first, working up the Relations.

随时提出问题.

注意 IDEF1 Notation 文档已经扩展.

Note the IDEF1 Notation document has been expanded.

这篇关于关于传感器/读取/警报数据库设计的意见的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！