数据库：何时拆分为单独的表？

Question

假设我有两种不同类型的传感器：一种监视模拟电压（例如温度传感器），另一种测量某些东西是否打开或关闭（开关传感器）。

我无法决定是否拥有一张表：

[Sensor]
Id : PK
UpperLimit : FLOAT
UpperLimitAlertDelay : INT
LowerLimit : FLOAT
LowerLimitAlertDelay : INT
IsAnalog : BOOL

[SensorReading]
Id : PK
SensorId : FK
AnalogValue : FLOAT
IsOn : BOOL

或将其全部分成单独的表：

[AnalogSensor]
Id : PK
UpperLimit : FLOAT
UpperLimitAlertDelay : INT
LowerLimit : FLOAT
LowerLimitAlertDelay : INT

[AnalogSensorReadings]
Id : PK
AnalogSensorId : FK
Value : FLOAT

[SwitchSensor]
Id : PK
OnTooLongAlertDelay : INT

[SwitchSensorReadings]
Id : PK
SwitchSensorId : FK
IsOn : BOOL

目前我将其作为一张表，并且在不将其用作模拟传感器时使用“UpperLimitAlertDelay”作为“OnTooLongAlertDelay”。

在代码中，我通过传感器表上的布尔标志进行区分，并创建适当的对象（即 AnalogSensor 或 SwitchSensor），但我想知道在数据库级别将其分离出来是否会更整洁/更合适。

您会根据什么经验法则做出此类决定？它们在一个层面上是不同的实体，但在另一个层面上你可以说它们都只是传感器。

这常常是我在创建数据库时永远无法决定采取什么方向的地方。也许每当我使用 bool 来区分哪些字段意味着/应该使用时，它真的应该是一个单独的表吗？

关于这个主题或这个具体问题的一般想法值得赞赏。

谢谢！

编辑：一些进一步的信息。

开关传感器监控诸如门是否打开、冰箱压缩机是否运行、电器是否打开等信息。

可以在任何传感器上生成图表和报告，以便它们的使用方式相同；只是数据将根据类型打开/关闭或模拟值。

所以基本上他们的待遇都是一样的。

在读数表中，一个传感器的一个读数始终为一行。

到目前为止，这些观点似乎都很主观——我想这两种方式都各有利弊。

上述信息是否改变了任何人的观点？

谢谢！ 标记。

Answer 1

这是与您的其他问题？

在这种情况下，答案已在 中提供数据模型。

如果不是同一个应用程序/数据库，或者这个问题没有得到充分回答，请发帖或评论。例如。根据之前的信息，我对其进行了建模，以便 SensorType 表区分 Sensor（模拟值或布尔值）...但我们可以：

• 在传感器级别区分它，

• 或将Reading分为子类型：ReadingAnalog和ReadingSwitch。这可以使生成图形等的程序变得更容易。

Answer 2

表通常被分成逻辑上不同的“事物”，因此您不会两次拥有相同的“事物”。例如，您不希望：

[SensorReadings]
Id : PK
UpperLimit : FLOAT
UpperLimitAlertDelay : INT
LowerLimit : FLOAT
LowerLimitAlertDelay : INT
IsAnalog : BOOL
AnalogValue : FLOAT
IsOn : BOOL

因为您将传感器和它的读数混合到一行中。传感器与其读数是不同的东西：

[Sensors]                      [SensorReadings]
Id                             Id
UpperLimit                     SensorID
UpperLimitAlertDelay           Reading
LowerLimit
LowerLimitAlertDelay
IsAnalog
Manufacturer
SerialNumber
LastInspectionDate
...

我不喜欢的一件事是将“传感器”分成两个表。传感器就是传感器，就是这样。就像顾客就是顾客，歌曲就是歌曲一样。您将有一张歌曲表，而不是一张古典歌曲表，还有一张表存放其余所有歌曲。如果您确实将传感器分成两个表，则可以想象有两个具有相同 ID 的传感器。传感器是唯一的实体，它们应该位于同一个表中，并且都具有唯一的 ID。传感器是模拟的还是数字的这一事实是传感器的属性。

---

您的问题很独特 - 您的传感器可以具有不同逻辑格式的读数；有些是模拟浮点值，其他是数字布尔值。当并非所有传感器读数都适合相同的逻辑列数据类型（即 float 与 bool）时，您正在努力解决如何存储传感器的“读数”。归根结底是实用性，以及什么对系统最有利。

