MS SQL Server

MS SQL Server - 存储多个表之间的多对多关系(MS SQL Server - storing many-to-many relations between several tables)

我有监控软件，可以检查某些主机上某些服务的可用性。 主机可以包含在许多主机组中，并且服务也可以包含在许多服务组中。

我有这些表来存储实际和历史（不再存在的主机和服务）值：

CREATE TABLE hosts ( id_host int name ... ) CREATE TABLE service ( id_service int name ... ) CREATE TABLE hostgroups ( id_hostgroup int, name ... ) CREATE TABLE servicegroups ( id_servicegroup int, name ... )

然后，从许多来源我了解到，多对多关系的最佳解决方案是创建关系表。 所以我决定为实际存在的关系创建关系表，如下所示：

CREATE TABLE host_service ( id_host int, id_service int ) CREATE TABLE host_hostgroup ( id_host int, id_hostgroup int ) CREATE TABLE host_service_servicegroup ( id_host int, id_service int, id_servicegroup int )

问题1：我不是很熟练，所以我不确定，如果这是我能得到的最佳模型。 有人对如何存储这类数据有其他或更好的想法吗？

接下来，我有测量数据表，例如：

CREATE TABLE monitoring ( id_monitoring int, id_day int, id_host int, id_service int, value float ... )

例如，主机“host123”上的服务“ping”在具有特定id_day的当天可用于90％。 但是，有必要对监视表中的每一行说明在该特定日期该主机的主机组以及当天该服务的哪些服务组（如果有的话）。

问题2：对我来说，他们的关系非常奇怪，我无法弄清楚如何将它们存储到数据库中，所以例如，从现在起两年后，我将能够为每个记录表示每个组。 有人知道我的问题的解决方案吗？ 我真的很感激。

先谢谢你。

I have monitoring software that checks availability of some service on some host. Host can be included in many hostgroups and also services can be included in many servicegroups.

I have these tables to store both actual and historical (hosts and services that do not exist anymore) values:

CREATE TABLE hosts ( id_host int name ... ) CREATE TABLE service ( id_service int name ... ) CREATE TABLE hostgroups ( id_hostgroup int, name ... ) CREATE TABLE servicegroups ( id_servicegroup int, name ... )

Then, from many sources I learned that the best solution for many-to-many relations is to create relation tables. So I decided to create relation tables for relations that actually exist, as folowing:

CREATE TABLE host_service ( id_host int, id_service int ) CREATE TABLE host_hostgroup ( id_host int, id_hostgroup int ) CREATE TABLE host_service_servicegroup ( id_host int, id_service int, id_servicegroup int )

Question 1: I'm not very skilled, so I'm not sure, if this is the best model that I can get. Do somebody have other or better idea of how to store this kind of data?

Next, I have table with measured data, such as:

CREATE TABLE monitoring ( id_monitoring int, id_day int, id_host int, id_service int, value float ... )

E.g. service "ping" on host "host123" was available for 90% on the day with specific id_day. However, it is necessary to say for each of these rows in monitoring table in what hostgroup(s) was that host on that specific day and in what servicegroup(s) was that service on that day, if in any.

Question 2: For me, they're very strange relations and I can't figure out how to store them into database, so e.g. two years from now I'll be able to say every group for every record in this table. Do somebody know the solution for my problem? I'd really really appreciate it.

Thank you in advance.

最满意答案

1：没有更好的模型。 创建关系表是模拟多对多关系的正确方法，例如您所拥有的。

也就是说，我认为你不需要host_service表。 您可以在host_service_servicegroup表中找到主机和服务之间的关系。 尽量避免创建比您需要的更多关系表。

2：如果我理解你的问题，处理这个问题的方法是在你的关系表中放入开始日期和结束日期。 因此，随着关系的变化，您可以回顾历史，看看某一天存在什么样的关系。

1: There is no better model. Creating relationship tables is the right way to model many-to-many relationships such as you have.

That said, I don't think you need the host_service table. You can find relationships between host and service in the host_service_servicegroup table. Try to avoid creating more relationship tables than you need.

2: If I understand your question, the way to handle this is to put start and end dates in your relationship tables. So that as relationships change over time, you can look back in history and see what relationships existed on a certain day.