如何设计具有潜在非唯一行的多对多关系中的连接表

Question

我正在为放置在房子周围不同房间的传感器设计一个家庭数据库。到目前为止，我已经规划了数据库如下：

tbl_floor = id, name - (e.g 1, first)
tbl_room = id, floor_id, name - (e.g 1, 1, lounge)
tbl_sensortype = id, name - e.g (1, dht11)
tbl_measurementtype = id, name - e.g (1, temperature)

传感器和测量之间存在多对多的关系(例如，dht11可以处理温度和湿度)，因此我有一个连接表：

tbl_sensortype_measurementtype = id, sensortype_id, measurementtype_id - (e.g 1, 1, 1) for a dht11 sensor's temperature measurement (it does other things too)

现在我的问题来了：

每个房间可能有多个相同类型的传感器(例如，一个房间有一个带有温度传感器和室温传感器的terrarrium )。因此，表tbl_room和tbl_sensortype_measurementtype之间存在多对多关系，但这可能会导致连接表中出现不唯一的行：

tbl_room_sensortype_measurementtype = room_id, sensortype_measurementtype_id - (e.g 1, 1) is a dht11 sensor's temperature measurement in the lounge

但是，如果将第二个dht11传感器放置在同一个房间中，则会有相同的行。

我可以将一个id放在连接表中，这使得它是唯一的，但很难区分它们，即

tbl_room_sensortype_measurementtype = id, room_id, sensortype_measurementtype_id

因此，在这个连接表中包含另一个名为"instance“的列，以便我可以很容易地区分同一房间中相同类型的两个传感器，这是否合理(从设计角度来看可以接受)？或者有没有更好/更可取的方法？即我的连接表将被定义为：

tbl_room_sensortype_measurementtype = id, room_id, sensortype_measurementtype_id, instance

然后，当我想要记录来自传感器的值时，我的值表将被定义为：

tbl_values = room_sensortype_measurementtype_id, value, time

我希望我的问题是有意义的(我正在学习数据库设计作为一个“有趣”的项目)。谢谢你，马丁

Answer 1

你可以有一个新的表格来代替tbl_room_sensortype_measurementtype，它可以在房间和传感器之间进行映射，如下所示，

tbl_sensor_room = id, room_id, sensortype_id

在这里，尽管房间和传感器类型相同，每个传感器都将具有唯一的id。该表稍后可以与SQL中的tbl_sensortype_measurementtype连接，以获取度量类型。

由于instance已经被id捕获，因此在唯一性方面，它成为一个冗余列。在没有instance的情况下，可以使用id编写SQL查询来实现任何所需的结果。从设计的角度来看，除非有SQL查询可以从直接查询instance列的速度方面受益，比如查询房间的第一个实例等，否则就没有必要使用它。