数据库“超级表”与更多表与通用表相比
我正在尝试决定数据库设计。更具体地说，这是一个更大设计的子部分。基本上，有“位置”——每个位置可以有任意数量的与其关联的传感器，并且可以有一个记录器（但只有 1 个）。
我有传感器读数和记录仪读数，每个读数都不同，我认为需要单独的表格。
如果传感器读数超出范围，则会生成警报。虽然传感器读数超出范围，但它们始终与该警报相关联，因此您最终会得到 1 个包含许多读数的警报，以便我稍后绘制警报图表，以便我可以发现趋势等。
与记录器读数相同。
到目前为止，我对存储这些数据有 3 个想法：
选项 1：
Location [Table] - Id [PK] - Name - HasLogger LiveSensor [Table] - LocationId [FK] - Id [PK] LiveSensorReading [Table] - Id [PK] - SensorId [FK] - Value LiveSensorAlert [Table] - Id [PK] - SensorReadingId [FK] (may not be needed - enforces need to always have at least 1 reading) LiveSensorAlertCorrectiveAction [Table] - LiveSensorAlertId [FK] - CorrectiveActionId [FK] - ByUserId [FK] LiveSensorAlertAcknowledgement [Table] - LiveSensorAlertId [FK] - ByUserID [FK] LiveSensorAlertReading [Table] - SensorAlertId [FK] - SensorReadingId [FK] LoggerReading [Table] - LocationId [FK] - Value LoggerAlert [Table] - Id [PK] - LoggerReadingId [FK] (may not be needed - enforces need to always have at least 1 reading) LoggerAlertReading [Table] - LoggerAlertId [FK] - LoggerReadingId [FK] LoggerAlertCorrectiveAction [Table] - LoggerAlertId [FK] - CorrectiveActionId [FK] - ByUserId [FK] LoggerAlertAcknowledgement [Table] - LoggerAlertId [FK] - ByUserID [FK]
- 问题：大量重复的表（但这真的很重要吗？？）
选项 2：
Location [Table] - Id - Name - HasLogger Sensor [Table] - Id [PK] - LocationId [FK] SensorReading [Table] - Id [PK] - SensorId [FK] - Value LoggerReading - LocationId [FK] - Value Alert [Table] - Id [PK] AlertCorrectiveAction [Table] - AlertId [FK] - CorrectiveActionId [FK] - ByUserId [FK] AlertAcknowledgement [Table] - AlertId [FK] - ByUserId [FK] SensorAlertReading - AlertId [FK] - SensorReadingId [FK] LoggerAlertReading - AlertId [FK] - LoggerReadingId [FK]
- 问题：是否存在不强制执行“至少 1 阅读每个警报”规则。
- 问题：允许超过一种类型 阅读以引用相同的警报。
选项 3：
Location [Table] - Id - Name - HasLogger Sensor [Table] - Id [PK] - LocationId [FK] SensorReading [Table] - Id [PK] - SensorId [FK] - Value LoggerReading - LocationId [FK] - Value Alert [Table] "super table" - Id [PK] LoggerAlert [Table] - AlertId [PK, FK] - LoggerReadingId [FK] SensorAlert [Table] - AlertId [PK, FK] - SensorReadingId [FK] AlertCorrectiveAction [Table] - AlertId [FK] - CorrectiveActionId [FK] - ByUserId [FK] AlertAcknowledgement [Table] - AlertId [FK] - ByUserId [FK] SensorAlertReading [Table] - SensorAlertId [FK] - SensorReadingId [FK] LoggerAlertReading [Table] - LoggerAlertId [FK] - LoggerReadingId [FK]
- 问题：没有什么可以阻止 记录器警报和传感器警报 引用相同的警报（相同的问题 作为选项 2）。
- 问题：混淆数据库（不是 超级表更多的是一个OO概念？一个 数据库纯粹是 关系不是吗？）
我认为到目前为止我更喜欢选项 1，因为它看起来很干净并且意图很明确（我希望！），即使我有效地重复了表格。
我刚刚想到的唯一一个小问题是，不同传感器的读数仍然可能与一个警报相关联。
我想知道人们对上述选项有何看法。我经常看到建议安静地使用“超级表”，但由于某种原因，它感觉不太对劲——这几乎感觉有点像黑客，尤其是当我看到试图确保数据完整性的方法时。它似乎更类似于面向对象编程而不是关系设计。
谢谢。
编辑： 一些进一步的信息可帮助回答以下一些问题：
大多数情况下，数据库仅通过应用程序服务器进行操作（如果这有什么区别的话）。
实时警报和记录器警报通常受到相同的处理，因此我可能会在大部分时间处理所有警报，而不是以不同的方式处理记录器警报和实时警报。
记录器在位置表中具有相当具体的列。由于位置和记录器将是 1 对 1 的映射，因此我决定不使用单独的记录器表，到目前为止，它似乎运行良好并保持简单。示例列：LoggerRFID (int)、LoggerUpperLimit (float)、LoggerLowerLimit (float) 等。您几乎可以说记录器是一个传感器，但我沿着这条路走下去，结果并不太好。
我几乎可以接受将警报设为通用，但正如其中一个答案所说，我试图对此非常确定，因此在选择特定路径之前，请尽可能长时间地继续研究。
I'm trying to decide on a database design. More specifically, this is a sub-part of a larger design. Basically, there are "Locations" - each location can have any number of sensors associated with it, and it can have a logger (but only 1).
I have sensor readings and I have logger readings, each different enough I think to warrant separate tables.
If a sensor reading goes out of range, an alert is generated. While a sensor reading stays out of range, they keep being associated with that alert so you end up with 1 alert containing many readings allowing me to graph the alert later so I can spot trends, etc.
Same with logger readings.
Here are my 3 ideas so far for storing this data:
Option 1:
Location [Table] - Id [PK] - Name - HasLogger LiveSensor [Table] - LocationId [FK] - Id [PK] LiveSensorReading [Table] - Id [PK] - SensorId [FK] - Value LiveSensorAlert [Table] - Id [PK] - SensorReadingId [FK] (may not be needed - enforces need to always have at least 1 reading) LiveSensorAlertCorrectiveAction [Table] - LiveSensorAlertId [FK] - CorrectiveActionId [FK] - ByUserId [FK] LiveSensorAlertAcknowledgement [Table] - LiveSensorAlertId [FK] - ByUserID [FK] LiveSensorAlertReading [Table] - SensorAlertId [FK] - SensorReadingId [FK] LoggerReading [Table] - LocationId [FK] - Value LoggerAlert [Table] - Id [PK] - LoggerReadingId [FK] (may not be needed - enforces need to always have at least 1 reading) LoggerAlertReading [Table] - LoggerAlertId [FK] - LoggerReadingId [FK] LoggerAlertCorrectiveAction [Table] - LoggerAlertId [FK] - CorrectiveActionId [FK] - ByUserId [FK] LoggerAlertAcknowledgement [Table] - LoggerAlertId [FK] - ByUserID [FK]
- Problem: Lots of repeated tables (does that really matter though??)
Option 2:
Location [Table] - Id - Name - HasLogger Sensor [Table] - Id [PK] - LocationId [FK] SensorReading [Table] - Id [PK] - SensorId [FK] - Value LoggerReading - LocationId [FK] - Value Alert [Table] - Id [PK] AlertCorrectiveAction [Table] - AlertId [FK] - CorrectiveActionId [FK] - ByUserId [FK] AlertAcknowledgement [Table] - AlertId [FK] - ByUserId [FK] SensorAlertReading - AlertId [FK] - SensorReadingId [FK] LoggerAlertReading - AlertId [FK] - LoggerReadingId [FK]
- Problem: Does not enforce "at least 1
reading per alert" rule. - Problem: Allows more than one type of
reading to reference the same alert.
Option 3:
Location [Table] - Id - Name - HasLogger Sensor [Table] - Id [PK] - LocationId [FK] SensorReading [Table] - Id [PK] - SensorId [FK] - Value LoggerReading - LocationId [FK] - Value Alert [Table] "super table" - Id [PK] LoggerAlert [Table] - AlertId [PK, FK] - LoggerReadingId [FK] SensorAlert [Table] - AlertId [PK, FK] - SensorReadingId [FK] AlertCorrectiveAction [Table] - AlertId [FK] - CorrectiveActionId [FK] - ByUserId [FK] AlertAcknowledgement [Table] - AlertId [FK] - ByUserId [FK] SensorAlertReading [Table] - SensorAlertId [FK] - SensorReadingId [FK] LoggerAlertReading [Table] - LoggerAlertId [FK] - LoggerReadingId [FK]
- Problem: Nothing stopping a
LoggerAlert and SensorAlert
referencing same Alert (same problem
as option 2). - Problem: Obfuscates database (isn't
super table more of an OO concept? A
database is meant to be purely
relational isn't it?)
I think so far I'm prefering option 1 because it just seems so clean and the intent is clear (I hope!), even though I'm repeating tables effectively.
The only slight problem I've just thought of is that readings for different sensors could still become associated with the one alarm.
I'm wondering what peoples opinions are on the above options. I've seen using "super tables" recommended quiet often but for some reason it just doesn't feel right - it almost feels like a bit of a hack especially when I see the methods for trying to ensure data integrity. It seems more akin towards OO programming than relational design.
Thanks.
EDIT:
Some further info to help answer some of the questions below:
Most of the time the database is only manipulated through an application server, if that makes any difference.
The live alerts and logger alerts are generally treated the same, so I'm probably going to be dealing with all alerts most of the time, rather than dealing with logger alerts and live alerts in different ways.
The logger has fairly specific columns that live in the location table. Since the location and logger would be a 1 to 1 mapping I decided against having a separate logger table and so far it seems to of worked out fine and kept it simple. Example columns: LoggerRFID (int), LoggerUpperLimit (float), LoggerLowerLimit (float), etc. You could almost argue that a logger is a sensor, but I went down that road and it didn't turn out too well.
I can almost accept making the alerts generic, but as one of the answers said I'm trying to be very sure about this so continuing the research for as long as I can before choosing a specific path.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
对此的一些想法（想法和意见，而不是答案）：
“超级表”（类型/子类型）模型很引人注目，但实施和支持可能很棘手。一些技巧：
...也就是说，复合主键，其中“类型”必须始终是 L)og 或 S)ensor。
...也就是说，相同的复合主键，外键位于两列上。通过这种方式，给定类型表只能有一个子类型表，并且顶部表清楚地显示涉及哪种子类型。无法强制子类型表中存在行，因此请仔细设置数据。大部分查询复杂性可以使用视图来处理（掩盖？）。
缺点是，它很复杂，对于那些不熟悉它的人来说很混乱，并且需要额外的支持和努力。真正的问题是，值得吗？
Some thoughts (ideas and opinions, not answers) on this:
The "supertable" (type/subtype) model is compelling, but can be tricky to implement and support. A few tricks:
...that is, compound primary key, where "type" must always be L)og or S)ensor.
...that is, same compound primary key, and the foreign key is on both columns. Done this way, there can only be one subtype table for a given type table, and the top table clearly shows which subtype is involved. No way to enforce the existance of a row in the subtype table, so set up your data carefully. And much of the querying complexity can be dealt with (covered up?) using views.
The downside is, it is complex, confusing to those not (yet) familiar with it, and will require extra support and effort. The real question is, is it worth it?
您可以在选项一中将 ObjectType 列添加到镜像表中，并提供 Sensor 或 Logger 的值。然后，您的数据库设计将如下所示：
这种设计确实有点混淆了数据库的基本用途，很大程度上是因为我想不出比“对象”更好的词来描述“传感器或记录器” - 如果有一些可以集体描述某个位置附加的某些内容的特定术语，这肯定有助于理解数据库。
如果您对表中的非整数 ID 不是特别挑剔，您还可以从 ObjectType 中删除 Id 列，并将 Name 设为主键。不过，我对像 ObjectType 这样的表有过不好的经历，其中主键不是整数，所以我几乎总是使用一个。
我也同意KM上面的评估，即每个表的主键ID应该命名为比“Id”长的东西。
You could add an ObjectType column to your mirrored tables in option one, and provide values of either Sensor or Logger. Your database design would then look something like this:
This design does obfuscate the underlying purpose of the database a bit, largely because I couldn't think of a better word to describe "sensor or logger" than "object" -- if there's some specific term that could collectively describe something attached at a location, that would certainly facilitate understanding the database.
You could also remove the Id column from ObjectType and make the Name a primary key if you aren't particularly squeamish about non-integer IDs in tables. I've had bad experiences with tables like ObjectType where the primary key isn't an integer, though, so I almost always use one.
I also agree with KM's assessment above that each table's primary key ID should be named something longer than "Id".
我认为这个问题已在您的其他问题中得到了解答，并提供了完整的数据模型；否则（如果有任何未解决的问题），请对此问题进行编辑。
如果您对一般意义上的父类型-子类型关系结构感兴趣，此问题您可能感兴趣。
我可以建议你关闭这个问题。
I think this question has been answered in your other question, with the full Data Model; otherwise (if there is anything outstanding), please post an Edit to this Question.
If you are interested in the Supertype-Subtype Relational structure, in a general sense, this question may be of interest to you.
May I suggest you close this question.