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

问题描述

我最近问过几个关于数据库设计的问题，可能太多了;-)但是我很遗憾，我慢慢地用我的设计来解决这个问题的核心，并慢慢把它煮沸。我仍然在几个关于如何警报存储在数据库中的决定摔跤。

在此系统中，警报是必须被确认的实体，

最初，我将读数与这样的警报相关（非常减少）： -

  [Location] 
 LocationId 
 
 [Sensor] 
 SensorId 
 LocationId 
 UpperLimitValue 
 LowerLimitValue 
 
 [SensorReading] 
 SensorReadingId 
值
状态
时间戳
 
 [SensorAlert] 
 SensorAlertId 
 b $ b [SensorAlertReading] 
 SensorAlertId 
 SensorReadingId 
  

最后一个表是关联

这个设计的问题是，它允许来自许多传感器的读数，与每个警报相关联，而每个警报仅针对单个传感器，并且应仅具有与其相关联的传感器的读数（我应该被打扰，DB允许这样吗？）。

我想简化的东西，为什么还要干扰SensorAlertReading表？我可以这样做：

  [Location] 
 LocationId 
 
 [Sensor] 
 SensorId 
 LocationId 
 
 [SensorReading] 
 SensorReadingId 
 SensorId 
值
状态
时间戳
 b $ b [SensorAlert] 
 SensorAlertId 
 SensorId 
时间戳
 
 [SensorAlertEnd] 
 SensorAlertId 
时间戳
  

基本上我不是现在关联读数与警报 - 而是我只知道在开始和结束时间之间的警报是活动的特定的传感器，如果我想查找该警报的读数，我可以做。

显然，缺点是我不再有任何约束，阻止我删除在警报期间发生的读数，但我不确定约束是必需的。

现在从外部作为开发人员/ DBA查看，是否会让您生病或看起来合理吗？

有没有其他方法可以这样做，我可能会失踪？

谢谢。

strong> EDIT：
这是另一个想法 - 它的工作方式不同。它存储每个传感器状态变化，从正常到警报在表中，然后读数只是与特定状态相关联。这似乎解决了所有的问题 - 什么d'ya想？ （我不知道唯一的事情是调用表SensorState，我不能认为有一个更好的名字（也许SensorReadingGroup？）： -

  [Location] 
 LocationId 
 
 [Sensor] 
 SensorId 
 LocationId 
 
 [SensorState] 
 SensorStateId 
 SensorId 
时间戳
状态
 IsInAlert 
 
 [SensorReading] 
 SensorReadingId 
 SensorStateId 
值
时间戳
  

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

解决方案

修订01 Jan 11 21:50 UTC

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

不熟悉关系建模标准的读者可以找到 ▶IDEF1X Notation◀ 有用。

商家（评论中制定的规则） >

p>这些可以在关系中读取（在数据模型旁边读取）。业务规则和所有暗示的参考和数据完整性可以在任何ISO SQL数据库中实现，并由此受到RULES，CHECK约束的保证。这是IDEF1X的演示，在开发的两个关系键，实体和关系。注意，动词短语不仅仅是繁荣。

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

位置

$ b b

• A 位置包含一对多 Sensors


• A 位置可能有一个记录器

strong> NetworkSlave

• NetworkSlave收集一对多NetworkSensors的阅读资料






使用者






• 用户可以保持零到多位置


• code>可保持零到多传感器


• 用户用户可保持零到多 NetworkSlaves


• 用户执行零到多下载


• 用户 to-many 致谢，每一个警告


• $ c> User 可以采取零到多个操作，每个 ActionType






传感器







• A SensorType 安装为零到多传感器




• A 记录器（房屋和）收集阅读 $ c> LoggerSensor




