数据库限制还能走多远？
这个问题与我问的另一个问题有关。另一个问题是我询问人们关于构建数据库的 3 种不同方式的意见。我能想到的最简洁的方法是选项 2，而无需（实际上）重复表和奇怪的概念（例如“超级表”）：
Location [Table]
- Id
- Name
- HasLogger
- LoggerRFID
- LoggerUpperLimit
- LoggerLowerLimit
Sensor [Table]
- Id [PK]
- LocationId [FK]
- UpperLimit
- LowerLimit
SensorReading [Table]
- Id [PK]
- SensorId [FK]
- Value
LoggerReading [Table]
- LocationId [FK]
- Value
Alert [Table]
- Id [PK]
AlertCorrectiveAction [Table]
- AlertId [FK]
- CorrectiveActionId [FK]
- ByUserId [FK]
AlertAcknowledgement [Table]
- AlertId [FK]
- ByUserId [FK]
SensorAlertReading [Table]
- AlertId [FK]
- SensorReadingId [FK]
LoggerAlertReading [Table]
- AlertId [FK]
- LoggerReadingId [FK]
现在此选项的问题是它允许“链接”来自多个传感器和多个位置的读数到单个警报。
为了详细说明为什么这是一个问题，我将解释系统的工作原理：
一个位置可以包含许多“实时传感器”，但只能包含 1 个记录器。因此，我将记录器属性放入位置表中（它实际上是一对一的关系）。记录器收集读数，直到稍后收集，实时传感器立即通过网络传递读数，并且它们具有额外的属性，例如具有网络地址属性的网络从属设备..与记录器有很大不同（我曾尝试将记录器视为传感器，但没有效果不太好）。
当传感器或记录仪超出范围（由读数指示）时，系统会生成警报。该警报仅针对该传感器，并且在该传感器（或记录器）的读数表明其回到范围内之前被视为处于活动状态。在那之前，使传感器进一步超出范围的读数会“链接”到同一警报。
因此，正如您所看到的，单个警报实际上应该只有与其链接的同一传感器的读数，但是我上面的设计允许来自不同传感器和记录器的不同读数与同一警报相关联 - 我应该担心我没有'是否以某种方式限制了这一点？另一个问题是它允许警报在没有任何读数的情况下存在。
因此我的问题是：人们应该在多大程度上接受约束或改变设计以适应这些约束？我喜欢上面的设计，因为它很简单 - 警报可以具有传感器读数和记录器读数，因此链接它们的关系很简单。
我忍不住想我也错过了一个技巧——是否有更好的方法来完成这个设计？我已经用它兜圈子很多年了，似乎总是有一个折衷方案（除非我重复不同阅读类型的所有警报表）。
谢谢。
This question is related to another question I asked. In my other question I ask peoples opinions about 3 different ways I could construct a database. The cleanest way I can think of doing it without (practically) repeating tables and strange notions such as "super tables" is option 2:
Location [Table]
- Id
- Name
- HasLogger
- LoggerRFID
- LoggerUpperLimit
- LoggerLowerLimit
Sensor [Table]
- Id [PK]
- LocationId [FK]
- UpperLimit
- LowerLimit
SensorReading [Table]
- Id [PK]
- SensorId [FK]
- Value
LoggerReading [Table]
- LocationId [FK]
- Value
Alert [Table]
- Id [PK]
AlertCorrectiveAction [Table]
- AlertId [FK]
- CorrectiveActionId [FK]
- ByUserId [FK]
AlertAcknowledgement [Table]
- AlertId [FK]
- ByUserId [FK]
SensorAlertReading [Table]
- AlertId [FK]
- SensorReadingId [FK]
LoggerAlertReading [Table]
- AlertId [FK]
- LoggerReadingId [FK]
Now the problem with this option is that it allows readings from multiple sensors and multiple locations to be "linked" to a single alert.
To expand on why this is a problem, I will explain how the system works:
A location can contain many "live sensors", but only 1 logger. For this reason I put the logger attributes into the location table (it was effictively a 1 to 1 relationship). A logger collects readings until it is later collected, live sensors communicate readings immediately via a network and they have extra attributes like network slaves which have network address attributes.. so fairly different to loggers (I tried treating loggers as sensors at one point, didn't work out well).
When a sensor or logger goes out of range (indicated by the reading) the system generates an alert. The alert is for that sensor only and is considered active until a reading for that sensor (or logger) indicates that it is back in range. Until that time, readings that take the sensor further out of range are "linked" to that same alert.
So as you can see, a single alert should really only have readings for the same sensor linked to it, however my design above allows different reading from different sensors and loggers to be associated with the same alert - should I be bothered that I haven't constrained that somehow? The other problem is that it allows alerts to exist without having any readings.
Hence my question; just how far should one go with constraints or bending a design to fit those constraints? I like the design above because it is simple - alerts can have sensor readings and logger readings, so it's a simple relation to link them.
I can't help thinking I'm missing a trick too - is there just a much better way to do this design? I've gone round in circles with it for ages now and there always seems to be a compromise (unless I repeat all the alert tables for the different reading types).
Thanks.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
是的。
你犯了两个基本错误。
将
Id物联网键粘贴在所有移动的物体上。这阻碍了您对数据进行建模的能力，作为数据（不是作为没有意义的行，而是具有人为强制的唯一性）和公开标识符；和依赖性（例如，传感器依赖于位置）。您正在使用包含数据的预设 Row_Ids 对电子表格进行建模。您需要将数据标准化为数据。
这导致了您所发现的问题，但还存在其他问题。
如果您对数据进行建模，标识符将是清晰的，并且索引和 FK 约束将防止这种情况发生。哪些数据是独立的；哪些数据属于（依赖于）哪些其他数据；哪些数据对其他数据做了什么，以及这些操作的基础。
然后（主要问题已得到解决）您只剩下一些较小的限制来解决次要区域。
否则，您将不得不到处添加约束来尝试并获得您想要的东西，但永远无法完全实现目标。你知道你需要它们，所以你正在寻找它们。
错误的地方。我们需要备份到(1)。
我已经回答了您的其他问题，并附上了▶传感器数据模型◀。这并没有解决您在此处指出的缺陷。然而，我刚刚看到这个问题，我明天会更新DM并包含这些表和列。
问题
看起来您需要一个传感器参考表，即货架项，以保存 UpperLimit 和 LowerLimit；而不是对每个位置重复它。或者它们是否针对每个位置进行了设置、本地化。
考虑 Logger 的 SensorNo 为零。
为什么传感器没有 RFID？
在每个
位置，记录器是可选的，是1::0-1吗？，Yes.
You have made two basic mistakes.
Sticking
Idiot keys on everything that moves.That has hindered your ability to model the data, as data (not as rows that have no meaning, but with an artificially enforced uniqueness), and expose Identifers; and Dependdencies (eg. a Sensor is Dependent o a Location). You are modelling spreadsheets, with pre-set Row_Ids, containing data. You need to Normalise the data, as data.
This has resulted in the problem you have identified , but there are other problems as well.
If you model the data, the Identifiers will be clear, and the Index and FK constraints will prevent this. What data is independent; what data belongs (is dependent on) what other data; what data does what to other data, and the basis of those actions.
Then (the major issues having been addressed) you are left with only minor constraints to address minor areas.
Otherwise you are stuck with adding constraints all over the place to try and get what you want, but never quite getting there. You know you need them, so you are looking for them.
Wrong place. We need to back up to (1).
I have answered your other question, and included a ▶Sensor Data Model◀. That does not address the deficiencies you identify here. However, I just saw this question, I will update the DM tomorrow and include these tables and columns.
Questions
It looks like you need a reference table for Sensors, the shelf item, to hold UpperLimit and LowerLimit; rather than repeating it for every Location. Or are they set, localised, for each Location.
Think about the Logger being SensorNo zero.
Why don't the Sensors have an RFID ?
At each
Location, is theLoggeroptional, is it 1::0-1 ?,为什么不这样做：
然后填写 SensorReadingId 或 LoggerReadingId。我想你的结构是一个简化的结构，但通常一张没有其他东西的表然后一个 PK 是多余的。
Why not have:
And then you fill either the SensorReadingId or the LoggerReadingId. I suppose your structure is a simplified one, but often a table with nothing else then one PK is redundent.