您可以将所有读数存储在浮点数列中：

[SensorReadings]
Id  SensorID  Reading
==  ========  =======
 1   3728     120.2
 2   3728     120.3
 3     89         1
 4     89         0
 5   3728     120.2
 6     89         0

但现在您必须知道将 0、1 的浮点值解释为逻辑 ，关闭。这很难做到吗？我个人不这么认为。确实，它没有充分利用数据库引擎中可用的数据类型，但我并不关心。您将把 SensorReadings 与 Sensors 连接起来，这样您就可以使用 IsAnalog 列来帮助您解释。换句话说：

SELECT Id, SensorID, Reading, Sensors.IsAnalog
FROM SensorReadings sr
   INNER JOIN Sensors s ON sr.SensorID = s.SensorID

将为您提供一个非常容易解析的结果集：

Id  SensorID  Reading  IsAnalog
==  ========  =======  ========
 1   3728     120.2     false
 2   3728     120.3     false
 3     89         1      true
 4     89         0      true
 5   3728     120.2     false
 6     89         0      true

您甚至可以创建一个辅助视图（或只是一个查询），将读数解码为 AnalogReading 和 DigitalReading：

CREATE VIEW SimpleSensorReadings AS

SELECT Id, SensorID, Reading AS RawReading,
    CASE Sensors.IsAnalog
    WHEN 0 THEN Reading
    ELSE NULL
    END AS AnalogReading,

    CASE Sensors.IsAnalog
    WHEN 1 THEN CAST(Reading AS BOOL)
    ELSE NULL
    END AS DigitalReading,
    Sensors.IsAnalog
FROM SensorReadings sr
   INNER JOIN Sensors s ON sr.SensorID = s.SensorID

这会给你：

[SimpleSensorReadings]
Id  SensorID  RawReading  AnalogReading  DigitalReading  IsAnalog
==  ========  ==========  =============  ==============  ========
 1   3728        120.2         120.2                       true
 2   3728        120.3         120.3                       true
 3     89            1                       true         false
 4     89            0                      false         false
 5   3728        120.2         120.2                       true
 6     89            0                      false         false

这取决于谁必须处理结果。我可以很容易地想象代码首先检查“IsAnalog”列，然后根据需要读出 AnalogReading 或 DigitalReading 。

---

你可以按照你最初的建议去做；将它们分成多个表。但现在问题变成了：如何访问数据？在我看来，如果我有这个传感器读数系统，在某些时候我将不得不对它们做一些事情 - 将它们展示给用户。现在我必须跳过障碍才能重新加入数据：

SELECT ID, AnalogSensorID AS SensorID, 
   Value AS RawReading, Value AS AnalogReading, 
   true AS IsAnalog
FROM AnalogSensorReadings

UNION ALL 

SELECT ID, SwitchSensorID AS SensorID, 
   CAST(IsOn AS float) AS RawReading, null AS AnalogReading, IsOn AS DigitalReading,
   false AS IsAnalog

给你

Id  SensorID  RawReading  AnalogReading  DigitalReading  IsAnalog
==  ========  ==========  =============  ==============  ========
 1   3728        120.2         120.2                       true
 2   3728        120.3         120.3                       true
 1     89            1                       true         false
 2     89            0                      false         false
 3   3728        120.2         120.2                       true
 3     89            0                      false         false

除了现在“Id”也很难解码，因为两个不同的读数可以具有相同的“ID”。读物就是读物，应该是独一无二的。

您可能正在寻找的折衷方案正是您最初拥有的。

[SensorReadings]
Id  SensorID  AnalogReading  DigitalReading
==  ========  =============  ==============
 1   3728        120.2         
 2   3728        120.3         
 3     89                       true
 4     89                      false
 5   3728        120.2         
 6     89                      false

是的，这会给您带来很多 (null) 值 - 但是将表重新连接在一起的费用是一个实际问题，必须在您的设计决策中考虑。

---

我认为它就像 Windows 中的注册表。 键包含一个值。您并不真正关心该值如何存储，只要您可以读取它即可，因为类型在逻辑上是这样的。为了在数据库中实现这一点，我将使用多个数据类型列，并根据需要读取它们。

Answer 3