• A 传感器 / em> one NetworkSensor 或一 LoggerSensor

  
  
  
  
  
  • A NetworkSensor 记录阅读 c> NetworkSlave
    
    。
  c $ c>一对多次
  
  
  
  • A LoggerSensor 记录阅读由一个记录器
    
    收集。
  
  
  
  
  • A 阅读可视为 / code>，一个 AlertType
    
    
    
    • AlertType 可能发生在零到多读数
      
      。
    
    
  
  
  • 警告可能是一个 code>，由一个用户
。
  
  
  • 确认 User
    
    >
    
    • Action 可以在零到多动作
    

  

  
  
  

  对评论的回复

  
  

  1. 粘贴 Id 一切移动的列，干扰标识符的确定，自然的关系键，数据库关系权力。它们是代理键，这意味着其他键和索引，它会阻碍关系力量;这导致比其它需要更多的连接。因此，只有当关系键变得太繁琐而无法迁移到子表（并接受强加的额外连接）时，我才使用它们。

  
  

  2. 可空键是一个经典未经规范化的数据库的症状。数据库中的空值是性能的坏消息;但是在FKs中的Null意味着每个表都做太多的事情，有太多的含义，结果是很差的代码。适合喜欢重构他们的数据库的人;

  
  

  3. 已解决：警告可能是已确认; 致谢可能是已采取行动。

  
  

  4. 行上方的列是主键（参考符号文档）。 SensorNo 是 LocationId 内的序列号;参考业务规则，在位置之外是无意义的;两列一起形成PK。当您准备好插入传感器（在您检查该尝试是否有效之后，等等），它派生如下。这不包括LoggerSensors，它为零：

       INSERT传感器VALUES（
      @LocationId，
      SensorNo =（SELECT ISNULL（MAX（SensorNo），0）+ 1 
      FROM Sensor 
      WHERE LocationId = @LocationId 
     ）
      @SensorCode 
     ）

     / li>
     

  5. 为了准确性或改进的意义，我已将 NetworkSlave监视器NetworkSensor 更改为 NetworkSlave从NetworkSensor 。

  
  

  6. 检查约束。 NetworkSensor 和 LoggerSensor 是 Sensor 的独占子类型，它们的完整性可以通过CHECK约束来设置。 警告，致谢和操作不是子类型，但它们的完整性由相同的方法设置，因此我将列出

     
     
     
     
     

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

     
     

     • 需要使用辨别器来识别 Sensor 这是 SensorNo = 0 for LoggerSensors ; NetworkSensors

     
     
     • NetworkSensors 和 LoggerSensors 受FK CONSTRAINTS限制为 NetworkSlave 和 Logger ;以及传感器。
     
     
     • 在 NetworkSensor 中，包含一个CHECK约束以确保 SensorNo 不为零
     
     

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

     


     • Acknowledgments > Actions 受到已识别的FK约束的约束（ Acknowledgement ; Action 如果没有确认就不能存在）。相反，没有确认的警报处于未确认状态; 警告与和确认，但没有操作确认但未采取行动的状态。
       。

     
     
  
  

  7. 提醒。在这种（实时监控和警报）应用程序的设计中的概念是许多小程序，独立运行;所有使用数据库作为单一版本的真相。一些程序插入行（ Readings，Alerts ）;其他程序轮询db的这种行的存在（和发送SMS消息等;或手持单元拾取与该单元相关的警报）。在这个意义上，db是一个可以被描述为一个消息框（一个程序放入行，另一个程序读取和动作）。

     
     
     

     code> 正在由 NetworkSlave 正在录制的 ，并且每分钟左右，插入一组新的 Readings 。后台进程定期执行（每分钟或任何事情），这是主要的监视程序，它将在其循环中有许多功能。一个这样的函数将监视 Readings 并产生自从（程序循环的）最后一次迭代以来发生的警报 。

     
     
     

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

      
       - 假设@LoopDateTime包含最后一次迭代的DateTime 
      INSERT警报
      SELECT LocationId，
      SensorNo，
      ReadingDtm，
     L -  AlertTypeLow
      FROM传感器s，
     读取r 
      WHERE s.LocationId = r.LocationId 
      AND s.SensorNo = r.SensorNo 
      AND r.ReadingDtm> @LoopDtm 
      AND r.Value< s.LowerLimit 
      INSERT警报
      SELECT LocationId，
      SensorNo，
      ReadingDtm，
     H -  AlertTypeHigh
      FROM传感器s b $ b Reading r 
      WHERE s.LocationId = r.LocationId 
      AND s.SensorNo = r.SensorNo 
      AND r.ReadingDtm> @LoopDtm 
      AND r.Value> s .UpperLimit  

     
     所以一个警告肯定是一个事实，在数据库中存在一行。随后可由用户（另一行/事实）和 Actioned 确认确认 / code>与与 ActionType 之间的。

     
     
     

     其他这个（由Projection行为创造的），即。一般和不变的情况下，我将警告仅作为警告中的一行引用

  
  

  8. 关注改变用户。这已经照顾，如下。在我的（修订昨天）答案的顶部，我声明主要识别元素是静态。我重新排序了业务规则以提高清晰度。

     
     
     
     
     

     • 由于您提及的原因， User.Name 良好的PK for User ，虽然它仍然是备用密钥（唯一）和用于人类交互的。

     
     

     • User.Name 不能重复，所以不能有多个 Fred ;可以是 FirstName-LastName ;两个 Fred Bloggs ，但不是 User.Name 。我们的第二个Fred需要选择另一个 User.Name 。注意已识别的索引。

     
     

     • UserId 是永久记录，它已经是PK。不要删除 User ，它具有历史意义。事实上，FK约束会阻止你（从不在真正的数据库中使用CASCADE，这是纯粹的疯狂）。

     
     

     • 或者（删除用户从未做过任何操作，并因此释放 User.Name 以供使用）allow Delete只要没有FK违反（即 UserId

     
     
     
     
     

     为确保只有用户是当前执行操作，请添加 IsObsolete boolean在用户（DM更新），并检查那个列被询问任何函数（报告除外）你可以实现一个视图 UserCurrent 只返回那些 Users 。

     
     
     

     c $ c>和 NetworkSlave 。如果你需要区分当前和历史，让我知道，我将添加 IsObsolete 给他们。

     
     
     

     我不知道：您可以定期清除古代历史数据的数据库，删除超过10年的行。

  


  随意问问题

  
  
  

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

  
  

  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?).

  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.

  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?

  Thanks.

  EDIT: 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

  Data Model

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

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

  Business (Rules Developed in the Commentary)

  I did identify some early business Rules, which are now obsolete, so I have deleted them

  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.

  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

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

  NetworkSlave

  • A NetworkSlave collects Readings for one-to-many NetworkSensors

  User

  • 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

  Sensor

  • A SensorType is installed as zero-to-many Sensors

  • A Logger (houses and) collects Readings for one LoggerSensor

  • A Sensor is either one NetworkSensor or one LoggerSensor

    • A NetworkSensor records Readings collected by one NetworkSlave
      .
  • A Logger is periodically Downloaded one-to-many times
    • A LoggerSensor records Readings collected by one Logger
      .
  • A Reading may be deemed in Alert, of one AlertType
    • An AlertType may happen on zero-to-many Readings
      .
  • An Alert may be one Acknowledgement, by one User .
  • An Acknowledgement may be closed by one Action, of one ActionType, by one User
    • An ActionType may be taken on zero-to-many Actions

  Responses to Comments

  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).

  2. 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.

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

  4. 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
         )

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

  6. 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.

     • 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)

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

     • The existence of NetworkSensors and LoggerSensors are constrained by the FK CONSTRAINTS to NetworkSlave and Logger, respectively; as well as to Sensor.
     • In NetworkSensor, include a CHECK constraint to ensure SensorNo is non-zero

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

     • 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. .

  7. 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).

     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).

     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

     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.

     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.

  8. 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.

     • 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 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 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.

     • 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).

     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.

     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.

     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.

  Feel free to ask Questions.

  Note the IDEF1 Notation document has been expanded.

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