数据库“可超级” vs更多的表vs通用表 [英] Database "supertable" vs more tables vs generic table
问题描述
我想决定一个数据库设计。更具体地，这是较大设计的子部分。基本上，有位置 - 每个位置可以有任何数量的传感器与它相关联，它可以有一个记录器（但只有1）。
我有传感器读数和我有记录器读数，每个不同我认为保证单独的表。
如果传感器读数超出范围，将生成警报。当传感器读数超出范围时，它们仍然与该警报相关联，因此您最终会得到一个包含许多读数的警报，从而允许我稍后绘制警报，以便我可以发现趋势等。
与记录器读数相同。
以下是我迄今为止存储此数据的3个想法：
选项1：
位置[表格]
- Id [PK]
- Name
- HasLogger
LiveSensor [Table]
- LocationId [FK]
- Id [PK]
LiveSensorReading [ Table]
- Id [PK]
- SensorId [FK]
- Value
LiveSensorAlert [Table]
- Id [PK]
- SensorReadingId [FK]（可能不需要 - 强制需要总是有至少一个读数）
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]（可能不需要 - 强制需要至少有一个读数）
LoggerAlertReading [Table]
- LoggerAlertId [FK]
- LoggerReadingId [FK]
LoggerAlertCorrectiveAction [ CorrectiveActionId [FK]
- ByUserId [FK]
LoggerAlertAcknowledgement [Table]
- LoggerAlertId [FK]
- ByUserID [FK]
pre>
< b
$ b选项2：
位置[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
条规则。 - 问题：允许多个类型的
读取引用同一个警报。
strong>选项3：
位置[表格]
- Id
- 名称
- HasLogger
Sensor [Table]
- Id [PK]
- LocationId [FK]
SensorReading [Table]
- Id [PK]
- SensorId [FK]
- 值
LoggerReading
- LocationId [FK]
- 值
$ b b警报[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]
- 问题：没有停止
LoggerAlert和SensorAlert
引用相同的警报（与选项2相同的问题
）。 - 问题：模糊数据库（不是
超级表概念？ A
数据库是纯粹的
关系不是吗？）
'm喜欢选项1，因为它只是看起来很干净，意图是清楚的（我希望！），即使我有效地重复表。
唯一的轻微问题我刚想到的是，不同传感器的读数仍然可能与一个报警相关联。
我想知道上面的选项有什么人民的意见。我已经看到使用超级表建议安静，但由于某种原因，它只是不舒服 - 它几乎感觉像一点黑客，特别是当我看到的方法，以确保数据完整性。
感谢。
编辑：
一些进一步的信息可以帮助回答以下一些问题：
大多数时候，数据库只通过应用服务器操作，如果这样做任何差异。
实时警报和记录器警报通常被视为相同，所以我可能会在大多数时间处理所有警报，与记录器警报和实时警报以不同的方式。
记录器有相当具体的列居住在位置表。因为位置和记录器将是一个1对1的映射我决定反对有一个单独的记录器表，到目前为止它似乎已确定好，并保持简单。示例列：LoggerRFID（int），LoggerUpperLimit（float），LoggerLowerLimit（float）等等。你几乎可以认为记录器是一个传感器，但是我走了那条路， >
我几乎可以接受使警报通用，但作为答案之一，我试图非常确定这一点，所以继续研究，只要我可以在选择
有关这方面的一些想法（想法和意见，而不是答案）：
supertable（类型/子类型）模型是令人信服的，但是实现和支持可能很棘手。一些技巧：
ALERT
AlertId PK 1/2
AlertType PK 2/2检查约束1或2，或更好的L或S）
...就是复合主键，其中type必须始终为L）og或S）ensor。
LOGALERT
LogAlertId PK 1/2 FK 1 / 2
AlertType PK 2/2 FK 2/2
（再次针对SENSORALERT）
...就是同一个复合主键，外键在两列上。以这种方式，对于给定类型表，只能有一个子类型表，并且顶部表格清楚地示出涉及哪个子类型。没有办法强制在子类型表中存在一行，因此请仔细设置您的数据。很多查询的复杂性可以使用视图来处理（覆盖）。
缺点是，它是复杂的，混淆的人它，并将需要额外的支持和努力。
- 您必须多久处理所有提醒，日志还是传感器？如果大多数时间你只需要处理一个或另一个，它可能不值得。
- 你需要处理多少日志或传感器的具体细节？除了与单个警报相关的实际事件之外，两种类型的实际事件之间是如何相似的，您将跟踪的无数属性（列中的详细信息）？如果用户，确认和纠正措施（足够）相同，您可以使它们成为ALERT的属性（列），但如果不是，则必须使它们成为适当子类型的属性，并失去超类型的整合优势。
- 在设计期间，您必须立即得到正确的答案。研究，提出问题，凝视水晶球（即思考未来可能会发生什么，使所有人的当前假设无效），因为如果你错了，你和你的继任者可能必须永远生活。
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.
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:
ALERT
AlertId PK 1/2
AlertType PK 2/2 Check constraint (1 or 2, or better L or S)
...that is, compound primary key, where "type" must always be L)og or S)ensor.
LOGALERT
LogAlertId PK 1/2 FK 1/2
AlertType PK 2/2 FK 2/2
(and again for SENSORALERT)
...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?
- How often must you deal with all alerts, not only Log or only Sensor? If most of the time you only have to deal with one or the other, it's probably not worth it.
- How much Log- or Sensor-specific details do you have to deal with? Beyond the actual events related to an individual alert, how similar across both types are the myriad attributes (details in columns) you'll be tracking? If users, aknowledgements, and corrective actions are (sufficiently) identicial, you can make them attributes (columns) of ALERT, but if not then you have to make them atttributes of the appropriate subtype, and you lose the consolidating advantage of the supertype.
- And you have to get it correct now, during design time. Research, ask questions, gaze into crystal balls (i.e. ponder what might happen in the future to invalidate everyone's current assumptions), because if you get it wrong now you and your successors may have to live with it forever.
这篇关于数据库“可超级” vs更多的表vs通用表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