问题是：从你的系统的角度来看，它们是同一件事吗？如果是这样，它们属于一张表。如果不是，则它们属于两个表。

通常这是理所当然的。 “雇员”和“保险计划”是两个不同的东西。 “名为 Bob 的员工”和“名为 Sally 的员工”是同一事物的两个实例。

有时更棘手。 “卡车”和“船”是两个不同的东西，还是它们都只是“车辆”的子类型？这取决于您的系统的观点。如果你出售它们，它们可能是同一件事。你可能不关心一个浮动而另一个不浮动，你只关心它们的成本是多少以及你有多少库存等等。也就是说，您保留有关它们的相同数据，并在相同的查询中使用它们。但是，如果您的系统管理一个捕鱼船队，对于船来说，您关心的是船员是谁、他们的工资是多少以及他们今天捕到了多少鱼，而对于卡车来说，您关心的是诸如它何时会出现在码头上之类的事情。码头接今天的渔获和你必须向货运公司支付每磅多少钱，它们可能是两件截然不同的事情。

确定它们是相同的迹象是：

• 它们具有相同的数据（当然不是相同的值，而是相同的字段）
• 查询通常会在几乎没有或没有区别的情况下对两者应用

如果这些不是真的，那么它们可能不是同样的事情。

也就是说，如果你发现对于类型 1，字段 A 将有值，字段 B 始终为空，而对于类型 2，字段 A 将为空，字段 B 将有值，那么他们就无法通过数据测试。

如果您发现将它们放在一张表中，则通常必须对所有查询添加类型检查，以便只能得到正确的查询，然后它们将无法通过数据测试。如果您得出结论，如果将它们放在两个单独的表中，则必须不断编写对两个表进行联接或并集的查询来获取这两个表，那么它们就通过了数据测试。

在您的示例中，您没有告诉我们您打算如何处理数据，因此我无法讨论查询测试。显然，两种类型的传感器有不同的数据——数字与开/关——所以马上就对两种不同的东西进行投票。但随后我们会回到这对您的系统有何实际影响。如果对于温度探头，您将生成温度随时间变化的图表或监视它们是否在特定范围内，而对于开/关开关，您将在它们打开时触发一个过程，并在它们关闭时停止它，它们可能是两个不同的事物。如果在这两种情况下，您都会在任何给定时间生成值报告（无论是数字还是开/关），那么它们可能是同一件事。

我倾向于认为它们可能有所不同，但在不了解更多信息的情况下，我真的不能说。

Answer 4

通常，您希望数据库设计中的冗余尽可能少。去查找普通形式，任何低于 BCNF 的东西通常都很难维护。有些应用程序使用更多的冗余来获得更高的读取性能，但却牺牲了清晰度和写入性能，例如数据仓库。
连接可能会很慢，但是当相同的信息存储两次时，它们比不一致的数据要好。

因此我建议使用较低的那个。假设您的传感器不再与完美的时间戳链接：突然，第一个布局建议严重失败。

Answer 5

我建议按照规范化规则进行操作。

根据您的需要，您可以选择 no-sql 数据库。

Answer 6

这是执行对象关系映射时需要做出的相当标准的设计决策。

您提供的第一个选项称为“每个层次结构表”，第二个选项是“每个具体类表”。还有其他变体，例如将抽象类映射到它们自己的表。一些 OR 框架（例如 hibernate）提供现成的代码来实现其中一些模式。

在某些搜索中使用其中一些关键字应该可以为您提供更多信息。有很多权衡需要考虑。我想首先要考虑的是您可能拥有多少种不同类型的传感器。

另一件需要考虑的事情是报告。如果您要编写大量查询所有类型传感器的报告，那么每个类一个表将需要联合，这可能是不需要的。

Answer 7

我怀疑这将取决于与未显示的其他实体的关系。如果有很多实体与一种类型的传感器相关，但与另一种类型的传感器无关，那么将它们分开可能是有意义的 - 否则，我会倾向于使用更简单的设计（即两个表方法，而不是比四表方法）。

我建议进行一些更改：

1. 将“UpperLimitAlertDelay”和“OnTooLongAlertDelay”拆分为单独的字段 - 据我了解，它们是不同的值，因此应该（在 1NF 下）是单独的字段。
2. 将日期时间戳字段添加到阅读表中。